Window functions without the magic

GROUP BY folds many rows into one. Sometimes you want the opposite: keep every row, but give each one some context. Its running total. Its rank. The value on the row before it.

That is what window functions do. They look like magic in the docs. Underneath, they are three steps you already know.

The three steps

Take this query:

SELECT driver_id, active_date,
       lag(active_date) OVER (PARTITION BY driver_id ORDER BY active_date) AS prev_day
FROM driver_days;

The engine runs the OVER (...) part like this:

1. Partition. Split the rows by driver_id. This is a hash map, exactly like GROUP BY, except each entry holds a list of rows instead of a running total.
2. Sort. Sort each partition by active_date.
3. Walk. Go down each sorted list in order and compute the function for each row, using the rows near it.

In Python:

rows = [(1, 6), (2, 1), (1, 3), (1, 7), (2, 4)]   # (driver_id, day)

partitions = {}                                   # 1. partition
for driver, day in rows:
  partitions.setdefault(driver, []).append(day)

for driver, days in partitions.items():
  days.sort()                                   # 2. sort
  prev = None
  for day in days:                              # 3. walk
      print(driver, day, "prev:", prev)
      prev = day

No rows were removed. Each one got a new column. That is the difference from GROUP BY: a window function adds a column, it never changes the row count.

What it costs

The three steps tell you the price. Partitioning is one hash pass, like GROUP BY. The walk is one pass. The sort is the expensive part: sorting n rows takes about n log n comparisons, and every row must be held, in memory or spilled to disk, until its partition is sorted.

So a window over 10 million rows costs roughly what an ORDER BY over 10 million rows costs. Two habits keep it cheap:

• Filter first. A WHERE clause shrinks the input before the sort.
• Reuse one window. Several functions with the same PARTITION BY and ORDER BY share one sort. Two different windows mean two sorts.

Gaps with LAG

Now the real thing. driver_days has one row per day a driver was online. Gaps are days off. lag() reads the row before the current one in the walk:

SELECT driver_id,
     active_date,
     lag(active_date) OVER w                AS prev_day,
     active_date - lag(active_date) OVER w  AS gap_days
FROM driver_days
WHERE driver_id = 1
WINDOW w AS (PARTITION BY driver_id ORDER BY active_date)
ORDER BY active_date;

The first row has no previous row, so lag() returns NULL. After that, a gap of 1 means “worked yesterday too”. A gap of 7 means a week off. lead() is the mirror: it reads the next row.

The WINDOW w AS (...) clause names the window once so you do not repeat it. It is plain SQL and saves a lot of typos.

Running totals with a frame

sum() with an ORDER BY inside OVER turns into a running total. As the walk moves down the partition, it keeps adding.

SELECT city,
     CAST(started_at AS DATE) AS day,
     round(sum(fare), 2) AS fares,
     round(sum(sum(fare)) OVER (PARTITION BY city
                                ORDER BY CAST(started_at AS DATE)), 2) AS running_fares
FROM trips
WHERE status = 'completed'
GROUP BY city, day
ORDER BY city, day
LIMIT 10;

Two things are happening here, in order. First, GROUP BY folds trips into one row per city per day. Then the window runs over those grouped rows. That is why you see sum(sum(fare)): the inner sum is the group aggregate, the outer sum is the window. Windows always run after GROUP BY and HAVING, and before ORDER BY.

Which rows go into each running sum is called the frame. With an ORDER BY and no frame written, the default is “from the start of the partition up to this row”. You can write others, like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-row moving sum.

Dedup with ROW_NUMBER

This is the window function you will use most at work. The events table has a known problem: about 5% of events were ingested twice. Same user_id, event_type and event_ts, but a different event_id and a later ingested_at.

row_number() numbers the rows in each partition 1, 2, 3 as the walk goes. Partition by what makes an event “the same event”, sort by ingest time, and keep number 1:

SELECT count(*) AS raw_events,
     count(*) FILTER (WHERE rn = 1) AS kept,
     count(*) FILTER (WHERE rn > 1) AS dropped
FROM (
SELECT *,
       row_number() OVER (PARTITION BY user_id, event_type, event_ts
                          ORDER BY ingested_at, event_id) AS rn
FROM events
);

3,146 rows in, 2,976 kept, 170 dropped. Note the second sort key, event_id. If two copies were ingested at the same instant, ingested_at alone would not decide which is first, and the result could change from run to run. Always sort on enough columns to make the order unique.

DuckDB also supports QUALIFY, which is to window functions what HAVING is to GROUP BY: a filter that runs after them. ... QUALIFY row_number() OVER (...) = 1 does the same dedup without the subquery.

Fixing last lesson’s fan-out

Last lesson, joining line items to price_changes turned 2,423 rows into 5,940. What we wanted was one row per line item: the list price in effect on the order date. That is “join, then keep the latest match per line item”:

SELECT count(*) AS rows_after_fix
FROM (
SELECT oi.order_id, oi.product_id, pc.price AS list_price
FROM order_items oi
JOIN orders o         ON o.order_id = oi.order_id
JOIN price_changes pc ON pc.product_id = oi.product_id
                     AND pc.changed_at <= o.order_date
QUALIFY row_number() OVER (PARTITION BY oi.order_id, oi.product_id
                           ORDER BY pc.changed_at DESC) = 1
);

Back to 2,423. The join still fans out, but the window picks one row per line item, the most recent change on or before the order date. This “as-of” pattern shows up anywhere history is stored as change rows: prices, addresses, exchange rates.

Ship it

Write a clean events view for ridelog:

1. Dedup events with row_number() over user_id, event_type, event_ts, ordered by ingested_at, event_id.
2. Keep rn = 1 only.
3. Check the counts: raw rows minus dropped rows must equal kept rows, and running it twice must give the same numbers.

Then use lag() on driver_days to find each driver’s longest break. You have shipped it when you can say, for driver 1, the longest gap in days and the date it ended.