Скачать или смотреть SQL One-Liner: Running Total (Cumulative Sum)

One of the most common SQL interview challenges is calculating a running total (cumulative sum).

Long Way (Subquery / Self-Join):

Uses a subquery per row to compute the total up to that date.

Correct but slow for large datasets.

Shortcut One-Liner (Window Function):

Uses SUM() OVER (ORDER BY column) to calculate running totals in a single scan.

Highly efficient and widely used in dashboards, financial reports, and trend analysis.

👉 Use Cases:

Cumulative sales by day/month.

Tracking balances in banking.

Running totals for dashboards and KPIs.

Queries:

✅ Long Way (Correlated Subquery):
SELECT t1.user_id, t1.transaction_id, t1.amount, t1.transaction_date
FROM transactions t1
WHERE t1.transaction_date = (
SELECT MAX(t2.transaction_date)
FROM transactions t2
WHERE t2.user_id = t1.user_id
);


Explanation:

For each transaction t1, we look up the maximum transaction date for the same user using a correlated subquery.

Only the latest transaction per user will match.

This works, but the correlated subquery can be slow for large datasets.

✅ Shortcut One-Liner (Window Function):
SELECT user_id, transaction_id, amount, transaction_date
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date DESC) AS rn
FROM transactions
) t
WHERE rn = 1;


Explanation:

ROW_NUMBER() assigns a unique sequential number for each transaction within a user, ordered by transaction_date DESC.

rn = 1 ensures we only take the latest record per user.

Faster, easier to read, and scalable for millions of rows.