Advanced SQL Interview Questions India 2026: Window Functions, CTEs, Query Optimisation, and Indexes

SQL window functions:

1. What are window functions? Window functions perform calculations across a set of rows related to the current row (the 'window'), without collapsing the rows into a group like GROUP BY does. Syntax: function_name() OVER (PARTITION BY ... ORDER BY ...)

2. ROWNUMBER, RANK, DENSERANK: ```sql SELECT employeeid, salary, departmentid, ROWNUMBER() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS rownum, RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS ranknum, DENSERANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS denseranknum FROM employees; ``` - ROWNUMBER(): unique sequential integer; no ties - RANK(): ties get the same rank; the next rank skips (1, 1, 3, 4) - DENSE_RANK(): ties get the same rank; the next rank does not skip (1, 1, 2, 3)

Classic interview question: 'Find the Nth highest salary in each department.' Use ROWNUMBER() OVER (PARTITION BY departmentid ORDER BY salary DESC) and filter WHERE row_num = N.

3. LAG and LEAD: ```sql SELECT orderdate, revenue, LAG(revenue, 1) OVER (ORDER BY orderdate) AS prevdayrevenue, LEAD(revenue, 1) OVER (ORDER BY orderdate) AS nextdayrevenue, revenue - LAG(revenue, 1) OVER (ORDER BY orderdate) AS dayoverdaychange FROM dailyrevenue; ``` - LAG(column, n): value of column n rows before the current row - LEAD(column, n): value of column n rows after the current row

Common use: month-over-month or week-over-week comparison without a self-join.

4. Aggregate window functions: SUM(), AVG(), MIN(), MAX() can be used as window functions: ```sql SELECT orderdate, revenue, SUM(revenue) OVER (ORDER BY orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotal, AVG(revenue) OVER (ORDER BY orderdate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling7dayavg FROM dailyrevenue; ```

SQL CTEs and subqueries:

1. CTEs (Common Table Expressions): CTEs use the WITH clause to define named intermediate result sets that can be referenced in the main query. They make complex queries more readable by breaking them into named steps: ```sql WITH monthlyrevenue AS ( SELECT DATETRUNC('month', orderdate) AS month, SUM(revenue) AS totalrevenue FROM orders GROUP BY 1 ), revenuewithprev AS ( SELECT month, totalrevenue, LAG(totalrevenue) OVER (ORDER BY month) AS prevmonthrevenue FROM monthlyrevenue ) SELECT month, totalrevenue, ROUND((totalrevenue - prevmonthrevenue) / prevmonthrevenue * 100, 2) AS momgrowthpct FROM revenuewith_prev ORDER BY month; ```

2. Recursive CTEs: Recursive CTEs allow a CTE to reference itself, enabling traversal of hierarchical data: ```sql WITH RECURSIVE employeehierarchy AS ( -- Anchor: start with the CEO (no manager) SELECT id, name, managerid, 0 AS level FROM employees WHERE manager_id IS NULL

UNION ALL

-- Recursive: join each employee to their manager SELECT e.id, e.name, e.managerid, h.level + 1 FROM employees e INNER JOIN employeehierarchy h ON e.managerid = h.id ) SELECT * FROM employeehierarchy ORDER BY level, name; ```

3. Correlated subqueries: A subquery that references the outer query's columns. Executes once per row of the outer query (slow for large datasets; prefer window functions or CTEs): ```sql SELECT name, salary FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.departmentid = e1.departmentid); ```

SQL query optimisation:

1. EXPLAIN and EXPLAIN ANALYZE: EXPLAIN shows the query execution plan (how the database will execute the query) without running it. EXPLAIN ANALYZE runs the query and shows both the plan and actual timings.

Key things to look for in the execution plan:

• Sequential scan (Seq Scan): the database reads every row in the table. For large tables, this is slow. If there is a WHERE clause that should use an index, you have a missing index.
• Index scan: the database uses an index to find rows. Fast for selective queries.
• Nested loop join: good for small tables; bad for large tables.
• Hash join: efficient for larger tables; builds a hash table from one table and probes it with the other.
• Merge join: efficient when both tables are sorted on the join key.

2. Index strategies: - B-tree index: the default PostgreSQL/MySQL index type; supports equality and range queries (<, >, BETWEEN, LIKE 'prefix%'). - Composite (multi-column) index: index on multiple columns. The column order matters: the query must use the leading column(s) to benefit from the index. Index on (departmentid, salary) helps queries filtering on departmentid or both departmentid and salary; it does NOT help queries filtering only on salary. - Partial index: an index on a subset of rows. CREATE INDEX idxactiveusers ON users(email) WHERE deletedat IS NULL: only indexes active users; smaller and faster for queries that always include WHERE deletedat IS NULL. - Index on expression: CREATE INDEX idxlower_email ON users(LOWER(email)): supports case-insensitive email lookups.

3. Common query performance issues: - Missing index on the WHERE clause column: add an index - Selecting * when you only need 2 columns: use SELECT col1, col2 instead - N+1 query pattern (in ORMs): use JOIN or EXISTS instead of a loop - Large OFFSET for pagination: use keyset pagination (WHERE id > lastid ORDER BY id LIMIT 20) instead of OFFSET 10000 - Function on indexed column in WHERE clause: WHERE YEAR(createdat) = 2024 cannot use the index; rewrite as WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

SQL interview problems and solutions:

1. Find duplicate rows: ```sql SELECT email, COUNT() as count FROM users GROUP BY email HAVING COUNT() > 1; ``` Delete duplicates keeping one: ```sql DELETE FROM users WHERE id NOT IN ( SELECT MIN(id) FROM users GROUP BY email ); ```

2. Find users who did not make a purchase in the last 30 days: ```sql SELECT u.id, u.email FROM users u LEFT JOIN orders o ON u.id = o.userid AND o.createdat >= CURRENTDATE - INTERVAL '30 days' WHERE o.userid IS NULL; ```

3. Running total and percentage of total: ```sql SELECT category, revenue, SUM(revenue) OVER (ORDER BY revenue DESC) AS runningtotal, ROUND(revenue::numeric / SUM(revenue) OVER () * 100, 2) AS pctoftotal FROM categoryrevenue ORDER BY revenue DESC; ```

4. Median salary by department: ```sql SELECT departmentid, PERCENTILECONT(0.5) WITHIN GROUP (ORDER BY salary) AS mediansalary FROM employees GROUP BY departmentid; ```

5. Sessions: find user sessions where each new session starts 30+ minutes after the last event: ```sql WITH eventswithlag AS ( SELECT userid, eventtime, LAG(eventtime) OVER (PARTITION BY userid ORDER BY eventtime) AS prevevent FROM userevents ), sessionstarts AS ( SELECT *, SUM(CASE WHEN prevevent IS NULL OR eventtime - prevevent > INTERVAL '30 minutes' THEN 1 ELSE 0 END) OVER (PARTITION BY userid ORDER BY eventtime) AS sessionid FROM eventswithlag ) SELECT userid, sessionid, MIN(eventtime) AS sessionstart, MAX(eventtime) AS sessionend FROM sessionstarts GROUP BY userid, sessionid ORDER BY userid, session_id; ```