SQL Window Functions: OVER, PARTITION BY & Ranking

Start with the table grain before writing windowed analysis Tutorial

To identify each customer’s latest order while keeping order details, assign ROW_NUMBER within each customer ordered by date descending, then keep row number 1.

• Window functions calculate across related rows without collapsing them into one row per group.
• PARTITION BY defines independent groups, while ORDER BY inside OVER defines sequence for ranking or cumulative calculations.
• ROW_NUMBER, RANK and DENSE_RANK handle ties differently and can change downstream filtering.
• Window frames matter for rolling sums and averages; the default frame may not match the business question.

Work through a query with row-count checks

For windowed analysis, predict one visible result before each snippet runs—a row count, shape, statistic, ranking or error pattern—then compare it with the printed checkpoint.

-- windowed analysis · example 1
WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn = 1;

windowed analysis checkpoint 1: Run the cell and inspect the printed values before changing anything.

-- windowed analysis · example 2
SELECT COUNT(*) AS rows, COUNT(DISTINCT customer_id) AS customers
FROM practice_orders;

windowed analysis checkpoint 2: rows | customers
30   | 12

-- windowed analysis · example 3
SELECT customer_id, COUNT(*) AS n
FROM practice_orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY n DESC;

windowed analysis checkpoint 3: Customers with more than one row are listed before any join or aggregation.

-- windowed analysis · example 4
SELECT region, COUNT(*) AS orders, ROUND(SUM(amount),2) AS revenue
FROM practice_orders
GROUP BY region
ORDER BY revenue DESC;

windowed analysis checkpoint 4: region | orders | revenue
West   | ...    | ...

-- windowed analysis · example 5
WITH x AS (
  SELECT amount FROM practice_orders WHERE amount IS NOT NULL
)
SELECT ROUND(SUM(amount),2) AS detail_total,
       ROUND((SELECT SUM(amount) FROM x),2) AS check_total
FROM x;

windowed analysis checkpoint 5: detail_total and check_total should match exactly.

Test duplicates, NULLs and unmatched keys

Self-check with answers

Practise with a reproducible SQLite-style dataset