Window functions and CTEs

This lesson covers OVER (), PARTITION BY, RANK / DENSE_RANK / ROW_NUMBER, running SUM, LAG / LEAD, WITH (CTE), pivot. Read it, then solve questions 51–60 below it in the sidebar. Every query runs on a real PostgreSQL database inside your browser.

What a window function does

func(…) OVER (…) computes a value across related rows without collapsing them, unlike GROUP BY. OVER () = all rows; OVER (PARTITION BY col) = rows with the same col.

SELECT product_name, category, unit_price,
       AVG(unit_price) OVER (PARTITION BY category) AS category_avg
FROM products;

Ranking

ROW_NUMBER() gives 1, 2, 3… (no ties). RANK() gives ties the same number and skips after them (1, 1, 3). DENSE_RANK() doesn’t skip (1, 1, 2). The ORDER BY inside OVER decides who is first.

SELECT customer_id, sale_id, total_price,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_price DESC) AS nth_biggest
FROM sales;

Running totals

Adding ORDER BY inside OVER makes SUM cumulative: each row sees the rows up to itself (rows with the same order value count together).

SELECT sale_date, total_price,
       SUM(total_price) OVER (ORDER BY sale_date) AS revenue_so_far
FROM sales
WHERE customer_id = 1;

First or last per group

Number the rows per group with ROW_NUMBER() and keep number 1. Window functions can’t go in WHERE, so compute them in a subquery or CTE first.

SELECT * FROM (
  SELECT category, product_name, unit_price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY unit_price) AS rn
  FROM products
) t
WHERE rn = 1;

Previous and next rows

LAG(col) reads col from the previous row in the window’s order, LEAD(col) from the next. The first row’s LAG is NULL.

SELECT sale_id, sale_date,
       LAG(sale_date) OVER (ORDER BY sale_date) AS previous_sale
FROM sales
WHERE customer_id = 2;

CTEs: WITH

WITH name AS (SELECT …) defines a named temporary result you can use in the main query, even several times. It reads top-down and replaces deeply nested subqueries.

WITH per_customer AS (
  SELECT customer_id, COUNT(*) AS bills
  FROM sales
  GROUP BY customer_id
)
SELECT MAX(bills) AS most, MIN(bills) AS fewest
FROM per_customer;

Shares and pivots

A share is 100 * part / total, with the total from a subquery or SUM(…) OVER (). A pivot turns row values into columns: one aggregate per output column, each with a condition (SUM(CASE WHEN … THEN x ELSE 0 END)).

SELECT customer_id,
       SUM(CASE WHEN sale_date < '2024-01-01' THEN 1 ELSE 0 END) AS bills_2023,
       SUM(CASE WHEN sale_date >= '2024-01-01' THEN 1 ELSE 0 END) AS bills_2024
FROM sales
GROUP BY customer_id
ORDER BY customer_id;