0
Challenge · 27Composing Queries: CTEs & Window Functions
Window functions: aggregate without collapsing rows
SQLused in ~60% of apps
You want each post to show its like count AND how that compares to the author's average — on the same row, next to every other post. GROUP BY can give you the average, but the moment you group, every individual post disappears into one summary row per author. You needed both, on the same line.
Core idea. A window function computes an aggregate-like value per row, using OVER(...), without collapsing the rows the way GROUP BY does. PARTITION BY inside OVER draws the groups (like GROUP BY would); ORDER BY inside OVER defines a per-group sequence, which is what row_number(), rank(), and dense_rank() count along — and where they diverge is exactly on a tie.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
show
aliceWhy I switched to Postgreslikes 12count=5, avg=9.4
aliceRLS, explainedlikes 9count=5, avg=9.4
aliceCTEs vs subquerieslikes 9count=5, avg=9.4
aliceWindow functions clicked for melikes 15count=5, avg=9.4
aliceDraft: indexing noteslikes 2count=5, avg=9.4
bobShips on Fridayslikes 6count=3, avg=5.0
bobWhy I moved off ORMslikes 6count=3, avg=5.0
bobPostgres or bustlikes 3count=3, avg=5.0
carolHello, DevLoglikes 1count=1, avg=1.0
9 rows out -- every post survives, each carrying its author's totals.
↑ play a little first, then dig in