SQL Coding Interview Problems: Senior Preparation Guide

SQL Coding Interview Problems: Senior Preparation Guide

SQL remains one of the most heavily tested skills in data engineering, analytics, and backend developer interviews. For senior roles, interviewers expect more than basic SELECT statements — they want to see mastery of window functions, complex joins, query optimization, and the ability to reason about edge cases. This guide walks you through the essential problem categories, practical examples, and strategies to ace a senior-level SQL interview.

What It Is

A SQL coding interview is a structured assessment where candidates solve data manipulation problems using SQL, typically on a whiteboard, shared editor, or a platform like LeetCode, HackerRank, or CoderPad. Senior-level problems go beyond simple retrieval and aggregation. They test your ability to:

• Model business logic with multiple tables and relationships
• Use advanced SQL features such as window functions, CTEs, and recursive queries
• Handle duplicates, NULLs, ties, and ranking edge cases
• Optimize queries for performance on large datasets
• Communicate assumptions and trade-offs clearly

Why It Matters

At the senior level, SQL proficiency signals more than just syntax knowledge. It demonstrates that you can think relationally, decompose ambiguous business questions into precise queries, and write code that is both correct and maintainable. Poor SQL skills often lead to slow dashboards, incorrect metrics, and expensive cloud warehouse bills. Interviewers use these problems to gauge whether you can be trusted with production data pipelines and decision-critical analytics.

Additionally, many senior engineers are expected to mentor juniors and review pull requests. Writing clean, readable SQL — with consistent aliasing, indentation, and comments — is part of that responsibility.

How to Use This Guide

Work through each problem category below in order. For every problem, attempt the solution yourself before reading the answer. Pay attention to the reasoning, not just the final query. In a real interview, the explanation matters as much as the code.

Problem Category 1: Aggregation and Grouping

Aggregation problems test whether you can summarize data correctly and understand the difference between filtering before and after grouping.

Problem: Second Highest Salary per Department

Given an employees table with columns id, name, salary, and department_id, find the employee with the second highest salary in each department.

WITH ranked AS (
    SELECT
        id,
        name,
        salary,
        department_id,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
)
SELECT department_id, id, name, salary
FROM ranked
WHERE rnk = 2;

Key points to discuss with your interviewer:

• Use DENSE_RANK() instead of RANK() or ROW_NUMBER() so that tied salaries do not skip the second rank.
• If a department has fewer than two distinct salaries, the query simply returns no rows for it — confirm whether that is the desired behavior.
• Wrap the window function in a CTE because window functions cannot appear directly in a WHERE clause.

Problem Category 2: Window Functions

Window functions are the single most important topic for senior SQL interviews. They allow you to compute values across rows related to the current row without collapsing the result set.

Problem: Running Total and Month-over-Month Growth

Given a sales table with sale_date and amount, compute the monthly total, a running cumulative total, and the percentage growth compared to the previous month.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS total
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date)
),
with_metrics AS (
    SELECT
        month,
        total,
        SUM(total) OVER (ORDER BY month) AS cumulative,
        LAG(total) OVER (ORDER BY month) AS prev_total
    FROM monthly
)
SELECT
    month,
    total,
    cumulative,
    ROUND(
        (total - prev_total) * 100.0 / NULLIF(prev_total, 0),
        2
    ) AS mom_growth_pct
FROM with_metrics
ORDER BY month;

Notes:

• NULLIF prevents a divide-by-zero error when the previous month had zero sales.
• SUM(total) OVER (ORDER BY month) without a frame clause produces a running total from the first row to the current row.
• Always clarify with the interviewer whether growth should be displayed as NULL or 0 for the first month.

Problem Category 3: Self Joins and Gaps

Self joins are common when comparing rows within the same table, such as consecutive events or finding gaps in sequences.

Problem: Consecutive Logins

Given a logins table with user_id and login_date, find users who logged in for at least three consecutive days.

WITH distinct_logins AS (
    SELECT DISTINCT user_id, login_date
    FROM logins
),
grouped AS (
    SELECT
        user_id,
        login_date,
        DATE_SUB(
            login_date,
            INTERVAL ROW_NUMBER() OVER (
                PARTITION BY user_id
                ORDER BY login_date
            ) DAY
        ) AS grp
    FROM distinct_logins
)
SELECT user_id
FROM grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id;

The trick here is the classic "islands and gaps" technique. By subtracting an incrementing row number from the date, consecutive dates map to the same group identifier. Any group with three or more rows represents a streak of at least three consecutive days.

Problem Category 4: Cumulative and Top-N per Group

Top-N-per-group problems are a staple of senior interviews because they combine partitioning, ordering, and filtering.

Problem: Top 3 Products by Revenue per Category

Given a products table (product_id, category, name) and an orders table (order_id, product_id, quantity, price), return the top three products by total revenue within each category.

WITH product_revenue AS (
    SELECT
        p.category,
        p.product_id,
        p.name,
        SUM(o.quantity * o.price) AS revenue
    FROM products p
    JOIN orders o ON o.product_id = p.product_id
    GROUP BY p.category, p.product_id, p.name
),
ranked AS (
    SELECT
        category,
        product_id,
        name,
        revenue,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY revenue DESC
        ) AS rnk
    FROM product_revenue
)
SELECT category, product_id, name, revenue
FROM ranked
WHERE rnk <= 3
ORDER BY category, rnk;

Discuss with your interviewer whether ties at the boundary should be included. If yes, switch from ROW_NUMBER() to DENSE_RANK() and adjust the filter accordingly.

Problem Category 5: Query Optimization

Senior candidates are often asked to take a slow query and improve it. Interviewers want to hear about indexes, partition pruning, avoiding correlated subqueries, and reducing data scanned.

Problem: Rewrite a Correlated Subquery

Consider this query that finds employees earning more than the average salary in their department:

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

This correlated subquery runs once per row, which is inefficient. A window function rewrite computes the average once per department:

WITH with_avg AS (
    SELECT
        name,
        salary,
        department_id,
        AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
)
SELECT name, salary, department_id
FROM with_avg
WHERE salary > dept_avg;

Alternatively, a grouped join approach can be more readable and sometimes faster depending on the database optimizer:

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN dept_avg d ON d.department_id = e.department_id
WHERE e.salary > d.avg_salary;

Best Practices

• Clarify before coding. Ask about NULL handling, duplicate rows, tie-breaking rules, and expected output columns. Ambiguity is intentional in many problems.
• Use CTEs for readability. Break complex queries into named steps. This mirrors how you would explain your thought process.
• Prefer window functions over self joins. They are usually clearer and often faster because they avoid repeated scans.
• Be explicit about JOIN types. Use LEFT JOIN when you need to preserve unmatched rows; never rely on implicit behavior.
• Handle edge cases. Think about empty tables, single-row partitions, ties, and division by zero. Use NULLIF, COALESCE, and CASE where appropriate.
• Format consistently. Uppercase keywords, lowercase identifiers, one column per line, and meaningful aliases make your query easier to review.
• Think about performance. Mention indexes on join and filter columns, the cost of sorting for window functions, and the impact of SELECT * in a columnar warehouse.
• Test mentally with sample data. Walk through your query with two or three rows to verify correctness before declaring it done.

Conclusion

Senior SQL interviews reward clear thinking, careful handling of edge cases, and an understanding of both correctness and performance. Master the core patterns — aggregation, window functions, islands and gaps, top-N per group, and query rewrites — and practice explaining your reasoning out loud. The goal is not just to produce a query that runs, but to demonstrate the judgment and communication skills expected of a senior engineer working with production data.