Working with Window Functions
1. Understanding Window Functions
| vs Aggregate | Window |
|---|---|
| Rows returned | One per input row (no collapse) |
| Defined by | OVER (PARTITION BY ... ORDER BY ...) |
| Use case | Ranking, running totals, lag/lead |
2. Using ROW_NUMBER Function
SELECT id, customer_id,
row_number() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
FROM orders;3. Using RANK Function
SELECT name, score,
rank() OVER (ORDER BY score DESC) AS rnk
FROM players;
-- Ties share rank, next jumps (1,1,3)4. Using DENSE_RANK Function
SELECT name, score, dense_rank() OVER (ORDER BY score DESC) FROM players;
-- (1,1,2)| Function | Tie Handling |
|---|---|
| row_number | Unique sequential |
| rank | Same rank, gap after |
| dense_rank | Same rank, no gap |
5. Using NTILE Function
SELECT name, score, ntile(4) OVER (ORDER BY score DESC) AS quartile FROM players;6. Using LAG Function
SELECT day, revenue,
lag(revenue, 1) OVER (ORDER BY day) AS prev_day,
revenue - lag(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM daily_revenue;7. Using LEAD Function
SELECT event_at, lead(event_at) OVER (PARTITION BY session_id ORDER BY event_at) AS next_event
FROM events;8. Using FIRST_VALUE Function
SELECT customer_id, placed_at,
first_value(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS first_order_at
FROM orders;9. Using LAST_VALUE Function
SELECT customer_id, placed_at,
last_value(placed_at) OVER (
PARTITION BY customer_id ORDER BY placed_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_order_at
FROM orders;Warning: Default window frame ends at CURRENT ROW—
last_value needs explicit unbounded frame.10. Using NTH_VALUE Function
SELECT customer_id,
nth_value(placed_at, 2) OVER (
PARTITION BY customer_id ORDER BY placed_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_order_at
FROM orders;11. Using PARTITION BY Clause
SELECT customer_id, total_cents,
sum(total_cents) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;12. Using ORDER BY in Window
| Effect | Detail |
|---|---|
| Defines | Row sequence within partition |
| Required for | Running totals, lag/lead, ranking |
| Default frame | RANGE UNBOUNDED PRECEDING to CURRENT ROW |
13. Defining Window Frame
SELECT day, revenue,
avg(revenue) OVER (
ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7day_avg
FROM daily_revenue;14. Using Named Windows
SELECT id, customer_id,
row_number() OVER w AS rn,
sum(total_cents) OVER w AS running_total
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at);15. Using Aggregate Functions as Window Functions
SELECT day, revenue,
sum(revenue) OVER (ORDER BY day) AS cumulative,
100.0*revenue/sum(revenue) OVER () AS pct_of_total
FROM daily_revenue;