SQL window functions: running totals and rankings

Question

I need a running total of sales per month, and the top 3 products per category. Currently I do it in Python after fetching everything. Can SQL do this directly?

Accepted Answer

Yes, with **window functions**. They compute over a set of rows *without collapsing them* like `GROUP BY` does.

**Running total:**
```sql
SELECT month,
       revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_sales;
```

**Top 3 per category:**
```sql
SELECT *
FROM (
  SELECT category, product, revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM product_sales
) t
WHERE rn <= 3;
```

- `PARTITION BY` restarts the calculation per group.
- `ROW_NUMBER` gives unique ranks; `RANK`/`DENSE_RANK` handle ties.
- `LAG(revenue) OVER (ORDER BY month)` gives the previous row, handy for month-over-month growth.

Window functions run in the database, next to the data, which is usually far faster than shipping all rows to Python.

1 réponse