SQL
Calculating Ranks and Running Totals with SQL Window Functions
Learn to use SQL window functions like ROW_NUMBER(), RANK(), and SUM() OVER() for advanced data ranking and calculating running totals in your web applications.
-- Example Table: orders
-- +---------+------------+--------+
-- | order_id| customer_id| amount |
-- +---------+------------+--------+
-- | 1 | 101 | 100.00 |
-- | 2 | 102 | 150.00 |
-- | 3 | 101 | 50.00 |
-- | 4 | 103 | 200.00 |
-- | 5 | 102 | 75.00 |
-- | 6 | 101 | 120.00 |
-- +---------+------------+--------+
-- Calculate rank of orders by amount globally
SELECT
order_id,
customer_id,
amount,
RANK() OVER (ORDER BY amount DESC) as global_rank
FROM
orders
ORDER BY
global_rank, order_id;
-- Calculate rank of orders by amount per customer
SELECT
order_id,
customer_id,
amount,
RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) as customer_rank
FROM
orders
ORDER BY
customer_id, customer_rank, order_id;
-- Calculate running total of amounts per customer
SELECT
order_id,
customer_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_id) as running_total
FROM
orders
ORDER BY
customer_id, order_id;
How it works: SQL window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, they do not collapse rows. This snippet demonstrates `RANK()` for ranking data globally and within partitions (e.g., per customer), and `SUM() OVER()` for calculating a running total, which is invaluable for analytical queries and reports.