Window-function mastery — SQL Advanced Module 5 | LeadAfrik Public Economics Hub

Intermediate gets you fluent with the common window functions. Advanced is about composing them — complex partitions, the right frame for the question, and dialect-specific shortcuts that keep the SQL readable.

Partitioning by an expression

sql

-- Top 3 highest-value orders per customer per quarter
SELECT customer_id, order_id, order_date, amount
FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id, EXTRACT(YEAR FROM order_date), EXTRACT(QUARTER FROM order_date)
      ORDER BY amount DESC
    ) AS rn
  FROM orders
) ranked
WHERE rn <= 3;

PARTITION BY accepts any expression, not just a column. Derived groupings — calendar quarters, geographic buckets, balance bands — let you slice the window in ways the underlying table doesn't make explicit.

ROWS vs RANGE vs GROUPS

ROWS — physical-row count. BETWEEN 2 PRECEDING AND CURRENT ROW = three physical rows
RANGE — value-based on the ORDER BY column. BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW = all rows within 7 calendar days. Rows with the same ORDER BY value are treated as a single position (peer)
GROUPS (SQL:2011, Postgres 11+) — peer-group based. BETWEEN 2 PRECEDING AND CURRENT ROW = the previous two peer groups plus the current peer group

sql

-- Sum every value within 7 days of the current row (calendar window)
SELECT date, value,
  SUM(value) OVER (
    ORDER BY date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS sum_last_7d
FROM daily_metrics;

When RANGE beats ROWS

When the ORDER BY column has irregular gaps (missing dates, varying timestamps), ROWS gives you a row-count window — which is wrong for a 'calendar week' question. RANGE with an INTERVAL gives the right answer.

Named windows: WINDOW clause

sql

SELECT customer_id, order_date, amount,
  RANK() OVER w AS rk,
  LAG(amount) OVER w AS prev_amt,
  SUM(amount) OVER w AS running_total
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);

When multiple window functions share the same PARTITION BY + ORDER BY, the WINDOW clause names the spec once and re-uses it. Removes repetition; reduces typo risk.

QUALIFY: HAVING for window functions

QUALIFY filters on the result of a window function in the same query — no subquery needed. Supported in Snowflake, DuckDB, BigQuery, Trino, Teradata. Postgres and MySQL don't support QUALIFY yet (use the subquery form).

sql

-- Snowflake / DuckDB / BigQuery / Trino:
SELECT customer_id, order_id, amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders
QUALIFY rn = 1;

-- Postgres / MySQL equivalent (subquery wrap):
SELECT * FROM (
  SELECT customer_id, order_id, amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
) t WHERE rn = 1;

Composing windows for percentile-and-rank work

sql

-- Score percentile and quartile per geographic region
SELECT user_id, region, score,
  PERCENT_RANK() OVER (PARTITION BY region ORDER BY score) AS percentile,
  NTILE(4)        OVER (PARTITION BY region ORDER BY score) AS quartile,
  CUME_DIST()     OVER (PARTITION BY region ORDER BY score) AS cume_dist
FROM users;

Exercise

You have a sales(rep_id, region, sale_date, amount) table. Write ONE query that returns, for each sale: the rep's rank in their region for that month (by total monthly sales), the rep's total sales last month (LAG of the monthly aggregate), and a flag indicating whether the sale was the largest single sale by that rep in that region historically. Use named windows where the same partition repeats.