🗄 SQL & Databases · Chapter 6

SQL: window functions vs GROUP BY

GROUP BY collapses rows. A window function keeps every row and adds the group's answer beside it.

Study this lesson free in the app →

The basics

A window function computes something across a group of related rows — an average, a rank, the previous value — and writes the answer onto every row instead of collapsing the group into one.

The problem it solves: GROUP BY answers "average fare per city" but throws away the individual rides. Real questions need both: "show each ride and how far it is above its city's average", "keep only the latest event per order", "time since this driver's previous trip". Before window functions you needed self-joins that were slow and easy to get wrong.

The mental model: each row looks through a window at its neighbours. PARTITION BY decides who counts as a neighbour (same city, same driver). ORDER BY inside OVER() lines those neighbours up in sequence. The frame decides how far along that line the row can see — all of it, or just the last 7 rows.

Explained

"Show each ride, plus how that fare compares to the city average."

With GROUP BY you lose the individual rides. With a window function you keep them and get the average.

Half of all SQL interview questions are secretly this distinction.

The class test results

A teacher has every student's marks.

GROUP BY is the summary sheet pinned to the noticeboard: Class A average 72, Class B average 68. Two rows. The individual students are gone.

A window function is writing on each student's own paper: "You: 81. Class average: 72. Rank in class: 3." Every student still has their sheet — each one now carries the context of its group.

Same calculation. The difference is whether the detail survives.

The syntax, minimally

-- GROUP BY: 1 row per city
SELECT city, AVG(fare) AS avg_fare
FROM rides
GROUP BY city;

-- WINDOW: every ride kept, city average attached
SELECT ride_id, city, fare,
       AVG(fare) OVER (PARTITION BY city) AS city_avg,
       fare - AVG(fare) OVER (PARTITION BY city) AS diff
FROM rides;

PARTITION BY = the grouping. ORDER BY inside OVER() = the sequence within the group. No PARTITION BY = the whole result set is one window.

The four you must know cold

-- 1. ROW_NUMBER — deduplicate: keep the latest row per ride
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY ride_id
                            ORDER BY event_time DESC) AS rn
  FROM ride_events
)
SELECT * FROM ranked WHERE rn = 1;

-- 2. LAG — time between consecutive rides for a driver
SELECT driver_id, ride_time,
       ride_time - LAG(ride_time) OVER (PARTITION BY driver_id
                                        ORDER BY ride_time) AS idle_gap
FROM rides;

-- 3. Running total — cumulative revenue by day
SELECT ride_date, daily_revenue,
       SUM(daily_revenue) OVER (ORDER BY ride_date
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND CURRENT ROW) AS running_total
FROM daily_revenue;

-- 4. RANK vs DENSE_RANK vs ROW_NUMBER on ties (100, 100, 90)
--    ROW_NUMBER  → 1, 2, 3   (arbitrary tie-break, always unique)
--    RANK        → 1, 1, 3   (gap after the tie)
--    DENSE_RANK  → 1, 1, 2   (no gap)

That ROW_NUMBER dedup pattern is the single most-asked data-engineering SQL question there is. Write it until it is muscle memory.

Where windows run in the query

Logical order of evaluation:

FROM → WHERE → GROUP BY → HAVING → WINDOW → SELECT → ORDER BY → LIMIT

Two consequences that trip people up:

1. You cannot filter on a window function in WHERE. The window has not been computed yet. Wrap it in a CTE or subquery and filter outside — that is why the dedup pattern needs WITH ranked AS (...)
2. Windows run after GROUP BY, so a window over an aggregate is legal: SUM(SUM(fare)) OVER (ORDER BY day) computes a running total of daily sums. It looks wrong; it is correct.

The cost at volume

A window function generally forces a sort within each partition. On a billion-row table that is where your warehouse bill and your runtime go.

What actually helps:

• Filter before the window — prune partitions/dates in the CTE, not after
• Match the physical layout — if the table is clustered/partitioned by city, event_date and you PARTITION BY city, the engine skips whole files
• Prefer QUALIFY where supported (Snowflake, Databricks, BigQuery) — SELECT … QUALIFY ROW_NUMBER() OVER (…) = 1 avoids the extra CTE and lets the optimiser do better
• Watch for skew — one city with 40% of rides makes one task the whole runtime. Same skew lesson as Kafka partitioning, different engine
• Consider incremental models — recomputing a running total over all history every hour is the classic runaway warehouse cost

GROUP BY collapses. OVER() accompanies.

PARTITION BY = which group. ORDER BY inside OVER = the sequence within it.

Cannot filter a window in WHERE — it runs after. Use a CTE, or QUALIFY.

Gotchas

• ROW_NUMBER without a deterministic ORDER BY — ties break arbitrarily, so re-running the same dedup gives different rows. Add a tiebreaker like event_id
• WHERE rn = 1 in the same SELECT — syntax error; the window is not available yet
• Confusing RANK and DENSE_RANK in a "top 3" question — with ties, RANK can return more or fewer than 3
• Forgetting the frame clause — with ORDER BY present, the default frame is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, which with duplicate order values sums entire tied blocks. Use ROWS when you mean rows
• COUNT(*) OVER () over a huge table just to get a total — cheap-looking, expensive

Words to know

In two minutes

• GROUP BY collapses rows to one per group; a window function keeps every row and attaches the group's answer.
• PARTITION BY is the group, ORDER BY inside OVER() is the sequence, the frame is how much of it each row sees.
• ROW_NUMBER + a CTE (or QUALIFY) is the standard dedup: keep rn = 1 per key, ordered by latest event with a tiebreaker.
• On ties: ROW_NUMBER 1,2,3 · RANK 1,1,3 · DENSE_RANK 1,1,2 — pick based on what the business wants.
• LAG/LEAD compare a row with its neighbour: gaps between rides, day-on-day change, sessionisation.
• Windows run after WHERE and GROUP BY, so you can't filter on them in WHERE — but you can window over an aggregate.
• Cost is a sort per partition: filter first, align with the table's clustering, watch for skew.

Test yourself

There are 9 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.