Rank merchant categories by total spend (highest first) and add a running cumulative percent of all spend, rounded to…
SQLLevel: IntermediateServicing and risk analytics
Rank merchant categories by total spend (highest first) and add a running cumulative percent of all spend, rounded to 1 decimal (cum_pct). Return category, total spend rounded to 2 decimals, and cum_pct. The "which categories make up 80% of volume" Pareto view is a senior-analyst staple.
Table definitions (from the source exercise):
CREATE TABLE transactions(transaction_id INTEGER PRIMARY KEY, account_id INTEGER, transaction_date TEXT, amount REAL, merchant_category TEXT);
Hints
- Which rows belong to the partition and frame for each current row?
- Check ordering, ties, frame boundaries and the difference from GROUP BY.
Answer
Approach: Aggregate per category in a CTE, then SUM(spend) OVER (ORDER BY spend DESC) / SUM(spend) OVER () - a running window divided by a full window.
WITH cat AS (
SELECT merchant_category, SUM(amount) AS spend
FROM transactions
GROUP BY merchant_category
)
SELECT merchant_category, ROUND(spend, 2) AS spend,
ROUND(100.0 * SUM(spend) OVER (ORDER BY spend DESC)
/ SUM(spend) OVER (), 1) AS cum_pct
FROM cat
ORDER BY spend DESC;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT