Sql Window Functions

SQL Window Functions Interview Questions and Answers (2026)

If you are preparing for a data analyst, data engineer, or data scientist interview in 2026, SQL window functions are non-negotiable. They consistently rank among the top five topics tested at companies like Google, Amazon, Meta, Microsoft, and leading startups. Unlike standard aggregate functions that collapse rows into a single result, window functions compute values across a sliding window of related rows while preserving every row in the output.

This guide covers the most important SQL window functions interview questions with clear explanations, real query examples, and the reasoning interviewers expect you to articulate. Before diving in, make sure you have also reviewed our complete list of Top 100 SQL Interview Questions and Answers for the full picture.

---

What Are SQL Window Functions? (The Definition Interviewers Expect)

A window function performs a calculation across a set of rows that are somehow related to the current row. The key word is window — you define a logical frame of rows using the OVER() clause. The critical difference from GROUP BY is that window functions do not reduce the number of rows returned. Each row retains its identity and gets an additional computed column.

The general syntax is:

function_name(column) OVER (
    PARTITION BY partition_column
    ORDER BY order_column
    ROWS/RANGE BETWEEN ... AND ...
)

Three clauses make up the window definition: PARTITION BY (divides rows into groups), ORDER BY (sorts rows within each partition), and the optional frame clause (defines which preceding and following rows to include).

---

Top SQL Window Functions Interview Questions with Answers

1. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

This is the single most common window function question in SQL interviews. All three assign numbers to rows but handle ties differently.

• ROW_NUMBER() — assigns a unique sequential integer to every row regardless of ties. Even two identical rows get different numbers. Result: 1, 2, 3, 4, 5.
• RANK() — assigns the same rank to tied rows but skips subsequent numbers. Result for ties at position 2: 1, 2, 2, 4, 5.
• DENSE_RANK() — assigns the same rank to tied rows but does NOT skip numbers. Result for ties at position 2: 1, 2, 2, 3, 4.

When to use each: Use ROW_NUMBER for pagination or deduplication. Use RANK for competition-style standings where ties matter. Use DENSE_RANK when you need a continuous ranking without gaps.

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

---

2. What does PARTITION BY do in a window function?

PARTITION BY divides the result set into logical groups so the window function calculation restarts independently for each group. It is conceptually similar to GROUP BY but does not collapse rows.

Example: Find each employee's rank within their own department by salary:

SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Without PARTITION BY, the rank would be computed globally across all departments. With PARTITION BY department, the ranking resets to 1 for each department.

---

3. How do LAG() and LEAD() work and when would you use them?

LAG(column, offset, default) accesses data from a row that is a specified number of rows before the current row. LEAD(column, offset, default) accesses data from a row that is a specified number of rows after the current row. Both eliminate the need for a self-join.

Classic use case — Calculate month-over-month revenue change:

SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS revenue_change
FROM monthly_sales;

The third argument (0) is the default value returned when there is no previous row (first row of the partition). Always provide a default to avoid unexpected NULLs.

---

4. What is a running total and how do you write one using window functions?

A running total (cumulative sum) is one of the most practical window function use cases. Use SUM() with an OVER clause that includes a frame specification:

SELECT
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "from the very first row up to and including the current row." This is the standard frame for running totals.

---

5. How would you find the top 3 employees by salary in each department?

This is a classic interview problem that tests whether you can combine DENSE_RANK with a subquery or CTE:

WITH ranked AS (
    SELECT
        employee_name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS dept_rank
    FROM employees
)
SELECT employee_name, department, salary
FROM ranked
WHERE dept_rank <= 3;

This approach is cleaner and more performant than correlated subqueries. The CTE computes the rank, and the outer query filters to the top 3 per department.

---

6. What is NTILE() and when would you use it?

NTILE(n) divides rows into n approximately equal buckets and assigns each row a bucket number from 1 to n. It is used for percentile analysis and segmentation.

SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customers;

Quartile 1 contains the top 25% of spenders, quartile 4 the bottom 25%. This is extremely common in marketing analytics and customer segmentation interviews.

---

7. What is the difference between FIRST_VALUE() and LAST_VALUE()?

FIRST_VALUE(col) returns the value of the specified column from the first row of the window frame. LAST_VALUE(col) returns it from the last row. The important gotcha: LAST_VALUE by default uses a frame that ends at the current row, not the end of the partition. You must explicitly extend the frame:

SELECT
    employee_name,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS highest_in_dept,
    LAST_VALUE(salary)  OVER (
        PARTITION BY dept ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_in_dept
FROM employees;

---

8. What is PERCENT_RANK() and how does it differ from CUME_DIST()?

PERCENT_RANK() calculates the relative rank as a percentage: (rank - 1) / (total_rows - 1). The first row always returns 0. CUME_DIST() calculates the fraction of rows with values less than or equal to the current row: rank / total_rows. CUME_DIST ranges from just above 0 to 1.0 and is always greater than or equal to PERCENT_RANK.

---

Window Functions vs GROUP BY — The Question That Trips People Up

Interviewers often ask you to explain the conceptual difference. The answer: GROUP BY collapses rows — you lose individual row identity and can only see aggregated results. Window functions preserve all rows — every original row stays in the output with an additional computed column alongside it. You can even use both in the same query: GROUP BY to aggregate first, then window functions to compute across those aggregated results.

---

5 Common Window Function Mistakes in Interviews

1. Forgetting the ORDER BY inside OVER() — functions like LAG, LEAD, RANK, and running totals require ORDER BY inside the OVER clause, not just in the outer query.
2. Confusing RANK and DENSE_RANK — always be ready to explain with a specific numeric example.
3. LAST_VALUE frame trap — forgetting that LAST_VALUE needs ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to return the true last value.
4. Using window functions in WHERE — you cannot filter on a window function result in a WHERE clause. You must wrap it in a subquery or CTE first.
5. Assuming PARTITION BY is mandatory — it is optional. Without it, the entire result set is one partition.

---

Practice Questions for Your Interview

Try solving these before your next interview:

1. Write a query to find employees whose salary is above the average salary of their own department (use window functions, not a subquery).
2. Given a table of daily stock prices, calculate the 7-day moving average for each stock.
3. Find all customers who made a purchase for three consecutive days.
4. Given an orders table, rank orders by value within each customer and return only each customer's top 2 orders.
5. Calculate the percentage contribution of each product's sales to its category total.

---

Frequently Asked Questions

Q: Are SQL window functions asked in data engineer interviews?

Yes — window functions are one of the most tested SQL topics in technical interviews for data engineer, data analyst, and data scientist roles at top technology companies.

Q: Which databases support window functions?

All major databases support window functions: PostgreSQL, MySQL (8.0+), SQL Server, Oracle, SQLite (3.25+), BigQuery, Snowflake, Redshift, Databricks SQL, and Spark SQL.

Q: Can you use window functions with GROUP BY in the same query?

Yes. Window functions are evaluated after GROUP BY, so you can apply them to the results of a grouped query. The window function sees each group's aggregated row as its input.

Q: What is the ROWS vs RANGE frame clause difference?

ROWS counts physical rows before/after the current row. RANGE counts logical rows based on value order — rows with the same ORDER BY value are treated as peers and included together. ROWS is more predictable for running totals.

Q: Is knowing window functions enough for a data analyst SQL interview?

Window functions are essential but not sufficient on their own. Review our full Top 100 SQL Interview Questions to cover joins, subqueries, indexing, CTEs, and query optimisation as well.

---

Conclusion

SQL window functions are a signal of advanced SQL proficiency. When you can fluently explain the difference between RANK and DENSE_RANK, write a running total with the correct frame clause, and solve a top-N-per-group problem using CTEs — you demonstrate the kind of analytical thinking that separates candidates at technical interviews. Practice each function with real data, understand the OVER clause deeply, and you will handle window function questions with confidence.

Ready for the full SQL interview preparation? Visit our complete guide: Top 100 SQL Interview Questions and Answers (2026).