10 SQL Window Functions That Separate Junior From Senior Developers
10 SQL Window Functions That Separate Junior From Senior Developers If you've ever stared at a SQL query wondering "there has to be a better way," window functions are usually the answer. They let you perform calculati
10 SQL Window Functions That Separate Junior From Senior Developers
If you've ever stared at a SQL query wondering "there has to be a better way," window functions are usually the answer. They let you perform calculations across a set of rows related to the current row — without collapsing them like GROUP BY does.
Here are 10 window functions I use weekly, with examples that show the difference between "it works" and "it's elegant."
1. ROW_NUMBER() — Ranking Without Ties
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;
Junior approach: A subquery with COUNT(*) for every row. Senior approach: One clean window function.
2. RANK() vs DENSE_RANK() — Know the Difference
-- RANK(): 1, 2, 2, 4 (skips after ties)
-- DENSE_RANK(): 1, 2, 2, 3 (no gaps)
SELECT
product_name,
sales,
RANK() OVER (ORDER BY sales DESC) as rank,
DENSE_RANK() OVER (ORDER BY sales DESC) as dense_rank
FROM products;
Use DENSE_RANK() when you need a continuous ranking (top 10 products). Use RANK() when position matters (Olympic medals).
3. LAG() — Compare With Previous Row
SELECT
date,
revenue,
LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue,
revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_change,
ROUND(
(revenue - LAG(revenue, 1) OVER (ORDER BY date)) * 100.0
/ NULLIF(LAG(revenue, 1) OVER (ORDER BY date), 0), 2
) as pct_change
FROM daily_sales;
This replaces a self-join that's 5x slower on large tables.
4. LEAD() — Peek at the Next Row
-- Find users whose subscription expires next month
SELECT
user_id,
plan_name,
renewal_date,
LEAD(plan_name) OVER (PARTITION BY user_id ORDER BY renewal_date) as next_plan
FROM subscriptions
WHERE renewal_date < DATE_ADD(CURDATE(), INTERVAL 2 MONTH);
5. SUM() OVER — Running Totals
SELECT
date,
daily_revenue,
SUM(daily_revenue) OVER (ORDER BY date) as cumulative_revenue,
SUM(daily_revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as rolling_7_day_avg
FROM daily_sales;
Running totals and moving averages without a single subquery.
6. AVG() OVER — Department Averages in One Query
SELECT
employee_name,
department,
salary,
ROUND(AVG(salary) OVER (PARTITION BY department), 2) as dept_avg,
ROUND(
salary * 100.0 / AVG(salary) OVER (PARTITION BY department), 1
) as pct_of_dept_avg
FROM employees;
Who's above or below their department average — instantly.
7. FIRST_VALUE() and LAST_VALUE() — Boundary Values
SELECT
date,
stock_price,
FIRST_VALUE(stock_price) OVER (ORDER BY date) as opening_price,
LAST_VALUE(stock_price) OVER (
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as closing_price
FROM stock_prices;
Gotcha: LAST_VALUE() without the ROWS BETWEEN frame returns the current row, not the actual last row. This trips up everyone the first time.
8. NTILE() — Bucket Data Into Percentiles
-- Divide customers into 4 quartiles by spending
SELECT
customer_name,
total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC) as spending_quartile
FROM customers;
Perfect for "top 25%" reports and cohort analysis.
9. PERCENT_RANK() — Exact Percentile Position
SELECT
student_name,
test_score,
ROUND(PERCENT_RANK() OVER (ORDER BY test_score DESC) * 100, 1) as percentile
FROM test_scores;
More precise than NTILE — gives you the exact position (0.0 to 1.0).
10. CUME_DIST() — Cumulative Distribution
-- What percentage of orders are at or below this amount?
SELECT
order_id,
order_total,
ROUND(CUME_DIST() OVER (ORDER BY order_total) * 100, 1) as pct_at_or_below
FROM orders;
Great for pricing analysis: "70% of our orders are under $50."
Bonus: The FRAME Clause
Every window function supports a frame:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- default for running totals
ROWS BETWEEN 6 PRECEDING AND 6 FOLLOWING -- centered moving average
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- entire partition
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW -- date-based window
Understanding frames is what separates "I know window functions" from "I'm an expert."
Why This Matters
Window functions can replace complex self-joins, correlated subqueries, and application-level processing. A query that takes 200 lines of Python post-processing often becomes a 10-line SQL query.
Want 100+ more SQL interview problems with detailed solutions? Check out my SQL Interview Guide — it covers JOINs, window functions, optimization, and real-world scenarios.
What's your favorite window function trick? Drop it in the comments!
Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.