🪟SQL advanced: windows & CTEs

Window functions are where data-engineering SQL questions are won or lost. Learn the five patterns in this lesson well enough to write them from memory.

Anatomy of a window function

func(...) OVER (
  PARTITION BY k            -- restart per group (like GROUP BY, but rows are NOT collapsed)
  ORDER BY t                -- order inside the partition
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW   -- optional frame
)

Pattern 1 · Top-N per group

WITH ranked AS (
  SELECT p.category, p.name, SUM(oi.quantity) AS qty,
         ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity) DESC, p.name) AS rn
  FROM order_items oi JOIN products p ON p.id = oi.product_id
  GROUP BY p.category, p.name                -- windows run AFTER group by, so you can window an aggregate
)
SELECT category, name, qty FROM ranked WHERE rn <= 2;   -- RANK() if ties should all be kept

Pattern 2 · Deduplicate: keep the latest row per key

WITH r AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_updates
)
SELECT customer_id, email FROM r WHERE rn = 1;
-- Postgres shortcut: SELECT DISTINCT ON (customer_id) * FROM t ORDER BY customer_id, updated_at DESC;
-- Snowflake/Databricks/BigQuery shortcut: ... QUALIFY ROW_NUMBER() OVER (...) = 1

Pattern 3 · Running totals, moving averages, % of total

WITH daily AS (
  SELECT o.order_date AS d, SUM(oi.quantity * oi.unit_price) AS rev
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'completed' GROUP BY o.order_date
)
SELECT d, rev,
       SUM(rev) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)                AS running,
       AVG(rev) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3,
       ROUND(100.0 * rev / SUM(rev) OVER (), 2)                           AS pct_of_total
FROM daily ORDER BY d;

Pattern 4 · Compare to previous row (LAG/LEAD): growth, gaps, churn

WITH m AS (
  SELECT strftime('%Y-%m', o.order_date) AS month, SUM(oi.quantity * oi.unit_price) AS rev
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status = 'completed' GROUP BY 1
)
SELECT month, rev,
       LAG(rev) OVER (ORDER BY month) AS prev_rev,
       ROUND(100.0 * (rev - LAG(rev) OVER (ORDER BY month)) / LAG(rev) OVER (ORDER BY month), 1) AS mom_growth_pct
FROM m;

Pattern 5 · Gaps & islands (streaks) and sessionization

-- islands: date minus row_number is constant inside a consecutive run
WITH g AS (
  SELECT user_id, login_date,
         julianday(login_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS grp
  FROM (SELECT DISTINCT user_id, login_date FROM logins) AS d   -- MySQL requires the alias
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM g GROUP BY user_id, grp ORDER BY user_id, streak_start;

-- sessions: flag "new session" rows, then a running SUM of the flags = session id
--   flag = CASE WHEN prev_ts IS NULL OR ts - prev_ts > 30 min THEN 1 ELSE 0 END
--   session_no = SUM(flag) OVER (PARTITION BY user_id ORDER BY ts)

Recursive CTEs (hierarchies, date spines)

-- org chart below a manager
WITH RECURSIVE t(id, name, depth, path) AS (
  SELECT id, name, 0, name FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, t.depth + 1, t.path || ' > ' || e.name
  FROM employees e JOIN t ON e.manager_id = t.id
)
SELECT * FROM t ORDER BY path;

-- date spine to fill missing days with 0
WITH RECURSIVE days(d) AS (
  SELECT '2026-05-01' UNION ALL SELECT date(d, '+1 day') FROM days WHERE d < '2026-05-07'
)
SELECT days.d, COUNT(l.user_id) AS logins
FROM days LEFT JOIN logins l ON l.login_date = days.d
GROUP BY days.d;

Retention / cohort (very common DE/analytics question)

WITH first AS (
  SELECT customer_id, MIN(strftime('%Y-%m', order_date)) AS cohort FROM orders
  WHERE status = 'completed' GROUP BY customer_id
), activity AS (
  SELECT DISTINCT customer_id, strftime('%Y-%m', order_date) AS month FROM orders WHERE status = 'completed'
)
SELECT f.cohort, a.month, COUNT(*) AS active_customers
FROM first f JOIN activity a ON a.customer_id = f.customer_id
GROUP BY f.cohort, a.month ORDER BY f.cohort, a.month;

Upserts / MERGE (loading data idempotently)

-- PostgreSQL / SQLite
INSERT INTO dim_customer (id, email) VALUES (1, 'a@b.c')
ON CONFLICT (id) DO UPDATE SET email = excluded.email;
-- MySQL (8.0.20+ prefers the row alias over VALUES())
INSERT INTO dim_customer (id, email) VALUES (1, 'a@b.c') AS new
ON DUPLICATE KEY UPDATE email = new.email;
-- ANSI MERGE (Snowflake, Databricks, SQL Server, Postgres 15+)
MERGE INTO dim_customer t USING staging s ON t.id = s.id
WHEN MATCHED AND t.email <> s.email THEN UPDATE SET email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email);

Window frames at a glance

Pattern 6 · Median and percentiles without a MEDIAN function

WITH r AS (
  SELECT category, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price) AS rn,
         COUNT(*)     OVER (PARTITION BY category)                AS n
  FROM products
)
SELECT category, AVG(price) AS median_price
FROM r
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)       -- MySQL: use DIV (decimal division otherwise)
GROUP BY category;
-- PostgreSQL: percentile_cont(0.5) WITHIN GROUP (ORDER BY price)
-- Spark / Databricks: percentile_approx(price, 0.5)   ·   quartiles: NTILE(4) OVER (ORDER BY price)

Pattern 7 · "Has all of…" (relational division) and "has none of…"

-- customers who bought from EVERY category
SELECT o.customer_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
GROUP BY o.customer_id
HAVING COUNT(DISTINCT p.category) = (SELECT COUNT(DISTINCT category) FROM products);

-- customers who bought electronics but NEVER furniture
SELECT DISTINCT o.customer_id
FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id
WHERE p.category = 'electronics'
  AND NOT EXISTS (SELECT 1 FROM orders o2
                  JOIN order_items i2 ON i2.order_id = o2.id
                  JOIN products p2    ON p2.id = i2.product_id
                  WHERE o2.customer_id = o.customer_id AND p2.category = 'furniture');

Pattern 8 · Status changes and "first time X happened"

-- rows where the value changed compared to the previous row (change log → transitions)
WITH x AS (
  SELECT customer_id, email, updated_at,
         LAG(email) OVER (PARTITION BY customer_id ORDER BY updated_at) AS prev_email
  FROM customer_updates
)
SELECT * FROM x WHERE prev_email IS NULL OR email <> prev_email;

-- first purchase per user, and the time from first view to first purchase
SELECT user_id,
       MIN(CASE WHEN event_type = 'view'     THEN ts END) AS first_view,
       MIN(CASE WHEN event_type = 'purchase' THEN ts END) AS first_purchase
FROM events GROUP BY user_id;

How to build a hard query without getting lost

1. Write the grain of the final output in one sentence: "one row per user per session".
2. Build the query as a chain of CTEs, one idea per step: base → flagged → grouped → ranked → final.
3. After each step, run SELECT * FROM step and check row counts against the data by hand.
4. Only the final SELECT renames, rounds and sorts.
5. Before submitting, delete helper columns and re-read the statement's column list and ORDER BY.