0:00Window functions let one roe see its whole group.. compute across it.. and still stay a roe.
0:06Group by summarizes and throws the roes away. A window function keeps every one.
0:12Here's the plan. First, we'll number the roes inside each group..
0:17then sort out what happens to a rank when two values tie.
0:21We'll use those numbers to grab the latest roe per group, the top few, and to drop duplicates..
0:27then reach back and compare each roe with the one before it..
0:30and finish by turning a plain sum into a running total.
0:34One small table runs every example in this video. Six orders.. three customers.
0:39Keep an eye on Ana. Three orders.. one hundred twenty, eighty, and one hundred twenty again.
0:47Say we want each customer's total. Group by customer is the reflex, and it works.
0:53But watch the roe count. Six orders just collapsed into three totals. Ana adds up to three hundred twenty.. and her individual orders are gone.
1:02Now the same sum as a window function. The over clause tells sum to compute across each customer's partition.. without collapsing anything.
1:11All six roes are still here. The total didn't replace the orders.. it got attached to each one.
1:17Every one of Ana's roes now carries her three hundred twenty, right next to the order that contributed to it.
1:22That's the whole difference. Group by gives you fewer roes than you started with. A window function gives you the same roes, with one more column on each.
1:33So what else fits inside an over clause? Plenty. Roe number, to start.. it hands out consecutive integers, one per roe.
1:42The function itself is simple. The interesting part is everything inside over.. that clause decides which roes get numbered together, and in what sequence.
1:53Partition by customer splits the six roes into three separate groups. Each group gets its own private numbering.
2:00Then order by "Order Date" sequences the roes inside each group.
2:05Earliest first <break time="0.3s" /> so the oldest order lands at number one.
2:09Run it, and watch Ana count up. One, two, three.
2:14Ben starts over at one. So does Cai. Every partition boundary resets the counter.
2:20One caution before we use this. The "order by" inside the "over" block sequences the calculation only. It does not sort your output.
2:28If the display order matters, add a normal "order by" at the very end.
2:34Roe number isn't alone, though. Sequel gives you three ranking functions.. and they only differ in one situation.
2:42We're keeping just Ana's orders, so the whole result is one small partition.
2:46And notice.. no partition by this time. All the remaining roes form a single window, ordered by amount, highest first.
2:55Two of her orders tie at one hundred twenty. That tie is exactly where the three functions split apart.
3:01Roe number says one, two, three. It breaks the tie, but arbitrarily.
3:06Without a tiebreaker in the order by, which one hundred twenty comes first is up to the database.
3:13Rank says one, one, three. <break time="0.2s" /> The tied roes share first place.. and second place simply never gets awarded.
3:21Dense rank closes that gap. One, one, two. Eighty is the second distinct amount, so it gets a two.
3:28So match the function to your question. Unique positions.. competition standings.. or distinct value levels.
3:38Now the workhorse pattern. You want each customer's most recent order.. so number them newest first, and keep number one. The tempting move is to do it all in the where clause.
3:48Postgres says no. Window functions aren't allowed in where.
3:52Here's why. Inside a query block, where filters roes "before" any window function runs.
3:59At filtering time, r n simply doesn't exist yet.
4:04So split the work. Compute the number inside a CTE called ranked, <break time="0.2s" /> dates descending, which hands every customer's newest order the number one.
4:13Then filter outside. By the time the outer query reads from ranked, r n is just a normal column.. and where can see it.
4:21Three customers, three latest orders. That's the pattern.. compute first, filter outside.
4:28Two small edits stretch it further. Add order ID as a tiebreaker, in case two orders ever share a date.. and relax the filter to R N at most two.
4:38Top two per customer. Two for Ana, two for Ben.. and Cai only has one to give.
4:45The same pattern cleans up duplicates, too. This input repeats order three on purpose, like a file that got imported twice.
4:54Partition by the duplicate key, order I-D. Both copies of order three land in the same window.. and only r n one survives the filter.
5:03Seven roes in, six roes out. And if the copies ever disagree, the order by decides which version wins.
5:11Next, letting every roe look at the one before it. That's lag.
5:16Same window as always.. per customer, ordered by date. Lag of amount means: whatever the previous order's amount was. Subtract it, and you've got the change.
5:28Watch Ana's second order. Its prev amount is one hundred twenty.. reached back from her first.
5:34Her third order reaches back and finds eighty. The window's order decides who counts as previous.
5:41And every customer's first order comes up empty. Nothing sits before it in its own partition.. lag never crosses into another customer's roes.
5:51The subtraction fills in the change column. Ana drops forty.. then climbs right back up forty.
5:57If you ever need the other direction, lead reads the next roe instead. Same window, opposite direction.
6:05One pattern left. Remember our first window sum.. it stamped the whole customer total onto every roe. All of Ana's roes said three hundred twenty.
6:16Give that same sum an order by, and the frame shrinks. Each roe now sums only the roes up to itself.
6:23Ana accumulates. One hundred twenty.. two hundred.. three hundred twenty. Each roe adds itself to everything before it.
6:31And Ben inherits none of it. His count restarts at two hundred.. the partition keeps every customer's math separate.
6:38One caveat. When two roes share a date, the default frame counts them together.
6:43For strict roe by roe totals, add a tiebreaker and spell the frame out.. roes between unbounded preceding and current roe.
6:51None of this is special to sum, either. Swap in average or count.. same frame, and you've got running averages and running counts.
7:02So here's what you walk away with.
7:04You can attach a group's answer to its individual roes.. instead of trading the roes for it.
7:10You can define the window yourself.. who shares it, and what order they stand in.
7:15You know which ranking function fits which kind of tie.
7:18You've got the workhorse.. number the roes in a CTE, then filter outside. Latest, top N, and dedup all fall out of that one move.
7:27You can reach back to the previous roe..
7:29and one order by turns a plain sum into a running total.
7:34That's window functions. When a query needs the group total "and" the individual roes, a window gives you both.

SQL Window Functions

Created by Abhyuday DesaiPublished 3 Oct, v1Runs 7 min 50 sec

0 views

About this video

Window functions compute across rows while keeping every row, which is what the hard tier of interview questions usually asks for: latest per group, top N, deduplication, running totals. One six-row orders table powers every example.

You'll review how to:

Tell GROUP BY from OVER, and define a window with PARTITION BY and ORDER BY
Rank ties with ROW_NUMBER, RANK and DENSE_RANK, and say which one the question wants
Compute in a CTE and filter outside it for latest-row, top-N and duplicate questions
Compare a row with the previous one using LAG, and build a running total with an ordered frame

For engineers who know SELECT, JOIN, GROUP BY and CTEs. Ranking and per-group questions, the most common hard tier.

What changed

v103 Oct 2026abhydesai7 min 50 sec037acd5

First published version.

0 views0 likes on this version