Settlement Report

Problem

Every night Fawry settles merchants. Using the tables below, write a query for a given day that returns per merchant: gross amount of successful transactions, total refunds, the fee (1.5% of gross for merchants on the standard plan, 1.0% on enterprise) and net = gross − refunds − fee. Then a second query: merchants whose refunds on that day exceed 5% of their gross, ordered by that ratio descending.

merchants(id, name, plan)                                   -- plan in ('standard', 'enterprise')
transactions(id, merchant_id, amount, status, created_at)   -- status in ('success', 'failed', 'pending')
refunds(id, transaction_id, amount, created_at)

Examples

Example 1 — "Carrefour" (enterprise) on 2026-08-10: successes 10,000 + 5,000, one failed 2,000 (ignored), one refund of 500 → gross 15,000, refunds 500, fee 150.00, net 14,350.00.

Example 2 — "Koshary Abou Tarek" (standard): gross 2,000, refunds 300 → ratio 15%, listed by the second query above a merchant with 4% refunds.

Constraints

• A refund counts on the refund's date, not the original transaction's date
• Merchants with no activity that day must not appear
• Amounts are NUMERIC(18,2); round the fee to 2 decimals

What they look for

Aggregating transactions and refunds separately before joining (join-then-aggregate double counts gross when one transaction has two refunds), a CASE for the fee, HAVING or a CTE for the ratio filter, and NULLIF against merchants that only have refunds that day.