Top 20 SQL Interview Questions on Window Functions 2024.

Window functions in SQL are powerful tools that allow you to perform calculations across rows of a result set while preserving the details of each individual row. Unlike aggregate functions, which summarize data into a single row, window functions enhance analytical capabilities by enabling tasks like ranking, running totals, and comparisons across rows. In this blog, we’ll explore the top interview questions on window functions, providing detailed answers to help you ace your SQL technical interviews.

1. What are window functions in SQL?
   Window functions allow you to perform calculations across rows that are related to the current row within the same query result set. Unlike aggregate functions, they do not collapse rows.
2. How is a window function different from an aggregate function?
   While aggregate functions group rows and return one value per group, window functions retain individual rows but add calculated values for each row.
3. What is the purpose of the OVER() clause?
   The OVER() clause defines the “window” (partition and ordering) over which the function operates. Without it, window functions won’t know how to group or sort rows.
4. Explain ROW_NUMBER() and provide an example.
   This function assigns a unique, sequential number to rows within a partition, starting from 1. It’s often used to remove duplicates. SELECT Name, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum FROM Employees;
5. What is RANK(), and how is it different from ROW_NUMBER()?
   RANK() assigns ranks but skips numbers after ties. For example, if two rows tie for 1st rank, the next row gets rank 3.
6. What does DENSE_RANK() do?
   Like RANK(), but it does not skip numbers after ties. So if two rows tie for 1st rank, the next row gets rank 2.
7. Can you use a window function without a PARTITION BY clause?
   Yes. Without PARTITION BY, the function calculates across all rows in the query result.
8. How do you calculate a running total with window functions?
   Use SUM() with OVER(ORDER BY) to calculate totals row-by-row sequentially. SELECT Name, SUM(Salary) OVER (ORDER BY ID) AS RunningTotal FROM Employees;
9. What is the difference between PARTITION BY and GROUP BY?
   GROUP BY reduces data to one row per group. PARTITION BY keeps all rows intact but treats partitions as subgroups for calculations.
10. What is the LAG() function?
    LAG() fetches the value of a column from a previous row. This is useful for comparing current and past rows.

11. What is the LEAD() function?
    LEAD() retrieves the value from a subsequent row, often used to compare current and next rows.
12. How do you find the Nth highest salary in a table?
    Use RANK() or DENSE_RANK() to order salaries and filter the desired rank. SELECT * FROM ( SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank_name FROM Employees ) WHERE Rank_name = 5;
13. Can you use multiple window functions in a single query?
    Yes. Multiple functions can be applied to different or the same windows within the same query.
14. What is the purpose of the NTILE() function?
    NTILE() divides rows into specified buckets (e.g., quartiles) and assigns a bucket number to each row.
15. What happens if there is no ORDER BY in the window definition?
    The window function may return results without a clear sequence, leading to inconsistent output.
16. What is the difference between FIRST_VALUE() and LAST_VALUE()?
    • FIRST_VALUE() returns the first value in the window.
    • LAST_VALUE() returns the last value. Useful for leader/summary reports.
17. Can window functions be used in the WHERE clause?
    No. Use subqueries or Common Table Expressions (CTEs) to filter rows by window function results.

18. Explain the FRAME clause in window functions.
    The ROWS or RANGE clause specifies a subset of rows within the partition for the window calculation.
19. What is the difference between ROWS and RANGE in window frames?
    • ROWS: Works on physical row positions.
    • RANGE: Works on a range of values, not positions, relative to the current row.
20. How do you optimize queries with window functions?
    • Index columns used in PARTITION BY and ORDER BY.
    • Use the smallest dataset possible by filtering early.

Let me know if you’d like more examples or SQL queries!