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!