Lesson 1 of 5 in this module

11/33 in track

Window functions (the DE benchmark)

sql14 min

Overview

Window functions compute across related rows without collapsing them into one summary line.

On this page8 sections

The table we keep using

What does the slice look like?

Look at event_id. E17 appears twice, E42 3 times and E55 twice.

events_demo: 7 rows, but only 3 real events. E55 arrived twice in the same minute.

event_iduser_idevent_typeingest_time
E17U10purchase2026-08-01 03:02
E17U10purchase2026-08-01 03:09
E42U11page_view2026-08-01 02:58
E42U11page_view2026-08-01 03:07
E42U11page_view2026-08-01 03:19
E55U12add_to_cart2026-08-01 04:15
E55U12add_to_cart2026-08-01 04:15

Why does an events table hold the same event twice?

A phone sends 'user bought something', gets no answer, and sends it again. Now the table holds the event twice and the purchase count is wrong by one.

  1. Phone sends event
  2. No reply arrives
  3. Phone sends it again
  4. Table has 2 rows

Each row is one arrival. ingest_time is when the row reached our system.

Can you build this table yourself?

Yes. Run this once and every query below works on the same 7 rows.

SQLThe events table for this lesson
CREATE OR REPLACE TABLE events_demo (
  event_id VARCHAR, user_id VARCHAR, event_type VARCHAR, ingest_time TIMESTAMP
);
INSERT INTO events_demo VALUES
  ('E17', 'U10', 'purchase',    TIMESTAMP '2026-08-01 03:02:00'),
  ('E17', 'U10', 'purchase',    TIMESTAMP '2026-08-01 03:09:00'),
  ('E42', 'U11', 'page_view',   TIMESTAMP '2026-08-01 02:58:00'),
  ('E42', 'U11', 'page_view',   TIMESTAMP '2026-08-01 03:07:00'),
  ('E42', 'U11', 'page_view',   TIMESTAMP '2026-08-01 03:19:00'),
  ('E55', 'U12', 'add_to_cart', TIMESTAMP '2026-08-01 04:15:00'),
  ('E55', 'U12', 'add_to_cart', TIMESTAMP '2026-08-01 04:15:00');

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
Window functionA calculation across related rows that keeps every row
PARTITION BYSplit rows into groups for the window
ORDER BYSay which row comes first inside the group
ROW_NUMBERNumber the rows in a group 1, 2, 3
DuplicateThe same record stored twice
DedupeKeep one row from each group of copies

What we need from it

Why can't GROUP BY give us the whole row?

We want 1 row per event, the latest arrival, with all its columns. GROUP BY gets close. Predict how many rows come back.

SQLGROUP BY collapses each event to 1 row
SELECT event_id, COUNT(*) AS copies, MAX(ingest_time) AS latest
FROM events_demo
GROUP BY event_id
ORDER BY event_id;
event_id  copies  latest
E17       2       2026-08-01 03:09:00
E42       3       2026-08-01 03:19:00
E55       2       2026-08-01 04:15:00

3 rows, but user_id and event_type are gone. The other rows in each group were flattened away.

What does a window do differently?

A window looks at a group of rows without collapsing them. Every row stays, and each gets an extra column about its group.

Collapse or keep
GROUP BY event_idWindow: OVER (PARTITION BY event_id)vs7 rows in, 3 rows out1 row per groupIndividual rows are goneGood for totals7 rows in, 7 rows outEvery row staysEach row gets an extra column about itsgroupGood for ranking and comparing

Both look at a group of rows. Only one still lets you see each row afterwards.

GROUP BY collapses rows. A window function keeps them.

Trace it step by step

What is the shape of every window function?

The recipe has three parts and reads almost like English.

SQLThe shape of every window function
function() OVER (
  PARTITION BY  grouping_column   -- who shares a window
  ORDER BY      sort_column       -- the sequence inside it
)

PARTITION BY splits rows into independent groups, like GROUP BY but the rows stay. ORDER BY sorts the rows inside each group. Either part can be left out.

Step 1: how do you count the copies without losing any?

A plain COUNT becomes a window function when you add OVER. No ORDER BY is needed, because a count ignores order.

SQLHow many times did each event arrive?
SELECT event_id, ingest_time,
       COUNT(*) OVER (PARTITION BY event_id) AS copies
FROM events_demo
ORDER BY event_id, ingest_time;
event_id  ingest_time          copies
E17       2026-08-01 03:02:00  2
E17       2026-08-01 03:09:00  2
E42       2026-08-01 02:58:00  3
E42       2026-08-01 03:07:00  3
E42       2026-08-01 03:19:00  3
E55       2026-08-01 04:15:00  2
E55       2026-08-01 04:15:00  2

7 rows in, 7 rows out. Each row carries its group's count.

Step 2: how do you number the copies, newest first?

A count gives the group size. To pick 1 row you need a rank. ROW_NUMBER() hands out 1, 2, 3 inside each partition, and DESC puts the newest at 1.

7 rows in three dashed boxes, one box per event_id. Inside each box rows are sorted by ingest_time from newest to oldest and numbered rn 1, 2 and so on. The first row of every box is highlighted green.
Each dashed box is a partition. The numbering restarts at 1 in every box, and rn = 1 marks the newest copy.

In the E42 box the arrivals sort as 03:19, 03:07, 02:58, so they get 1, 2, 3. Then numbering restarts for E55. The green rows are the ones we keep.

SQLRank each arrival, newest = 1
SELECT event_id, user_id, event_type, ingest_time,
       ROW_NUMBER() OVER (
         PARTITION BY event_id
         ORDER BY ingest_time DESC
       ) AS rn
FROM events_demo
ORDER BY event_id, rn;
event_id  user_id  event_type   ingest_time          rn
E17       U10      purchase     2026-08-01 03:09:00  1
E17       U10      purchase     2026-08-01 03:02:00  2
E42       U11      page_view    2026-08-01 03:19:00  1
E42       U11      page_view    2026-08-01 03:07:00  2
E42       U11      page_view    2026-08-01 02:58:00  3
E55       U12      add_to_cart  2026-08-01 04:15:00  1
E55       U12      add_to_cart  2026-08-01 04:15:00  2

Still 7 rows. The new column rn is what we filter on. Try the player below, and predict each step before you reveal it.

Same table, next cut

Cut 1: why does WHERE rn = 1 fail?

WHERE runs before window functions are calculated, so rn does not exist yet. Predict the error.

SQLFiltering on a window result with WHERE
SELECT event_id, user_id, event_type, ingest_time,
       ROW_NUMBER() OVER (
         PARTITION BY event_id ORDER BY ingest_time DESC
       ) AS rn
FROM events_demo
WHERE rn = 1;
Binder Error: WHERE clause cannot contain window functions!

LINE 2:        ROW_NUMBER() OVER (
               ^

DuckDB lets WHERE see the alias rn and then complains about the window function inside it. Other engines say the column rn does not exist. Either way, the rule is the same.

How does QUALIFY keep the latest row?

QUALIFY is the clause that filters on a window result. It runs after the window is calculated.

SQL1 row per event: the latest arrival
SELECT event_id, user_id, event_type, ingest_time
FROM events_demo
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY event_id
  ORDER BY ingest_time DESC
) = 1
ORDER BY event_id;
event_id  user_id  event_type   ingest_time
E17       U10      purchase     2026-08-01 03:09:00
E42       U11      page_view    2026-08-01 03:19:00
E55       U12      add_to_cart  2026-08-01 04:15:00

7 rows became 3: 1 row per real event, with all its columns.

Rank in a window, then keep rank 1.

What if your database has no QUALIFY?

DuckDB, Snowflake and BigQuery have it. PostgreSQL and MySQL do not. The version that works everywhere ranks in a CTE, then filters.

SQLThe same dedupe without QUALIFY
WITH ranked AS (
  SELECT event_id, user_id, event_type, ingest_time,
         ROW_NUMBER() OVER (
           PARTITION BY event_id
           ORDER BY ingest_time DESC
         ) AS rn
  FROM events_demo
)
SELECT event_id, user_id, event_type, ingest_time
FROM ranked
WHERE rn = 1
ORDER BY event_id;
event_id  user_id  event_type   ingest_time
E17       U10      purchase     2026-08-01 03:09:00
E42       U11      page_view    2026-08-01 03:19:00
E55       U12      add_to_cart  2026-08-01 04:15:00

Same 3 rows. This is the standard way to drop late or replayed duplicates from an event stream. You will meet it again in the PySpark and streaming tracks.

Watch outTies pick an arbitrary winner

E55 arrived twice at exactly 04:15, so ROW_NUMBER can give rn = 1 to either copy. Here the 2 rows are identical, so it does not matter. When copies differ, add a second sort column, such as ORDER BY ingest_time DESC, event_id.

Cut 2: how do ROW_NUMBER, RANK and DENSE_RANK treat ties?

2 rows can share a sort value. Three functions handle that differently. Here is a small scores table to see it.

SQLScores for two departments
CREATE OR REPLACE TABLE scores_demo (department VARCHAR, student_name VARCHAR, score INTEGER);
INSERT INTO scores_demo VALUES
  ('Data', 'Aarav', 95), ('Data', 'Diya', 90), ('Data', 'Kabir', 90),
  ('Data', 'Neha', 85),  ('Data', 'Rohan', 75),
  ('Cloud', 'Pooja', 98), ('Cloud', 'Ananya', 92), ('Cloud', 'Vikram', 92);

Predict the numbers for Diya and Kabir (both 90) and for Neha (85).

SQLROW_NUMBER vs RANK vs DENSE_RANK on a tie
SELECT student_name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC, student_name) AS row_num,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM scores_demo
WHERE department = 'Data'
ORDER BY score DESC, student_name;
student_name  score  row_num  rnk  dense_rnk
Aarav         95     1        1    1
Diya          90     2        2    2
Kabir         90     3        2    2
Neha          85     4        4    3
Rohan         75     5        5    4

Diya and Kabir tie. RANK gives both 2, then skips 3 and jumps to 4. DENSE_RANK gives both 2 and continues with 3.

Pick by what should happen after a tie.

FunctionResult on the tieUse it for
ROW_NUMBER()1, 2, 3, 4Exactly one winner, such as deduplication
RANK()1, 2, 2, 4Podium ranks with gaps
DENSE_RANK()1, 2, 2, 3Tiers without gaps, such as top-N salary levels

ROW_NUMBER never repeats, so we added student_name to settle the tie.

Cut 3: how do you build a running total?

A window can look at neighbouring rows in sequence. For that we need numbers over days. Here are 5 days of revenue.

SQL5 days of revenue
CREATE OR REPLACE TABLE daily_demo (report_date DATE, revenue DOUBLE);
INSERT INTO daily_demo VALUES
  (DATE '2026-08-01', 1000), (DATE '2026-08-02', 1500), (DATE '2026-08-03', 1200),
  (DATE '2026-08-04', 1800), (DATE '2026-08-05', 2000);

A frame (ROWS BETWEEN) says which neighbouring rows the window may use. Predict day 3's moving average of 3 days.

SQLRunning total and 3-day moving average
SELECT report_date, revenue,
       SUM(revenue) OVER (
         ORDER BY report_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       ROUND(AVG(revenue) OVER (
         ORDER BY report_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 1) AS moving_avg_3d
FROM daily_demo
ORDER BY report_date;
report_date  revenue  running_total  moving_avg_3d
2026-08-01   1000     1000           1000
2026-08-02   1500     2500           1250
2026-08-03   1200     3700           1233.3
2026-08-04   1800     5500           1500
2026-08-05   2000     7500           1666.7

Day 3 is (1000 + 1500 + 1200) / 3 = 1233.3. The 3-row window slides forward 1 row each day.

These frames come up most often.

  • UNBOUNDED PRECEDING to CURRENT ROW: a running total from the first row.
  • 2 PRECEDING to CURRENT ROW: the current row plus the two before it.
  • 1 PRECEDING to 1 FOLLOWING: a centred 3-row average.
  • UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING: the whole partition, for percent of total or global extremes.

What frame do you get if you write none?

With an ORDER BY inside OVER and no frame, the default runs from the first row to the current row. So the default SUM is also a running total.

SQLNo frame written
SELECT report_date, revenue,
       SUM(revenue) OVER (ORDER BY report_date) AS default_frame_total
FROM daily_demo
ORDER BY report_date;
report_date  revenue  default_frame_total
2026-08-01   1000     1000
2026-08-02   1500     2500
2026-08-03   1200     3700
2026-08-04   1800     5500
2026-08-05   2000     7500

The totals match the explicit version. But the default is RANGE, not ROWS, and that difference shows on ties.

What is the difference between ROWS and RANGE?

ROWS counts physical rows, while RANGE treats equal sort values as peers. The two E55 rows tie on ingest_time. Predict the last two counts.

SQLDefault (RANGE) frame against ROWS on a tie
SELECT event_id, ingest_time,
       COUNT(*) OVER (ORDER BY ingest_time) AS running_range,
       COUNT(*) OVER (
         ORDER BY ingest_time
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_rows
FROM events_demo
ORDER BY ingest_time, event_id;
event_id  ingest_time          running_range  running_rows
E42       2026-08-01 02:58:00  1              1
E17       2026-08-01 03:02:00  2              2
E42       2026-08-01 03:07:00  3              3
E17       2026-08-01 03:09:00  4              4
E42       2026-08-01 03:19:00  5              5
E55       2026-08-01 04:15:00  7              6
E55       2026-08-01 04:15:00  7              7

RANGE counts both E55 rows at once, so both show 7. ROWS counts 1 row at a time, 6 then 7.

Everything up to here is the core of window functions. Cuts 4 to 7 are extras that come up in reports and interviews. Read them now, or come back once the dedupe pattern feels natural.

Cut 4: why does LAST_VALUE just repeat the current row?

FIRST_VALUE and LAST_VALUE return the first or last value in the frame. The default frame stops at the current row. Predict last_rev_trap.

SQLThe LAST_VALUE trap and its fix
SELECT report_date, revenue,
       FIRST_VALUE(revenue) OVER (ORDER BY report_date) AS first_rev,
       LAST_VALUE(revenue)  OVER (ORDER BY report_date) AS last_rev_trap,
       LAST_VALUE(revenue)  OVER (
         ORDER BY report_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_rev_fixed
FROM daily_demo
ORDER BY report_date;
report_date  revenue  first_rev  last_rev_trap  last_rev_fixed
2026-08-01   1000     1000       1000           2000
2026-08-02   1500     1000       1500           2000
2026-08-03   1200     1000       1200           2000
2026-08-04   1800     1000       1800           2000
2026-08-05   2000     1000       2000           2000

last_rev_trap copies each row's own revenue, because the frame ends at the current row. To see the true last value, extend the frame to UNBOUNDED FOLLOWING.

The default frame ends at the current row, so LAST_VALUE cannot see ahead.

Cut 5: how do you get day-over-day growth?

LAG fetches the value from the previous row. Predict growth_pct for day 2.

SQLGrowth percent with LAG
SELECT report_date, revenue,
       LAG(revenue) OVER (ORDER BY report_date) AS prev_day_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY report_date))
         / NULLIF(LAG(revenue) OVER (ORDER BY report_date), 0), 1) AS growth_pct
FROM daily_demo
ORDER BY report_date;
report_date  revenue  prev_day_revenue  growth_pct
2026-08-01   1000     NULL              NULL
2026-08-02   1500     1000              50
2026-08-03   1200     1500              -20
2026-08-04   1800     1200              50
2026-08-05   2000     1800              11.1

Day 1 has no previous row, so LAG gives NULL. NULLIF avoids dividing by zero. LEAD does the same thing looking forward.

How do you get percent of total?

Use an empty OVER (). It makes one window out of the whole table, so SUM(revenue) OVER () is the grand total on every row.

SQLEach day's share of the total
SELECT report_date, revenue,
       SUM(revenue) OVER () AS grand_total,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM daily_demo
ORDER BY report_date;
report_date  revenue  grand_total  pct_of_total
2026-08-01   1000     7500         13.3
2026-08-02   1500     7500         20
2026-08-03   1200     7500         16
2026-08-04   1800     7500         24
2026-08-05   2000     7500         26.7

August 5 brought in 26.7 percent of the week's revenue.

Cut 6: how do you split rows into quartiles?

NTILE(n) deals rows into n roughly equal buckets, numbered 1 to n. It is common in customer scoring.

SQLFour performance quartiles
SELECT student_name, score,
       NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM scores_demo
WHERE department = 'Data'
ORDER BY score DESC;
student_name  score  quartile
Aarav         95     1
Diya          90     1
Kabir         90     2
Neha          85     3
Rohan         75     4

5 rows into 4 buckets: the first bucket gets the extra row. Kabir and Diya tie at 90 yet land in different quartiles, so NTILE splits ties.

Which of the two lands in quartile 1 is not guaranteed, so add a tie-break column to the ORDER BY when it matters.

How do you find the top 2 per group?

This is a classic interview question. Rank inside each department in a CTE, then keep ranks 1 and 2.

SQLTop 2 score tiers in every department
WITH ranked AS (
  SELECT department, student_name, score,
         DENSE_RANK() OVER (
           PARTITION BY department
           ORDER BY score DESC
         ) AS rnk
  FROM scores_demo
)
SELECT department, student_name, score, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY department, rnk, student_name;
department  student_name  score  rnk
Cloud       Pooja         98     1
Cloud       Ananya        92     2
Cloud       Vikram        92     2
Data        Aarav         95     1
Data        Diya          90     2
Data        Kabir         90     2

Each department returns its top 2 tiers. DENSE_RANK keeps both tied students; ROW_NUMBER would have dropped one.

Cut 7: how do you reuse one window?

When several functions share the same window, name it once with WINDOW at the end of the query.

SQLOne named window, two functions
SELECT report_date, revenue,
       SUM(revenue) OVER w AS running_total,
       ROUND(AVG(revenue) OVER w, 1) AS running_avg
FROM daily_demo
WINDOW w AS (
  ORDER BY report_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY report_date;
report_date  revenue  running_total  running_avg
2026-08-01   1000     1000           1000
2026-08-02   1500     2500           1250
2026-08-03   1200     3700           1233.3
2026-08-04   1800     5500           1375
2026-08-05   2000     7500           1500

Both columns use the same window, defined once as w.

Watch outTwo different ORDER BY clauses

ORDER BY inside OVER decides which row is first within each partition. The ORDER BY at the very end of the query decides how the final result is displayed. They are independent, and getting rn right says nothing about the display order.

How does the whole dedupe run, start to finish?

Deduplicating events, end to end
  1. events_demo has 7 rows but only 3 real events, because phones retry.

  2. PARTITION BY event_id puts the copies of each event in one window.

  3. ORDER BY ingest_time DESC with ROW_NUMBER gives the newest copy rn = 1.

  4. QUALIFY ... = 1 (or a CTE with WHERE rn = 1) keeps only that row.

  5. 7 rows become 3, each with user_id and event_type intact.

Keep these.

  1. A window function adds a column and keeps every row. GROUP BY collapses them.
  2. OVER (PARTITION BY who ORDER BY sequence) is the shape of every window.
  3. WHERE cannot see a window result. Use QUALIFY, or rank in a CTE and filter after.
  4. Dedupe recipe: ROW_NUMBER() newest first, keep rn = 1. Add a tie-breaker.
  5. Write the frame when you use LAST_VALUE or moving averages.
Watch outWHERE cannot see a window result

WHERE runs before window functions, so WHERE rn = 1 fails. Use QUALIFY if your database has it, or compute the window in a CTE and filter in the next query.

RememberDedupe recipe

ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingest_time DESC), then keep rn = 1 in a later step.

Common beginner questions

Do I always need PARTITION BY?

No. Leave it out and the entire result is one big partition. ROW_NUMBER() OVER (ORDER BY ingest_time DESC) would number all 7 rows from 1 to 7, which is useful for a global ranking.

Can I use more than one window function in the same query?

Yes. Each has its own OVER clause, and they can use different partitions and orders, like the rn and rnk columns above.

What is the difference between ROWS and RANGE in window frames?

ROWS counts physical rows (for example, 2 PRECEDING means exactly 2 physical rows back). RANGE looks at value offsets in the ORDER BY column, treating rows with identical sort values as peers and expanding the frame to include all ties.

Why does LAST_VALUE repeat the current row without an explicit frame?

Because ANSI SQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when an ORDER BY is present. The frame stops at the current row, so the engine cannot see any later values unless you specify UNBOUNDED FOLLOWING.

What comes next

You now have the full set of analytical window tools: frames, rankings, offsets, running totals, and moving averages.

The next lesson, Data cleaning, takes the other side of the job, fixing messy values like NULL statuses, padded text and wrong date grains, so that the rows you group and rank are trustworthy to begin with.

Practice

The exercise asks for a ROW_NUMBER() column called rn, partitioned by event_id and ordered by ingest_time descending, returning event_id, ingest_time and rn for 20 rows.

Run the sample first to see QUALIFY keep only the winners. Then write your own version that shows every row with its number, and check that the numbering restarts at 1 for each new event_id.

Going deeper: window or GROUP BY, and the cases that surprise people

You already know the pieces: PARTITION BY, ORDER BY, ranking functions and frames. Here is one table to keep in front of you while you decide between a window and a GROUP BY, and while you look at the edge cases that produce wrong numbers without any error.

The sales table for this section. Esha's amount is missing on purpose.

regionrepdayamount
NorthAsha2026-09-01100
NorthBen2026-09-01100
NorthChen2026-09-0280
SouthDev2026-09-0160
SouthEsha2026-09-02NULL
SouthFarid2026-09-0390

The first question: fewer rows, or the same rows?

SQLQuery
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region
ORDER BY region;

GROUP BY returns 1 row per region. The window version keeps all 6 rows and attaches the regional total to each, so you can still compare a rep with the total. Predict how many rows each query returns before you open the outputs.

region  region_total
North   280
South   150
region  rep    amount  region_total
North   Asha   100     280
North   Ben    100     280
North   Chen   80      280
South   Dev    60      150
South   Esha   NULL    150
South   Farid  90      150

Case 1: a NULL changes more than one result

NULL is not zero. Esha's missing amount is ignored by SUM and AVG, but it still occupies a row. LAG with a default only fills in when there is no previous row at all; a previous row that holds NULL gives you NULL back. Predict Farid's prev_or_zero.

SQLQuery
SELECT rep, amount,
       LAG(amount, 1, 0) OVER (PARTITION BY region ORDER BY day) AS prev_or_zero
FROM sales
WHERE region = 'South'
ORDER BY day;
rep    amount  prev_or_zero
Dev    60      0
Esha   NULL    60
Farid  90      NULL

Farid's previous row is Esha, whose amount is NULL, so the answer is NULL, not 0. If you want 0, wrap the value: COALESCE(amount, 0) before you take the LAG, or around the result.

Decide what NULL should mean first. Also note where NULLs sort: engines differ on whether NULLs come first or last in a descending sort, so write NULLS LAST or NULLS FIRST when it matters.

Case 2: a tie makes ROW_NUMBER an arbitrary choice

Asha and Ben both sold 100. ROW_NUMBER must give them different numbers, and with ORDER BY amount alone the database may order them either way, and that can change from run to run. RANK gives both 1, and DENSE_RANK also gives both 1 but continues with 2 afterwards.

SQLQuery
SELECT rep, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC, rep) AS rn,
       RANK()       OVER (ORDER BY amount DESC)      AS rk,
       DENSE_RANK() OVER (ORDER BY amount DESC)      AS dr
FROM sales
WHERE region = 'North'
ORDER BY rn;
rep   amount  rn  rk  dr
Asha  100     1   1   1
Ben   100     2   1   1
Chen  80      3   3   2

Look at Chen: RANK jumps to 3 because two people are ahead, while DENSE_RANK goes to 2. When you need exactly 1 row per group, add a tie-breaker (here, rep) to the ORDER BY so the choice is repeatable.

Case 3: you cannot filter on a window result directly

WHERE runs before windows are computed, so WHERE rn = 1 is invalid. Compute the number in a CTE or subquery, then filter outside it.

SQLQuery
WITH ranked AS (
  SELECT region, rep, amount,
         ROW_NUMBER() OVER (PARTITION BY region
                            ORDER BY amount DESC NULLS LAST, rep) AS rn
  FROM sales
)
SELECT region, rep, amount
FROM ranked
WHERE rn = 1
ORDER BY region;
region  rep    amount
North   Asha   100
South   Farid  90

Try a new scenario

For every row in the sales table, show the gap between that rep's amount and the best amount in the same region, and a running total of amount by day within the region. Break ties by rep. Predict the gap for Esha and the running total for Esha before you check.

SQLQuery
SELECT region, rep, day, amount,
       MAX(amount) OVER (PARTITION BY region) - amount AS gap_to_best,
       SUM(amount) OVER (PARTITION BY region
                         ORDER BY day, rep
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales
ORDER BY region, day, rep;
Open the answer

The gap is NULL for Esha, because the best amount minus NULL is NULL. The running total for Esha is 60, because SUM ignores NULL and the earlier row contributed 60. Output: North rows gap 0, 0, 20 with totals 100, 200, 280. South rows gap 30, NULL, 0 with totals 60, 60, 150.

Why does ROWS BETWEEN matter when the running total uses ORDER BY day, rep?

Because day alone has a tie in North on 2026-09-01. With ORDER BY day and the default RANGE frame, Asha and Ben are peers and both see 200. Adding rep as a tie-breaker and using ROWS makes each row see only the rows up to itself.

Close the lesson and explain it

Without scrolling up, explain when you would choose a window function over GROUP BY, and name one way a NULL or a tie can make a correct-looking query wrong.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Practice this idea

Each page shows the idea moving, then lists problems from easiest. Open one now, or come back when you finish the lesson.

Practice this

Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.

Rate:
Was this useful?