SQL Interview Questions

Window functions perform a calculation across a set of rows related to the current row, without collapsing them into a single output row (unlike GROUP BY).

Syntax: function() OVER (PARTITION BY col ORDER BY col)

Common Functions: 1. ROW_NUMBER(): Assigns a unique sequential integer per partition, starting at 1. 2. RANK(): Same rank for ties, but leaves gaps. 3. DENSE_RANK(): Same rank for ties, no gaps. 4. LAG() / LEAD(): Access data from the previous/next row within the partition. 5. SUM() / AVG() OVER(...): Running totals or moving averages.

Example: Find the top 3 highest-paid employees per department.

WITH ranked AS (
    SELECT name, salary, department,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, salary, department
FROM ranked
WHERE rn <= 3;

Key difference from GROUP BY: Window functions retain the original row granularity while adding aggregated data alongside.

Both filter rows, but they operate at different stages of query execution:

WHERE: - Filters rows before grouping (pre-aggregation filter). - Cannot be used with aggregate functions (COUNT, SUM, AVG, etc.). - Example: SELECT * FROM employees WHERE salary > 50000;

HAVING: - Filters groups after aggregation. - Used with the GROUP BY clause. - Can use aggregate functions in its condition. - Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 70000;

Order of execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Using WHERE on aggregate columns produces an error. Always filter individual rows with WHERE and aggregated groups with HAVING.