SQL: window functions for running totals and ranks
A GROUP BY query collapses each group into one row. A window function computes a value over a set of related rows but keeps every row. The OVER clause defines the window: PARTITION BY splits the rows into groups, and ORDER BY sets the order inside each group.
SQL
CREATE TABLE sales (day INTEGER, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 10), (2, 'N', 20), (3, 'N', 5), (1, 'S', 7), (2, 'S', 7), (3, 'S', 30);
SELECT day, region, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY day) AS running_total,
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales
ORDER BY region, day;