SQL Window Functions Tutorial: Master Advanced Analytics for Technical Interviews

Window functions are the Swiss Army knife of SQL analytics, yet they trip up countless engineers in technical interviews. I've seen brilliant developers fumble questions about calculating running totals or finding the top N records per group—problems that become trivial once you understand window functions.

Unlike regular aggregate functions that collapse rows into a single result, window functions perform calculations across a set of rows related to the current row while preserving individual row details. Think of them as a way to "peek" at other rows without losing your current context.

Understanding the Basic Window Function Syntax

Every window function follows this pattern:

function_name() OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [ROWS or RANGE frame_specification]
)

The OVER clause is what makes it a window function. Inside, you define:

• PARTITION BY: Divides rows into groups (like GROUP BY, but doesn't collapse rows)


• ORDER BY: Defines the sequence within each partition


• Frame specification: Defines which rows to include in the calculation (optional)

Let's say you have a sales table and want to add each salesperson's total sales alongside individual transactions:

SELECT 
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY salesperson) as total_sales_by_person
FROM sales;

This gives you each transaction plus the salesperson's total—something impossible with a regular GROUP BY.

Ranking Functions: ROW_NUMBER, RANK, and DENSE_RANK

Ranking functions are window function superstars, especially in interviews. Here's how they differ:

• ROW_NUMBER(): Assigns unique sequential integers (1, 2, 3, 4...)
• RANK(): Gives same rank for ties, skips next numbers (1, 2, 2, 4...)
• DENSE_RANK(): Gives same rank for ties, doesn't skip (1, 2, 2, 3...)

A classic interview question: "Find the second highest salary in each department."

WITH ranked_salaries AS (
    SELECT 
        employee_id,
        department,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
    FROM employees
)
SELECT employee_id, department, salary
FROM ranked_salaries
WHERE salary_rank = 2;

Why DENSE_RANK here? If multiple people have the highest salary, we still want the second-highest amount, not the person in the third position.

ROW_NUMBER is perfect when you need to eliminate duplicates or get exactly N records per group:

-- Get the most recent order for each customer
SELECT customer_id, order_date, amount
FROM (
    SELECT 
        customer_id, 
        order_date, 
        amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
    FROM orders
) ranked
WHERE rn = 1;

Aggregate Window Functions for Running Totals and Moving Averages

Regular aggregate functions (SUM, AVG, COUNT) become powerful analytical tools when used as window functions. The key is understanding frame specifications.

By default, the frame is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"—basically from the start of the partition to the current row. This creates running totals:

SELECT 
    order_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY order_date) as running_total
FROM daily_sales_summary
ORDER BY order_date;

For moving averages, you need to specify a custom frame:

SELECT 
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_moving_avg
FROM daily_sales_summary;

The difference between ROWS and RANGE is subtle but important:

• ROWS: Counts physical rows (6 PRECEDING = exactly 6 rows before)


• RANGE: Based on values in ORDER BY column (useful for dates/times)

Advanced Window Function Patterns for Complex Analytics

Once you master the basics, window functions unlock sophisticated analyses. The LAG and LEAD functions let you access previous or next row values:

SELECT 
    employee_id,
    salary,
    LAG(salary) OVER (ORDER BY salary) as previous_salary,
    salary - LAG(salary) OVER (ORDER BY salary) as salary_gap
FROM employees
ORDER BY salary;

For percentage of total calculations, combine window functions with regular aggregates:

SELECT 
    department,
    SUM(salary) as dept_total,
    SUM(salary) / SUM(SUM(salary)) OVER () * 100 as pct_of_company_payroll
FROM employees
GROUP BY department;

Here's a mind-bender: finding gaps in sequences. Say you want to find missing order IDs:

WITH gaps AS (
    SELECT 
        order_id,
        LAG(order_id) OVER (ORDER BY order_id) as prev_id
    FROM orders
)
SELECT prev_id + 1 as missing_start, order_id - 1 as missing_end
FROM gaps
WHERE order_id - prev_id > 1;

Common Window Function Interview Questions and Solutions

Interviewers love window functions because they test both SQL knowledge and analytical thinking. Here are patterns you'll see repeatedly:

Pattern 1: Top N per group
"Find the top 3 products by sales in each category"

WITH ranked_products AS (
    SELECT 
        category,
        product_name,
        total_sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC) as rn
    FROM product_sales
)
SELECT category, product_name, total_sales
FROM ranked_products
WHERE rn <= 3;

Pattern 2: Comparison with previous period
"Calculate month-over-month growth rate"

SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
    (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100 as growth_rate
FROM monthly_revenue;

Pattern 3: Cumulative and moving calculations
"Show running total and 3-month moving average"

The key insight: window functions eliminate the need for complex self-joins or correlated subqueries. When you see a problem asking for "running totals," "top N per group," or "compare with previous," think window functions.

Remember that window functions execute after WHERE but before ORDER BY in the logical query processing order. If you need to filter on window function results, use a CTE or subquery.

Window functions transform you from someone who can query data to someone who can analyze it. They're not just a nice-to-have for interviews—they're essential for any role involving data analysis, reporting, or analytics engineering.

Practice this on Goliath Prep — AI-graded mock interviews with instant feedback. Try it free at app.goliathprep.com