📎 This article includes 3 downloadable practice files ↓

GROUP BY collapses rows into totals. Window functions calculate across related rows without collapsing them — so each row keeps its detail and gains a running total, a rank or last month’s value. They work in PostgreSQL, SQL Server, Oracle, MySQL 8+ and SQLite 3.25+.

The shape

function_name(...) OVER (
    PARTITION BY group_columns     -- restart for each group (optional)
    ORDER BY sort_columns          -- order inside the group
    ROWS BETWEEN ... AND ...       -- which rows to include (optional)
)

Our example table monthly_sales(region, month, amount) has one row per region per month.

1. Running total

SELECT region, month, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM monthly_sales;

Each region’s running total restarts because of PARTITION BY region. Remove it for one company-wide running total.

2. Ranking: ROW_NUMBER, RANK, DENSE_RANK

SELECT region, month, amount,
       ROW_NUMBER() OVER (PARTITION BY month ORDER BY amount DESC) AS rn,
       RANK()       OVER (PARTITION BY month ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY month ORDER BY amount DESC) AS drnk
FROM monthly_sales;

3. Top-N per group

“Best two months for each region” — rank inside a subquery, then filter:

SELECT * FROM (
    SELECT region, month, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM monthly_sales
) t
WHERE rn <= 2;

You cannot put a window function directly in WHERE, because windows are calculated after filtering. The subquery (or a CTE) is the standard pattern.

4. Month-on-month with LAG

SELECT region, month, amount,
       LAG(amount) OVER (PARTITION BY region ORDER BY month) AS prev_month,
       amount - LAG(amount) OVER (PARTITION BY region ORDER BY month) AS change,
       ROUND(100.0 * (amount - LAG(amount) OVER (PARTITION BY region ORDER BY month))
             / NULLIF(LAG(amount) OVER (PARTITION BY region ORDER BY month), 0), 1) AS change_pct
FROM monthly_sales;

LEAD looks forward instead. LAG(amount, 12) gives the same month last year when every month is present.

5. Moving average

SELECT region, month, amount,
       AVG(amount) OVER (PARTITION BY region ORDER BY month
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3m
FROM monthly_sales;

6. Share of total without a join

SELECT region, month, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY month), 1) AS share_pct
FROM monthly_sales;

7. De-duplicate: keep the latest record per customer

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customers_raw
)
SELECT * FROM ranked WHERE rn = 1;

Excel equivalents, for intuition

Practise these queries in the browser in the SQL Playground.