Advanced SQL Interview Questions

Write a Query Using Window Functions

Write a SQL query to calculate a running total of sales per month using window functions.

Expected output: A result set showing cumulative sales per month.

Hint: Use SUM() OVER with ORDER BY on the date column.

Create a Recursive CTE

Write a recursive CTE to list all subordinates under a given manager ID.

Expected output: A hierarchical list of employees reporting to the manager.

Hint: Use UNION ALL to combine anchor and recursive parts.

Analyze Query Execution Plan

Use EXPLAIN to analyze a slow query and identify if indexes are used.

Expected output: A report indicating whether the query uses indexes efficiently.

Hint: Look for 'Index Scan' or 'Seq Scan' in the plan.

ROW_NUMBER() assigns a unique sequential number to rows, RANK() assigns the same rank to ties but skips ranks after ties, and DENSE_RANK() assigns the same rank to ties without gaps in ranking.

Recursive CTEs repeatedly execute a query starting from an anchor member and union the results with recursive members, useful for hierarchical or graph data traversal.

Indexes speed up data retrieval by allowing the database to find rows faster but can slow down inserts, updates, and deletes due to maintenance overhead.

ACID stands for Atomicity, Consistency, Isolation, and Durability, which ensure reliable processing of database transactions.