Window Functions: Seeing the Group Without Losing the Row

GROUP BY has one serious limitation: the moment it collapses rows into a summary, the detail is gone. You can find out that hive 101 produced 36.5 kg this year, but not which harvest was the biggest, or how much honey flow grew between visits, not without a second query. Window functions exist to answer exactly that kind of question: calculations that need to see an entire group, but still need to hand back an answer for every row in it.

To have a better understanding of the examples used in this article, have a look at these articles:

• SQL joins.
• Subqueries and CTES.
• SQL functions.

The four original harvests stay exactly as they were. Four new ones were added so each hive has enough history for a window function to actually do something interesting with it.

INSERT INTO harvests (harvest_id, hive_id, harvest_date, honey_kg, notes) VALUES
('H5', 102, '2024-08-01', 11.0, 'Late summer harvest'),
('H6', 103, '2024-07-20', 6.5,  'First harvest from this hive'),
('H7', 103, '2024-09-10', 8.0,  'Second harvest, steady growth'),
('H8', 101, '2024-11-01', 15.0, 'End of season harvest, strong flow');

harvests, in full

harvest_id	hive_id	harvest_date	honey_kg
H1	101	2024-05-01	12.5
H2	101	2024-09-01	9.0
H3	102	2024-06-15	15.0
H4	999	2024-07-01	5.0
H5	102	2024-08-01	11.0
H6	103	2024-07-20	6.5
H7	103	2024-09-10	8.0
H8	101	2024-11-01	15.0

Hive 999 is still the orphaned hive_id from the joins article, with no matching row in hives. It's staying in this dataset on purpose. A window function doesn't care whether a row has a valid foreign key anywhere else, it just processes whatever rows the query gives it.

The Anatomy of a Window Function

Every window function follows the same shape: a function, followed by OVER (...), followed optionally by PARTITION BY and ORDER BY inside those parentheses. Each piece answers a different question.

PARTITION BY decides which rows belong together the same way GROUP BY would. ORDER BY inside OVER() decides the sequence within that group, which matters enormously for anything about position, ranking, or what came before. Leaving both of them out leads to the function treating the entire result set as one window.

Here's the clearest way to see PARTITION BY and ORDER BY working together: number each hive's harvests in the order they happened.

SELECT hive_id, harvest_id, harvest_date, honey_kg,
    ROW_NUMBER() OVER (PARTITION BY hive_id ORDER BY harvest_date) AS harvest_seq
FROM harvests
ORDER BY hive_id, harvest_date;

Result:

hive_id	harvest_id	harvest_date	honey_kg	harvest_seq
101	H1	2024-05-01	12.5	1
101	H2	2024-09-01	9.0	2
101	H8	2024-11-01	15.0	3
102	H3	2024-06-15	15.0	1
102	H5	2024-08-01	11.0	2
103	H6	2024-07-20	6.5	1
103	H7	2024-09-10	8.0	2
999	H4	2024-07-01	5.0	1

Eight rows went in, eight rows came out, nothing collapsed. PARTITION BY hive_id reset the counter for every hive, and ORDER BY harvest_date decided which harvest counts as number one within each. Filter this result to harvest_seq = 1 and you have a clean list of each hive's very first harvest, useful for a "new hive is now productive" milestone report, without a single subquery.

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

ROW_NUMBER above never repeats a number, even when two rows are genuinely tied. RANK and DENSE_RANK handle ties differently, and the difference only shows up when a tie actually exists. Ranking the hives by their single best harvest produces one:

SELECT hive_id,
    MAX(honey_kg) AS best_harvest_kg,
    RANK()       OVER (ORDER BY MAX(honey_kg) DESC) AS honey_rank,
    DENSE_RANK() OVER (ORDER BY MAX(honey_kg) DESC) AS honey_dense_rank
FROM harvests
GROUP BY hive_id
ORDER BY best_harvest_kg DESC, hive_id;

Result:

hive_id	best_harvest_kg	honey_rank	honey_dense_rank
101	15.0	1	1
102	15.0	1	1
103	8.0	3	2
999	5.0	4	3

Hive 101's best single harvest, H8 at 15.0 kg, ties exactly with hive 102's H3. RANK gives both of them rank 1, then skips straight to rank 3 for the next hive, counting the two tied rows as if they'd occupied ranks 1 and 2. DENSE_RANK also gives both hives rank 1, but the next distinct value gets rank 2, no gap. Which one you want depends on the question: "top 3 hives by best harvest" reads differently depending on whether a tie for first should use up a slot.

NTILE takes a different approach to ranking: instead of ordering, it splits rows into a fixed number of equal-sized buckets. Say the co-op wants to flag its two weakest hives by total output for a closer inspection this winter:

SELECT hive_id, SUM(honey_kg) AS total_kg,
    NTILE(2) OVER (ORDER BY SUM(honey_kg) DESC) AS performance_tier
FROM harvests
GROUP BY hive_id
ORDER BY total_kg DESC;

Result:

hive_id	total_kg	performance_tier
101	36.5	1
102	26.0	1
103	14.5	2
999	5.0	2

NTILE(2) divides the four hives into two tiers of two. Hives 103 and 999 land in tier 2, the co-op's shortlist for a winter health check, generated without a single manually chosen cutoff value.

Offset Functions: LAG and LEAD

Ranking functions look at position. LAG and LEAD look sideways, at the row immediately before or after the current one, within the same partition and order. Hive 101 now has three harvests across the season, enough to ask how each one compares to its neighbors.

SELECT harvest_id, harvest_date, honey_kg,
    LAG(honey_kg)  OVER (PARTITION BY hive_id ORDER BY harvest_date) AS prev_kg,
    ROUND(honey_kg - LAG(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date), 1) AS change_kg,
    LEAD(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date) AS next_kg
FROM harvests
WHERE hive_id = 101
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	prev_kg	change_kg	next_kg
H1	2024-05-01	12.5	NULL	NULL	9.0
H2	2024-09-01	9.0	12.5	-3.5	15.0
H8	2024-11-01	15.0	9.0	6.0	NULL

LAG reaches backward, LEAD reaches forward, and both return NULL at the edges of the partition, there's nothing before H1 or after H8 for this hive. The change_kg column, honey this time minus honey last time, is the real payoff: a dip of 3.5 kg in September followed by a jump of 6.0 kg by November tells the beekeeper more than the raw totals ever would, and it took no self-join and no subquery to produce.

Aggregate Functions as Window Functions: Frames

Any aggregate function, SUM, AVG, COUNT, can run as a window function too, and this is where the frame clause matters, the part of OVER() that defines exactly which neighboring rows a calculation includes. Left unspecified with an ORDER BY present, the default frame runs from the start of the partition to the current row, which is what produces a running total. Specify the frame explicitly, and you can build something more targeted, like a two-harvest moving average across the whole co-op's season:

SELECT harvest_id, harvest_date, honey_kg,
    ROUND(AVG(honey_kg) OVER (
        ORDER BY harvest_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_2
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	moving_avg_2
H1	2024-05-01	12.5	12.5
H3	2024-06-15	15.0	13.75
H4	2024-07-01	5.0	10.0
H6	2024-07-20	6.5	5.75
H5	2024-08-01	11.0	8.75
H2	2024-09-01	9.0	10.0
H7	2024-09-10	8.0	8.5
H8	2024-11-01	15.0	11.5

No PARTITION BY this time, so every harvest across every hive is treated as one continuous timeline, a co-op-wide honey flow trend rather than a per-hive one. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW tells the database exactly which two rows to average at each step: the current one and the one right before it. The dip after H3's bumper 15.0 kg harvest and the climb back up toward H8 are both visible in the smoothed column in a way the raw honey_kg values alone don't show as clearly.

When to Reach for a Window Function

You need to...	Use
Number or sequence rows within a group, without collapsing it	ROW_NUMBER()
Rank rows, and gaps after ties are acceptable	RANK()
Rank rows, and ties shouldn't create gaps	DENSE_RANK()
Split rows into a fixed number of equal buckets	NTILE(n)
Compare a row to the one before or after it	LAG() / LEAD()
Get a running total, moving average, or other rolling calculation	Aggregate function OVER (...) with a frame

Try It Yourself

Take the moving-average query and change the frame to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, a three-harvest average instead of two. Watch how much smoother the trend line gets, and how much more it lags behind sudden changes like H3's bumper crop. That trade-off, smoothness against responsiveness, is the real decision behind every moving average, in a spreadsheet or in SQL.