Mastering SQL Window Functions: The Ultimate Guide for Data Science Interviews

In Data Science, Data Engineering, and Data Analyst technical interviews, standard SELECT, JOIN, and GROUP BY statements will only get you through the warm-up round. The true differentiator that proves your SQL fluency is your mastery of Window Functions.

Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, without collapsing those rows into a single output row (which is what GROUP BY does). In this guide, we will break down the most critical window functions you must know to pass FAANG-level SQL interviews.

1. The Anatomy of a Window Function

A window function is defined by the OVER() clause. It has three main components:

1. PARTITION BY: Divides the result set into partitions (similar to GROUP BY), but keeps all original rows intact.
2. ORDER BY: Defines the logical order of the rows within each partition.
3. ROWS/RANGE (The Frame Clause): Defines a specific moving subset of rows within the partition (crucial for rolling averages).

SELECT 
    employee_id,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
FROM employees;

In this example, every employee row is returned, but with an appended column showing the average salary of their specific department.

2. Ranking Functions: RANK vs. DENSE_RANK vs. ROW_NUMBER

Interviewers love to test your understanding of how different ranking functions handle ties (e.g., two employees having the exact same salary).

• ROW_NUMBER(): Assigns a unique, sequential integer to each row within the partition, regardless of ties. (1, 2, 3, 4).
• RANK(): Assigns the same rank to identical values, but skips the next logical rank. If two people tie for 1st place, the next person is 3rd. (1, 1, 3, 4).
• DENSE_RANK(): Assigns the same rank to identical values, but does not skip ranks. If two people tie for 1st place, the next person is 2nd. (1, 1, 2, 3).

Classic Interview Question: "Find the 3rd highest paid employee in each department." Answer: Use a CTE (Common Table Expression) with DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank, then filter WHERE rank = 3 in the outer query.

3. Navigational Functions: LEAD and LAG

When analyzing time-series data or calculating week-over-week growth, LEAD and LAG are mathematically necessary. They allow you to access data from a subsequent or previous row without using complex self-joins.

• LAG(column, offset): Retrieves a value from a previous row.
• LEAD(column, offset): Retrieves a value from a subsequent row.

-- Calculating Month-over-Month Revenue Growth
SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) as revenue_difference
FROM monthly_sales;

4. The Final Boss: Running Totals and Moving Averages

The most difficult SQL questions involve the Frame Clause (ROWS BETWEEN). If asked to calculate a "7-day rolling average" of user signups, you must bound your window.

SELECT 
    date,
    daily_signups,
    AVG(daily_signups) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as rolling_7_day_avg
FROM signups;

This forces the window function to only look at the current row and the 6 rows immediately preceding it, creating a flawless moving average.

Perfect Your SQL Queries on PracHub

Writing perfect syntax on an IDE is easy. Writing a complex Window Function with a nested Frame Clause on a whiteboard while a Data Engineering Manager watches your logic is incredibly stressful.

PracHub is the ultimate environment to sharpen your technical interview skills. Our platform pairs you with experienced data professionals for live, collaborative SQL coding sessions. Don't wait until the final interview round to realize you confused RANK with DENSE_RANK. Practice your advanced SQL on PracHub and secure your next Data Science role.