Skip to content
Quantoros

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

Related questions