Window Functions

Window functions keep every row and add a column that can see the neighbours. Almost every interview question phrased as “per customer, the …” is one.

The three ranking functions on the same data

SELECT name, amount,
       row_number() OVER (ORDER BY amount DESC) AS rn,
       rank()       OVER (ORDER BY amount DESC) AS rnk,
       dense_rank() OVER (ORDER BY amount DESC) AS dense
FROM (VALUES ('a', 300), ('b', 200), ('c', 200), ('d', 100)) AS t(name, amount);

$ duckdb interview.duckdb < ranks.sql
┌─────────┬────────┬───────┬───────┬───────┐
│  name   │ amount │  rn   │  rnk  │ dense │
│ varchar │ int32  │ int64 │ int64 │ int64 │
├─────────┼────────┼───────┼───────┼───────┤
│ a       │    300 │     1 │     1 │     1 │
│ b       │    200 │     2 │     2 │     2 │
│ c       │    200 │     3 │     2 │     2 │
│ d       │    100 │     4 │     4 │     3 │

Rows b and c are tied at 200, and that single fact separates the three:

row_number  1 2 3 4    ties broken arbitrarily — a different run may swap b and c
rank        1 2 2 4    ties share, then the next value skips to 4
dense_rank  1 2 2 3    ties share, no gap

“Which one depends on ties. If two customers tie for second, should the next be third or fourth? Should both tied customers appear in a top-2? ROW_NUMBER picks one arbitrarily, which is fine for pagination and wrong for ‘the top 2 spenders’.”

That question, asked before writing, is worth more than the query.

ROW_NUMBER without a deterministic ORDER BY is non-deterministic. Add a tiebreaker — ORDER BY amount DESC, order_id — whenever the result must be reproducible.

Partitions: the same computation, per group

SELECT c.name, o.order_id, o.amount,
       row_number() OVER (PARTITION BY c.customer_id ORDER BY o.order_date) AS nth_order,
       sum(o.amount) OVER (PARTITION BY c.customer_id)                      AS customer_total,
       round(100.0 * o.amount /
             sum(o.amount) OVER (PARTITION BY c.customer_id), 1)            AS pct_of_customer
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_date;

┌─────────┬──────────┬────────┬───────────┬────────────────┬─────────────────┐
│  name   │ order_id │ amount │ nth_order │ customer_total │ pct_of_customer │
├─────────┼──────────┼────────┼───────────┼────────────────┼─────────────────┤
│ Ana     │      101 │ 120.00 │         1 │         400.50 │            30.0 │
│ Ana     │      102 │  80.50 │         2 │         400.50 │            20.1 │
│ Ana     │      105 │ 200.00 │         3 │         400.50 │            49.9 │
│ Bo      │      103 │ 310.00 │         1 │         409.99 │            75.6 │
│ Bo      │      106 │  99.99 │         2 │         409.99 │            24.4 │
│ Cy      │      104 │  45.00 │         1 │          45.00 │           100.0 │
│ Di      │      107 │ 150.00 │         1 │         150.00 │           100.0 │
└─────────┴──────────┴────────┴───────────┴────────────────┴─────────────────┘

Seven order rows in, seven rows out. GROUP BY customer_id would have returned four. That is the whole distinction, and it is the sentence to say when asked why you chose a window function.

Note sum(...) OVER (PARTITION BY ...) with no ORDER BY — that gives the partition total on every row, which is what a percentage-of-total needs. Adding an ORDER BY turns it into a running total, which is the next section and a very easy accident.

Running totals, and the frame that changes the answer

SELECT order_date, amount,
       sum(amount) OVER (ORDER BY order_date)                                  AS default_frame,
       sum(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)     AS rows_frame,
       sum(amount) OVER (ORDER BY order_date
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)    AS range_frame
FROM (VALUES (DATE '2024-01-01', 10), (DATE '2024-01-02', 20),
             (DATE '2024-01-02', 30), (DATE '2024-01-03', 40)) AS t(order_date, amount)
ORDER BY order_date;

┌────────────┬────────┬───────────────┬────────────┬─────────────┐
│ order_date │ amount │ default_frame │ rows_frame │ range_frame │
├────────────┼────────┼───────────────┼────────────┼─────────────┤
│ 2024-01-01 │     10 │            10 │         10 │          10 │
│ 2024-01-02 │     20 │            60 │         30 │          60 │
│ 2024-01-02 │     30 │            60 │         60 │          60 │
│ 2024-01-03 │     40 │           100 │        100 │         100 │
└────────────┴────────┴───────────────┴────────────┴─────────────┘

The two rows on 2024-01-02 both show 60 under the default frame, not 30 and 60.

ORDER BY with no explicit frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats all rows with the same ORDER BY value as one peer group — so both rows see the full day. ROWS counts rows literally and gives the row-by-row running total most people mean.

“If the ORDER BY column has duplicates, RANGE and ROWS give different answers. I write ROWS explicitly when I want a row-by-row running total, and RANGE when the ordering column is a date and I want the whole day to settle together.”

That distinction appears rarely in tutorials and frequently in production bugs.

The moving-average frame

SELECT order_date, amount,
       round(avg(amount) OVER (ORDER BY order_date
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS trailing_3,
       sum(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)   AS remaining
FROM orders
WHERE status = 'shipped'
ORDER BY order_date;

┌────────────┬────────┬────────────┬───────────┐
│ order_date │ amount │ trailing_3 │ remaining │
├────────────┼────────┼────────────┼───────────┤
│ 2024-03-01 │ 120.00 │     120.00 │    935.50 │
│ 2024-03-15 │  80.50 │     100.25 │    815.50 │
│ 2024-03-18 │ 310.00 │     170.17 │    735.00 │
│ 2024-04-20 │ 200.00 │     196.83 │    425.00 │
│ 2024-05-30 │ 150.00 │     220.00 │    225.00 │
│ 2024-06-02 │  75.00 │     141.67 │     75.00 │
└────────────┴────────┴────────────┴───────────┘

The first two trailing_3 values average fewer than three rows — the frame is clipped at the partition edge rather than returning NULL. If the question wants NULL until three rows exist, that must be written explicitly with a count(*) OVER (...) guard. Ask which is wanted.

Order 108 (75.00, NULL customer) is present here because this query does not join to customers. The same query with a join would silently lose it — a good thing to notice aloud.

LAG and LEAD: comparing to the neighbour

SELECT c.name, o.order_date, o.amount,
       lag(o.amount)     OVER (PARTITION BY c.customer_id ORDER BY o.order_date) AS prev_amount,
       o.amount - lag(o.amount) OVER (PARTITION BY c.customer_id ORDER BY o.order_date) AS delta,
       o.order_date - lag(o.order_date) OVER (PARTITION BY c.customer_id ORDER BY o.order_date)
                                                                                  AS days_since,
       lead(o.order_date) OVER (PARTITION BY c.customer_id ORDER BY o.order_date)  AS next_order
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_date;

┌─────────┬────────────┬────────┬─────────────┬─────────┬────────────┬────────────┐
│  name   │ order_date │ amount │ prev_amount │  delta  │ days_since │ next_order │
├─────────┼────────────┼────────┼─────────────┼─────────┼────────────┼────────────┤
│ Ana     │ 2024-03-01 │ 120.00 │        NULL │    NULL │       NULL │ 2024-03-15 │
│ Ana     │ 2024-03-15 │  80.50 │      120.00 │  -39.50 │         14 │ 2024-04-20 │
│ Ana     │ 2024-04-20 │ 200.00 │       80.50 │  119.50 │         36 │       NULL │
│ Bo      │ 2024-03-18 │ 310.00 │        NULL │    NULL │       NULL │ 2024-05-05 │
│ Bo      │ 2024-05-05 │  99.99 │      310.00 │ -210.01 │         48 │       NULL │
│ Cy      │ 2024-04-02 │  45.00 │        NULL │    NULL │       NULL │       NULL │
│ Di      │ 2024-05-30 │ 150.00 │        NULL │    NULL │       NULL │       NULL │
└─────────┴────────────┴────────┴─────────────┴─────────┴────────────┴────────────┘

The first row of every partition has a NULL prev_amount — correctly, because there is no previous order. If the question wants zero instead, lag(o.amount, 1, 0) supplies a default.

days_since is the shape behind a large family of questions: time between events, session gaps, churn detection, and the streak problems in a later lesson.

Window functions cannot go in WHERE

SELECT name, amount, row_number() OVER (ORDER BY amount DESC) AS rn
FROM orders o JOIN customers c USING (customer_id)
WHERE rn <= 3;

Binder Error: Referenced column "rn" not found in FROM clause!

Window functions are evaluated after WHERE, GROUP BY and HAVING — they are computed as part of SELECT. The fix is a CTE:

WITH ranked AS (
    SELECT c.name, o.amount,
           row_number() OVER (ORDER BY o.amount DESC, o.order_id) AS rn
    FROM orders o JOIN customers c USING (customer_id)
)
SELECT * FROM ranked WHERE rn <= 3;

┌─────────┬────────┬───────┐
│  name   │ amount │  rn   │
├─────────┼────────┼───────┤
│ Bo      │ 310.00 │     1 │
│ Ana     │ 200.00 │     2 │
│ Di      │ 150.00 │     3 │
└─────────┴────────┴───────┘

This two-step shape — compute the window in a CTE, filter in the outer query — is the answer to the entire top-N-per-group family, which the next lesson is about.

Reusing a window definition

SELECT c.name, o.amount,
       row_number() OVER w AS rn,
       sum(o.amount) OVER w AS running,
       lag(o.amount) OVER w AS prev
FROM customers c JOIN orders o USING (customer_id)
WINDOW w AS (PARTITION BY c.customer_id ORDER BY o.order_date)
ORDER BY c.name, o.order_date;

┌─────────┬────────┬───────┬─────────┬────────┐
│  name   │ amount │  rn   │ running │  prev  │
├─────────┼────────┼───────┼─────────┼────────┤
│ Ana     │ 120.00 │     1 │  120.00 │   NULL │
│ Ana     │  80.50 │     2 │  200.50 │ 120.00 │
│ Ana     │ 200.00 │     3 │  400.50 │  80.50 │
│ Bo      │ 310.00 │     1 │  310.00 │   NULL │
│ Bo      │  99.99 │     2 │  409.99 │ 310.00 │
│ Cy      │  45.00 │     1 │   45.00 │   NULL │
│ Di      │ 150.00 │     1 │  150.00 │   NULL │
└─────────┴────────┴───────┴─────────┴────────┘

The WINDOW clause names a definition once. With three or more windows sharing a partition it removes the copy-paste that hides a typo — and typing the same OVER clause four times is a real source of “why is only one column wrong”.

Supported in PostgreSQL, DuckDB, SQL Server and MySQL 8; not in older MySQL or SQLite before 3.28.

Recognising it

QUESTION                                        FUNCTION
"nth per group", "top N per group"              row_number() in a CTE, filter outside
"rank, and ties should share"                   rank() or dense_rank() — ask which
"running total", "cumulative"                   sum() OVER (ORDER BY ... ROWS ...)
"percentage of the group total"                 x / sum(x) OVER (PARTITION BY ...)
"compare to the previous / next row"            lag() / lead()
"days between consecutive events"               order_date - lag(order_date)
"moving average over N periods"                 avg() OVER (ROWS BETWEEN N-1 PRECEDING ...)
"first / last value in the group"               first_value() / last_value() — mind the frame
"which decile / quartile"                       ntile(4)
one row per group, nothing per-row              GROUP BY, not a window

Practice

row_number  1 2 3 4      rank  1 2 2 4      dense_rank  1 2 2 3

default (RANGE): 10, 60, 60, 100      explicit ROWS: 10, 30, 60, 100

Binder Error: Referenced column "rn" not found in FROM clause!

You get percentage of the running total, not of the group total.

Next: top-N-per-group — the single most-asked SQL interview pattern, three ways.