Overview
Window functions compute across related rows without collapsing them into one summary line.
On this page7 sections
The table we keep using
Source data
Events table with duplicates: E17 and E42 each appear twice with different ingest times.
| event_id | user_id | event_type | ingest_time |
|---|---|---|---|
| E17 | U10 | purchase | 2026-08-01 03:02 |
| E17 | U10 | purchase | 2026-08-01 03:09 |
| E42 | U11 | page_view | 2026-08-01 02:58 |
| E42 | U11 | page_view | 2026-08-01 03:07 |
| E55 | U12 | add_to_cart | 2026-08-01 04:15 |
A window function performs a calculation across a group of related rows without collapsing them into a single result. Unlike GROUP BY which merges rows into summary lines, a window function keeps every individual row and adds a new column with the computed value beside it.
The keyword OVER defines the window. PARTITION BY inside OVER creates groups (like GROUP BY but without collapsing). ORDER BY inside OVER defines the sequence within each group. Together they answer: 'For each row, what is its rank/sum/count within its group?'
Common window functions include ROW_NUMBER() (assigns sequential numbers), RANK() (handles ties), LAG() (looks at the previous row), LEAD() (looks at the next row), SUM() OVER (running total), and COUNT() OVER (running count).
What we need from it
An e-commerce platform receives duplicate event records (the same click tracked twice due to retry logic). You need to keep only the latest copy of each event. GROUP BY cannot easily do this because you need all the columns from the winning row, not just an aggregate. ROW_NUMBER() with PARTITION BY event_id lets you rank duplicates and keep only the winner.
Window functions also enable: ranking customers by spend, computing running totals of daily revenue, comparing each order to the previous one, and calculating moving averages. These are questions where you need both the detail row and its context within a group.
Trace it step by step
The syntax is: function_name() OVER (PARTITION BY grouping_column ORDER BY sorting_column). PARTITION BY is optional (without it, the entire result is one group). ORDER BY inside OVER defines the sequence for ranking or running calculations.
Each partition restarts numbering. DESC order puts the newest row as number 1.
Trace the window
GROUP BY would collapse Ada's orders. A window keeps every row and writes the city's rank beside it. Predict before the next step.
Same table, next cut
Example 1: ROW_NUMBER for ranking
ROW_NUMBER() assigns 1, 2, 3... within each partition. By ordering DESC on ingest_time, the most recent copy gets rank 1.
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
SELECT
event_id,
user_id,
event_type,
ingest_time,
ROW_NUMBER() OVER (
PARTITION BY event_id
ORDER BY ingest_time DESC
) AS rn
FROM ecommerce_events;Result: every row is preserved, but now each has a rank within its event_id partition.
| event_id | user_id | event_type | ingest_time | rn |
|---|---|---|---|---|
| E17 | U10 | purchase | 2026-08-01 03:09 | 1 |
| E17 | U10 | purchase | 2026-08-01 03:02 | 2 |
| E42 | U11 | page_view | 2026-08-01 03:07 | 1 |
| E42 | U11 | page_view | 2026-08-01 02:58 | 2 |
| E55 | U12 | add_to_cart | 2026-08-01 04:15 | 1 |
Example 2: LAG to compare with previous row
LAG(column) returns the value from the previous row in the ordered partition. This is useful for comparing each order to the customer's previous order.
SELECT
order_id,
user_id,
order_status,
created_at,
LAG(order_status) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS previous_status
FROM orders
ORDER BY user_id, created_at;Result: the first order per user has NULL previous_status (no earlier row exists).
| order_id | user_id | order_status | created_at | previous_status |
|---|---|---|---|---|
| O0001 | U100 | paid | 2026-08-01 | NULL |
| O0004 | U100 | pending | 2026-08-03 | paid |
| O0002 | U101 | cancelled | 2026-08-01 | NULL |
| O0003 | U102 | paid | 2026-08-02 | NULL |
Example 3: Filtering window results with QUALIFY
You cannot filter on a window function in WHERE because WHERE runs before windows are computed. The QUALIFY clause filters after window calculations, making it perfect for deduplication.
SELECT
event_id,
user_id,
event_type,
ingest_time
FROM ecommerce_events
QUALIFY ROW_NUMBER() OVER (
PARTITION BY event_id
ORDER BY ingest_time DESC
) = 1;Result: one row per event_id, keeping the newest ingest_time.
| event_id | user_id | event_type | ingest_time |
|---|---|---|---|
| E17 | U10 | purchase | 2026-08-01 03:09 |
| E42 | U11 | page_view | 2026-08-01 03:07 |
| E55 | U12 | add_to_cart | 2026-08-01 04:15 |
Rank within partitions, then keep only winners.
Two different ORDER BY clauses
ORDER BY inside OVER() defines the calculation sequence (which row gets rank 1). ORDER BY at the end of the query defines the display order. They serve completely different purposes.
WHERE cannot filter window results
Window functions run after WHERE. Writing WHERE rn = 1 fails because rn does not exist yet. Use QUALIFY (if supported) or wrap the window query in a CTE and filter the outer query.
The dedup pattern
ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC) then keep rn = 1. This is the standard pattern for deduplicating event streams with late-arriving data.
GROUP BY collapses rows; window functions annotate them.
| Question | GROUP BY approach | Window function approach |
|---|---|---|
| Events per user | One row per user with COUNT | Every event row plus a count column |
| Latest event per ID | MAX(ingest_time) but lose other columns | All columns from the winning row |
| Previous order status | Not possible directly | LAG(status) shows prior value |
| Running total by day | Not possible without self-join | SUM() OVER (ORDER BY day) adds rows up |
Copy-paste without reading the output
Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.
Common beginner questions
What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?
ROW_NUMBER always assigns unique numbers (1, 2, 3). RANK gives tied rows the same number but skips the next (1, 1, 3). DENSE_RANK gives ties the same number without skipping (1, 1, 2). For deduplication, always use ROW_NUMBER.
Do I need PARTITION BY?
No. Without it, the entire result set is one group. This is useful for things like ranking all orders globally rather than per customer.
Can I use multiple window functions in one query?
Yes. Each window function can have its own OVER clause with different partitions and orderings.
What comes next
Window functions enable deduplication and sequencing. The next lesson covers data cleaning: handling NULLs, trimming whitespace, normalizing types, and applying business rules to messy source data.
Practice
Run Sample to dedupe events with QUALIFY. Then complete the exercise: compute ROW_NUMBER() partitioned by event_id, ordered by ingest_time DESC, as rn. Return event_id, ingest_time, and rn for 20 rows.
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.
Practice this
Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.
- Cumulative orders per productInterview-style drill: Cumulative count of orders for each product over time.Studiointermediatesql12 minPro
- Highest and lowest salary per departmentInterview-style drill: FIRST_VALUE / LAST_VALUE for top and bottom earner names per department.Studiointermediatesql12 minPro
- Month-over-month revenue with LAGInterview-style drill: Monthly revenue with previous month via LAG.Studiointermediatesql12 minPro