SQL Window Functions Interview Questions and Answers

7 min readMar 21, 2025

1. What is a window function in SQL and how is it different from aggregate functions?

Answer:
A window function performs a calculation across a set of table rows that are related to the current row. Unlike aggregate functions which collapse rows into a single result (like SUM, AVG with GROUP BY), window functions preserve all original rows and simply add new computed columns.

✅ Use Case:
Add a column showing the total salary of all employees while keeping individual salaries in each row.

SELECT employee_id, salary, SUM(salary) OVER () AS total_salary
FROM employees;

Key Point: One row per employee is retained.

Analogy:

Aggregate = making a smoothie (all fruits blended into one drink)
Window function = buffet (each dish stays separate, but you still know the total)

2. What does the OVER() clause do in a window function?

Answer:
The OVER() clause defines the window frame, i.e., which rows are considered for the calculation. You can use:

PARTITION BY → Splits data into groups (like GROUP BY, but doesn’t collapse rows).
ORDER BY → Specifies the order in which to process rows within each partition.

SELECT employee_id, department_id, salary, 
       AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary
FROM employees;

🎯 Analogy: Like placing a spotlight on one group at a time — each group has its own focus.

3. How are window functions different from GROUP BY?

Answer:

📌 Example Comparison:

GROUP BY:

SELECT department_id, AVG(salary) 
FROM employees 
GROUP BY department_id;

Window Function:

SELECT employee_id, department_id, 
       AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

🎯 Analogy:

GROUP BY = team photo — one for each group
Window = passport photos with team stats in background

4. What happens if you omit PARTITION BY in the OVER() clause?

Answer:
The function treats the entire dataset as a single partition, applying the calculation to all rows.

SELECT employee_id, salary, SUM(salary) OVER () AS total_salary
FROM employees;

🎯 Analogy:
Like throwing a company-wide party — no teams or divisions.

5. Can window functions be used in a WHERE clause?

Answer:
No — window functions are computed after the WHERE clause in SQL's execution order.
If you need to filter based on a window function result, use a subquery or CTE (Common Table Expression).

WITH ranked_employees AS (
  SELECT employee_id, salary, 
         RANK() OVER (ORDER BY salary DESC) AS rank
  FROM employees
)
SELECT * FROM ranked_employees WHERE rank = 1;

Analogy:
You can’t eat dessert before the main course — SQL processes WHERE before calculating window functions.

📊 Section 2: Practical Applications

6. How to calculate a running total of sales by product category?

SELECT product_id, category, sales, 
       SUM(sales) OVER (PARTITION BY category ORDER BY product_id) AS running_total
FROM sales;

🎯 Analogy: Like tallying up your buffet plate as you go along.

7. How to rank employees by salary within each department?

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Ranks are reset for each department and may skip numbers in case of ties.

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

ROW_NUMBER()

Assigns a unique sequential number to each row within the result set, based on the specified ORDER BY.
It does not handle ties — even if two rows have the same value, they get different numbers.

RANK()

Assigns the same rank to rows with equal values (i.e., handles ties), but skips subsequent ranks.
For example, if two rows are tied for rank 1, the next row gets rank 3 (not 2).

DENSE_RANK()

Also assigns the same rank to tied rows, but does not skip ranks.
If two rows are tied for rank 1, the next row gets rank 2.

SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

9. How to get the top 3 earners per department?

ITH ranked_employees AS (
  SELECT employee_id, department_id, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
  FROM employees
)
SELECT * FROM ranked_employees WHERE rank <= 3;

Use this structure for “Top-N per group” scenarios.

10. How to calculate a 7-day moving average of sales?

SELECT sale_date, sales,
       AVG(sales) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM daily_sales;

🎯 Analogy: Like tracking your weekly calorie intake day by day.

🔁 Section 3: Key Window Functions

11. What is the difference between LAG() and LEAD()?

Answer:
These functions let you access values from previous or following rows without using a self-join.

SELECT employee_id, salary,
       LAG(salary) OVER (ORDER BY employee_id) AS prev_salary,
       LEAD(salary) OVER (ORDER BY employee_id) AS next_salary
FROM employees;

📌 LAG(): Retrieves data from a previous row
📌 LEAD(): Retrieves data from a next row

🎯 Analogy: Like peeking at your neighbor’s test scores — one behind you (LAG), one ahead (LEAD).

12. How to calculate sales difference between consecutive days?

SELECT product_id, sale_date, sales,
       sales - LAG(sales) OVER (PARTITION BY product_id ORDER BY sale_date) AS sales_diff
FROM daily_sales;

🎯 Use Case: Track day-over-day changes in product sales.

13. What do FIRST_VALUE() and LAST_VALUE() do?

Answer:
They return the first or last value in a window frame based on specified order.

SELECT employee_id, department_id, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_salary,
       LAST_VALUE(salary) OVER (
           PARTITION BY department_id 
           ORDER BY salary DESC 
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS lowest_salary
FROM employees;

🔎 Important: Always define the frame for LAST_VALUE() to get meaningful results.

14. When should you use NTILE()?

Answer:
Use NTILE(n) when you want to divide data into "n" equally distributed groups (buckets).

SELECT employee_id, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

📌 Creates quartiles, deciles, percentiles, etc.

🎯 Analogy: Sorting runners into top 25%, middle 50%, bottom 25% groups.

15. What’s the difference between PERCENT_RANK() and CUME_DIST()?

SELECT employee_id, salary,
       PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank,
       CUME_DIST() OVER (ORDER BY salary) AS cumulative_dist
FROM employees;

🎯 Use Case: Normalize rankings or determine a cutoff percentile.

Section 4: Partitioning & Ordering

16. What does PARTITION BY do?

Answer:
It breaks the dataset into subsets — like virtual “groups” — over which the window function operates.

SELECT employee_id, department_id, 
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total_salary
FROM employees;

Analogy: Like dividing students into classes before grading them.

17. How to calculate cumulative revenue by region and month?

SELECT region, month, revenue,
       SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS cumulative_revenue
FROM sales;

🎯 Use Case: Running totals by geography and time.

18. What is the role of ORDER BY in window functions?

Answer:
ORDER BY determines the sequence in which calculations are done inside each partition.

Crucial for:

RANK(), ROW_NUMBER(), LAG()
Moving averages
Running totals

SELECT sale_date, sales,
       SUM(sales) OVER (ORDER BY sale_date) AS cumulative_sales
FROM daily_sales;

🎯 Analogy: Like organizing your calendar — without a timeline, nothing makes sense.

19. Can you use multiple columns in PARTITION BY?

Answer:
Yes, to create multi-level groupings.

SELECT employee_id, department_id, job_id, salary,
       AVG(salary) OVER (PARTITION BY department_id, job_id) AS avg_salary
FROM employees;

🎯 Use Case: Avg salary by job within each department.

20. How to calculate sales difference per region and date?

SELECT region, sale_date, sales,
       sales - LAG(sales) OVER (PARTITION BY region ORDER BY sale_date) AS sales_diff
FROM daily_sales;

🎯 Use Case: Trend analysis per region.

Advanced Concepts

21. What’s the difference between ROWS and RANGE in window frames?

SELECT sale_date, sales,
       AVG(sales) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_rows,
       AVG(sales) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '2 day' PRECEDING AND CURRENT ROW) AS avg_range
FROM daily_sales;

ROWS: "Take last 3 receipts"
RANGE: "Take all receipts from the past 3 days"

22. How to calculate a 7-day rolling average per store?

SELECT store_id, sale_date, sales,
       AVG(sales) OVER (
         PARTITION BY store_id 
         ORDER BY sale_date 
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_avg
FROM daily_sales;

🎯 Use Case: Store-level time series smoothing.

23. Does DISTINCT affect window function results?

Answer:
No — DISTINCT only affects the final output rows, not the rows considered by the window function.

If you want to calculate over unique values, use a subquery to de-duplicate first.

24. How to rank products within categories by total sales?

SELECT CategoryID, ProductID, 
       SUM(SalesAmount) AS TotalSales,
       RANK() OVER (PARTITION BY CategoryID ORDER BY SUM(SalesAmount) DESC) AS ProductRank
FROM Sales
GROUP BY CategoryID, ProductID
ORDER BY CategoryID, ProductRank;

🎯 Use Case: Product leaderboard within categories.

Section 6: Optimization & Debugging

25. What are best practices for optimizing window functions?

WITH Filtered AS (
  SELECT * FROM Sales WHERE SaleDate > '2023-01-01'
)
SELECT product_id, 
       ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY SaleAmount DESC) AS rank
FROM Filtered;

Section 7: Combining Joins & Multiple Window Functions

26. How to use window functions with JOINs?

SELECT e.employee_id, d.department_name, e.salary,
       RANK() OVER (PARTITION BY d.department_name ORDER BY e.salary DESC) AS rank
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

27. Can you combine multiple window functions in one query?

SELECT employee_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

28. How to find the second-highest earner in each department?

WITH ranked_employees AS (
  SELECT employee_id, department_id, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
  FROM employees
)
SELECT * FROM ranked_employees WHERE rank = 2;