Allowed R6 · Within policy
{
"slug": "merchant-growth-sql",
"title": "Merchant Growth Query",
"difficulty": "medium",
"tags": [
"sql",
"technical",
"backend",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 0.6,
"last_reported_at": "2026-07-27T20:54:51.003258Z",
"url": "https://www.mokabla.com/en/c/paymob/merchant-growth-sql",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/paymob/merchant-growth-sql",
"question": "https://www.mokabla.com/agent/_call/get_question?company=paymob&question=merchant-growth-sql"
},
"company": {
"slug": "paymob",
"name_en": "Paymob"
},
"body_md": "## Problem\nUsing the tables below, write a query that returns, for each merchant, their successful transaction volume\nin the current month and the previous month, plus month-over-month growth as a percentage. Then a second\nquery: the 10 merchants with the highest success rate over the last 90 days, counting only merchants with\nat least 500 attempts. Success rate = successful ÷ (successful + failed); `pending` rows are excluded from\nboth numerator and denominator.\n\n```\nmerchants(id, name, created_at)\ntransactions(id, merchant_id, amount_cents, status, created_at) -- status in ('success', 'failed', 'pending')\n```\n\n## Examples\n**Example 1**, \"Breadfast\": July successes 1,200,000 EGP, August 1,500,000 EGP → growth 25.0%. A\nmerchant with July 0 and August 400,000 → growth NULL (not infinite), still listed.\n\n**Example 2**, \"Elmenus\": 5,000 successes, 500 failures, 100 pending in 90 days → rate 90.9%, eligible.\nA merchant with 300 attempts at 100% is not listed.\n\n## Constraints\n- Use a fixed reference date parameter (`:as_of`), not `now()`, so the query is testable\n- Volume is stored in piasters (`amount_cents`); output EGP with 2 decimals\n- Growth rounded to 1 decimal; NULL when the previous month is 0\n\n## What they look for\nConditional aggregation (`SUM(CASE WHEN ...)` / `FILTER`) instead of self-joins, `date_trunc` for month\nboundaries, `NULLIF` for the zero-division case, and `HAVING` for the attempts threshold. Follow-up: an\nindex for both queries, `(merchant_id, created_at)` including `status`, and whether a nightly\nmaterialised view is the better answer.",
"follow_ups_md": "- Same growth report but by payment method within each merchant.\n- How does the answer change if `created_at` is stored in UTC and the business day is Cairo time?",
"access": "Question bodies are paid content: a person buys 30 days of access per company (200 EGP or 200 points) in the human app. Agents get titles, tags and difficulty; bodies only for free questions."
}