Advanced SQL Window Functions: Practice Problems and Solutions
This post assumes you already know what window functions are. If you're new to them, start with the SQL Window Functions Guide — it covers syntax, PARTITION BY, ORDER BY, and the nine core patterns with worked examples.
This post is for practice and advanced patterns: problems you'll see in technical interviews, real analytics work, and edge cases that trip up developers who know the basics but haven't drilled the hard cases.
Work each problem before reading the solution.
Problem 1: Top-N Per Group (Without LIMIT)
Problem: Find the top 2 products by sales in each category. LIMIT alone can't do this.
SELECT category, name, total_sales
FROM (
SELECT
category,
name,
total_sales,
DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS dr
FROM products
) t
WHERE dr <= 2;
Why DENSE_RANK, not ROW_NUMBER? If two products tie for first place, ROW_NUMBER arbitrarily breaks the tie and hides one. DENSE_RANK returns both. Use ROW_NUMBER only when you need exactly N results and ties don't matter (e.g., pagination).
Problem 2: Deduplication — Keep the Latest Row Per User
Problem: The user_events table has duplicates for some users. Keep only the most recent row per user_id.
SELECT user_id, email, event_type, created_at
FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC
) AS rn
FROM user_events
) t
WHERE rn = 1;
The pattern: ROW_NUMBER + filter is the standard deduplication approach. It's deterministic (unlike DISTINCT) and works when you need to keep a specific row, not just any row.
Problem 3: Month-Over-Month Growth with NULL Safety
Problem: Compute month-over-month revenue change and growth percentage, handling the first month (where there's no prior month) cleanly.
WITH monthly AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY 1
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
2
) AS mom_growth_pct
FROM monthly
ORDER BY month;
The key: NULLIF(..., 0) prevents division by zero if a prior month had zero revenue. The first row returns NULL for prev_revenue — that's correct behavior, not a bug.
Problem 4: Running Total That Resets Per Group
Problem: Compute a cumulative sum of sales, but reset it at the start of each year.
SELECT
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY DATE_PART('year', sale_date)
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_sales
FROM sales
ORDER BY sale_date;
The key: PARTITION BY year resets the window for each year. Without it, the running total accumulates forever across years.
Problem 5: Sessionization — Group Events Into Sessions
Problem: A user's page view events should be grouped into "sessions" — a session ends when there's a gap of more than 30 minutes between events. Assign a session number per user.
WITH session_flags AS (
SELECT
user_id,
event_time,
CASE
WHEN event_time - LAG(event_time) OVER (
PARTITION BY user_id ORDER BY event_time
) > INTERVAL '30 minutes'
THEN 1
ELSE 0
END AS is_new_session
FROM page_views
),
session_numbers AS (
SELECT
user_id,
event_time,
SUM(is_new_session) OVER (
PARTITION BY user_id
ORDER BY event_time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_id
FROM session_flags
)
SELECT
user_id,
session_id,
MIN(event_time) AS session_start,
MAX(event_time) AS session_end,
COUNT(*) AS page_views,
MAX(event_time) - MIN(event_time) AS session_duration
FROM session_numbers
GROUP BY user_id, session_id
ORDER BY user_id, session_id;
How it works: LAG detects where a gap exceeds 30 minutes and marks it as 1. A cumulative SUM of those flags gives each session a unique incrementing ID per user.
Problem 6: Gaps and Islands — Find Consecutive Date Streaks
Problem: A login_dates table has one row per user per day they logged in. Find all consecutive streaks (no gaps) and report start date, end date, and streak length.
WITH date_groups AS (
SELECT
user_id,
login_date,
login_date - (ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
) * INTERVAL '1 day') AS grp
FROM login_dates
)
SELECT
user_id,
MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end,
COUNT(*) AS streak_length
FROM date_groups
GROUP BY user_id, grp
ORDER BY user_id, streak_start;
The trick: Subtract a sequential row number (as days) from the login date. Consecutive dates produce the same constant — that's the group key. Gaps produce different constants, splitting the groups.
Problem 7: Median and Percentiles Per Group
Problem: Find the median and interquartile range of order values per product category.
SELECT
category,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_total) AS median,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY order_total) AS p25,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY order_total) AS p75,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY order_total) -
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY order_total) AS iqr
FROM orders
JOIN products USING (product_id)
GROUP BY category;
PERCENTILE_CONT vs PERCENTILE_DISC: CONT interpolates between values (returns a value that may not exist in the data). DISC returns the nearest actual value from the dataset. For median salary, DISC is usually more intuitive.
Problem 8: Cohort Retention
Problem: For each monthly signup cohort, calculate what percentage of users were active in each subsequent month (month 0, 1, 2, ...).
WITH first_activity AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(created_at)) AS cohort_month
FROM users
GROUP BY user_id
),
monthly_activity AS (
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_time) AS activity_month
FROM events
),
cohort_data AS (
SELECT
fa.cohort_month,
ma.activity_month,
EXTRACT(EPOCH FROM (ma.activity_month - fa.cohort_month)) / 2592000 AS months_since_signup,
COUNT(DISTINCT fa.user_id) AS active_users
FROM first_activity fa
JOIN monthly_activity ma ON fa.user_id = ma.user_id
GROUP BY 1, 2, 3
)
SELECT
cohort_month,
months_since_signup::INT AS month_number,
active_users,
FIRST_VALUE(active_users) OVER (
PARTITION BY cohort_month ORDER BY months_since_signup
) AS cohort_size,
ROUND(
active_users::NUMERIC /
FIRST_VALUE(active_users) OVER (
PARTITION BY cohort_month ORDER BY months_since_signup
) * 100,
1
) AS retention_pct
FROM cohort_data
ORDER BY cohort_month, months_since_signup;
The key: FIRST_VALUE retrieves the cohort size (month 0) within each cohort partition, so you can divide subsequent months against it without a self-join.
Performance Tips
Use a Named WINDOW Clause
-- BAD: Same OVER() clause written three times
SELECT
customer_id,
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
AVG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_avg,
MAX(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_max
FROM orders;
-- GOOD: Define once, reference by name
SELECT
customer_id,
SUM(amount) OVER w AS running_total,
AVG(amount) OVER w AS running_avg,
MAX(amount) OVER w AS running_max
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);
Filter Before Windowing
-- Push filters into a CTE — window functions run AFTER WHERE, but early filtering reduces rows
WITH recent_orders AS (
SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
AND status = 'completed'
)
SELECT
customer_id,
order_date,
amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM recent_orders;
Index the PARTITION BY Column
-- This query benefits from a composite index on (customer_id, order_date)
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);
Common Pitfalls
LAST_VALUE Requires an Explicit Frame
-- WRONG: Default frame ends at current row, not partition end
SELECT
LAST_VALUE(sales) OVER (PARTITION BY product_id ORDER BY week) AS last_sale
FROM weekly_sales;
-- CORRECT: Extend frame to cover all rows in the partition
SELECT
LAST_VALUE(sales) OVER (
PARTITION BY product_id
ORDER BY week
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_sale
FROM weekly_sales;
Window Functions Can't Appear in WHERE
-- WRONG
SELECT * FROM orders
WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) = 1;
-- CORRECT: Filter in an outer query
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders
) t
WHERE rn = 1;
NULL Handling with LAG/LEAD
-- Provide a default to avoid NULL on the first row
SELECT
order_date,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY order_date) AS prev_revenue
FROM daily_sales;
New to window functions or need the full syntax reference? The SQL Window Functions Guide covers every function with 9 essential patterns explained from scratch.
Want all of this plus 140+ interview questions? The SQL Crash Course covers window functions, cohort analysis, and advanced analytics in 270+ pages.
Stuck on one of these problems or have a harder variant to share? Get in touch.
Enjoyed this article? Share it!
About the Author
Vajo Lukic
Vajo Lukic is a technology leader with 20+ years of experience in software development and system administration. Author of The Practical Linux Handbook, he shares practical, field-tested knowledge to help developers and IT professionals master Linux fundamentals.
Read more about VajoRelated Articles
10 Essential SQL Queries Every Developer Should Know (With Examples)
The 10 SQL query patterns every developer needs: SELECT, JOINs, GROUP BY, CTEs, window functions, and more. With real examples and performance tips.
Read more →
ChatGPT Can't Replace Your SQL Skills — Here's Why
ChatGPT generates SQL, but it doesn't know your schema, can't optimize for your indexes, and gets NULL handling wrong. Here's what AI still can't do with SQL.
Read more →
SQL Crash Course: From Zero to Interview-Ready in 30 Days
A structured 30-day SQL crash course for beginners and job seekers. Learn SQL from SELECT basics to window functions, CTEs, and interview prep — with practice.
Read more →Ready to Transform Your Life?
Get the complete guide to personal transformation and start your journey today.
Get the Book