📐 The Mean is Lying to You.
At BharatPe's Risk Analytics team, the average transaction value is the headline metric. But averages are liars — one massive transaction from a corporate account can inflate the number beyond recognition.
The Head of Risk wants the median transaction value per merchant category. Median is the middle value. It cannot be faked by outliers.
SQL has no native MEDIAN function. This forces you to compute it from scratch using window functions:
Calculate the median transaction amount per merchant category. Return category and median_amount. Round to 2 decimal places. Order by median_amount descending.
The technique: use ROW_NUMBER() and COUNT() within each category. The median is the average of the middle one or two values — selected by filtering where row number is between (total/2) and (total/2 + 1).
transactions Table:
| Column | Type |
|---|---|
| txn_id | integer |
| merchant_id | integer |
| amount | integer |
| category | text |
| txn_date | text |
Expected Outcome (Illustrative Sample):
| category | median_amount |
|---|---|
| Travel | 16500.00 |
This is the query that separates analysts who read about statistics from those who can compute them in SQL.