Merchant Growth Query

Problem

Using the tables below, write a query that returns, for each merchant, their successful transaction volume in the current month and the previous month, plus month-over-month growth as a percentage. Then a second query: the 10 merchants with the highest success rate over the last 90 days, counting only merchants with at least 500 attempts. Success rate = successful ÷ (successful + failed); pending rows are excluded from both numerator and denominator.

merchants(id, name, created_at)
transactions(id, merchant_id, amount_cents, status, created_at)   -- status in ('success', 'failed', 'pending')

Examples

Example 1 — "Breadfast": July successes 1,200,000 EGP, August 1,500,000 EGP → growth 25.0%. A merchant with July 0 and August 400,000 → growth NULL (not infinite), still listed.

Example 2 — "Elmenus": 5,000 successes, 500 failures, 100 pending in 90 days → rate 90.9%, eligible. A merchant with 300 attempts at 100% is not listed.

Constraints

• Use a fixed reference date parameter (:as_of), not now(), so the query is testable
• Volume is stored in piasters (amount_cents); output EGP with 2 decimals
• Growth rounded to 1 decimal; NULL when the previous month is 0

What they look for

Conditional aggregation (SUM(CASE WHEN ...) / FILTER) instead of self-joins, date_trunc for month boundaries, NULLIF for the zero-division case, and HAVING for the attempts threshold. Follow-up: an index for both queries — (merchant_id, created_at) including status — and whether a nightly materialised view is the better answer.