Advanced SQL Interview Questions: Window Functions, CTEs, and Transactions

15 advanced SQL questions on window functions, CTEs, ACID, isolation levels, deadlocks, views, and stored procedures.

1. 1. What is a window function, and how is it different from GROUP BY?

   A window function computes a value across a set of rows related to the current row without collapsing them, using OVER with optional PARTITION BY and ORDER BY. GROUP BY returns one row per group, while a window function keeps every row and adds the calculated column.

   SELECT name, department, salary,
          AVG(salary) OVER (PARTITION BY department) AS dept_avg
   FROM employees;

2. 2. What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

   ROW_NUMBER gives each row a unique sequential number, even for ties. RANK gives tied rows the same number and then skips numbers (1, 2, 2, 4). DENSE_RANK gives tied rows the same number without gaps (1, 2, 2, 3).

3. 3. How do you get the top 3 salaries in each department?

   Rank within each department and filter on the rank in an outer query.

   SELECT * FROM (
     SELECT name, department, salary,
            DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
     FROM employees
   ) t
   WHERE rnk <= 3;

4. 4. How do you calculate a running total?

   Use SUM as a window function ordered by time.

   SELECT order_date, amount,
          SUM(amount) OVER (ORDER BY order_date
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
   FROM orders;

5. 5. What do LAG and LEAD do?

   They return a value from a previous (LAG) or following (LEAD) row in the window, which is useful for comparing a row with its neighbor, such as day-over-day change.

   SELECT day, revenue,
          revenue - LAG(revenue) OVER (ORDER BY day) AS change_from_yesterday
   FROM daily_revenue;

6. 6. What is a CTE, and what is a recursive CTE?

   A common table expression (WITH name AS (...)) names a subquery so a query is easier to read and reuse. A recursive CTE refers to itself and is used for hierarchies such as org charts or category trees.

   WITH RECURSIVE chain AS (
     SELECT id, name, manager_id FROM employees WHERE id = 1
     UNION ALL
     SELECT e.id, e.name, e.manager_id
     FROM employees e JOIN chain c ON e.manager_id = c.id
   )
   SELECT * FROM chain;

7. 7. What are the ACID properties?

   Atomicity: a transaction either fully happens or not at all. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not see each other's partial work. Durability: once committed, data survives crashes.

8. 8. What are transaction isolation levels and the problems they prevent?

   From weakest to strongest: READ UNCOMMITTED (allows dirty reads), READ COMMITTED (no dirty reads, but non-repeatable reads), REPEATABLE READ (rows read once stay the same; phantoms are possible in some databases), and SERIALIZABLE (transactions behave as if run one at a time). Higher isolation means fewer anomalies but more locking or retries. Defaults differ: PostgreSQL and SQL Server use READ COMMITTED, MySQL InnoDB uses REPEATABLE READ.

9. 9. What are dirty reads, non-repeatable reads, and phantom reads?

   A dirty read sees data from a transaction that has not committed. A non-repeatable read gets different values when the same row is read twice in one transaction because another transaction changed and committed it. A phantom read returns a different set of rows when the same range query runs twice because another transaction inserted or deleted matching rows.

10. 10. What is a deadlock in a database, and how do you handle it?

    Two transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle and aborts one transaction, which the application must retry. Reduce deadlocks by accessing tables and rows in a consistent order, keeping transactions short, and indexing the columns used in updates so fewer rows are locked.

11. 11. What is the difference between a view and a materialized view?

    A view is a saved query that runs each time it is used and stores no data. A materialized view stores the query result physically and must be refreshed, which makes reads fast at the cost of staleness and storage. MySQL has no built-in materialized views; PostgreSQL and Oracle do.

12. 12. What is the difference between a stored procedure and a function?

    A function returns a value and can be used inside queries (for example in SELECT or WHERE), with restrictions on side effects. A stored procedure is called explicitly, can return multiple result sets or output parameters, and can perform data changes and transaction control. Exact capabilities depend on the database.

13. 13. What are triggers, and what are their downsides?

    A trigger is code that runs automatically before or after an INSERT, UPDATE, or DELETE. They suit auditing and enforcing rules at the data layer. Downsides: hidden logic that surprises developers, extra cost on every write, difficulty in testing and debugging, and cascading trigger chains.

14. 14. How do you pivot rows into columns?

    SQL Server and Oracle have a PIVOT operator. In portable SQL, use conditional aggregation.

    SELECT product,
           SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS q1,
           SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS q2
    FROM sales
    GROUP BY product;

15. 15. How do you delete duplicate rows but keep one?

    Number the duplicates with ROW_NUMBER and delete all but the first. Syntax for deleting from a CTE differs by database; this version works in SQL Server and PostgreSQL with minor changes.

    WITH ranked AS (
      SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
      FROM users
    )
    DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

    In MySQL, wrap the subquery in a derived table or use a self join. Always test on a copy first.