💀 Millions in Revenue. Zero Credit Given.
It is the end of quarter at Razorpay's Revenue Operations team. The attribution model is broken — and nobody noticed until now.
Payments are flowing in. But a significant portion of those payments reference a merchant_id that does not exist in the merchants master table. These are orphaned payments — real money with no attribution.
Your task is surgical: For each month, show the total payment volume, the volume from orphaned payments (merchant not in merchants table), and the orphaned percentage (rounded to 2 decimal places). Order chronologically.
This requires a LEFT JOIN to identify orphaned records, CASE WHEN to separate them, and date aggregation — all in one query.
payments Table:
| Column | Type |
|---|---|
| payment_id | integer |
| merchant_id | integer |
| amount | integer |
| payment_date | text |
| status | text |
merchants Table:
| Column | Type |
|---|---|
| merchant_id | integer |
| name | text |
| category | text |
Expected Outcome (Illustrative Sample):
| month | total_volume | orphaned_volume | orphaned_pct |
|---|---|---|---|
| 2024-01 | 507000 | 175000 | 34.52 |
Find the gap before the auditors do.