← Back to DevBytes
Developer Tutorial August 04, 2026 7 min read

SQL Coding Interview Problems: Mid-Level Preparation Guide

SQL Coding Interview Problems: Mid-Level Preparation Guide

SQL remains one of the most tested skills in data engineering, analytics, and backend developer interviews. At the mid-level, interviewers expect you to go beyond basic SELECT statements and demonstrate fluency with joins, window functions, aggregations, subqueries, and query optimization. This guide walks you through what to expect, why these problems matter, how to approach them, and the best practices that separate strong candidates from average ones.

What Is a Mid-Level SQL Interview?

A mid-level SQL interview typically consists of 2 to 4 coding problems that you solve in a shared editor or whiteboard within 45 to 60 minutes. Unlike entry-level questions that test syntax recall, mid-level problems assess your ability to model data, reason about edge cases, and write efficient, readable queries against realistic schemas.

Common themes include:

Why Mid-Level SQL Matters

SQL is the lingua franca of data. Whether you are building reporting pipelines, debugging production data issues, or writing application queries, the ability to translate a business question into a correct and efficient query is essential. Interviewers use SQL problems to evaluate several traits at once:

Failing to clarify requirements or ignoring edge cases is the most common reason mid-level candidates get rejected, even when their queries produce the right output on the happy path.

How to Approach SQL Interview Problems

Develop a repeatable framework so you do not freeze under pressure. The following five-step method works for almost every problem:

Essential Patterns to Master

1. Aggregations with Conditional Logic

Many mid-level problems ask you to compute metrics broken down by category, often with conditional aggregation using CASE inside SUM or COUNT.

Problem: Given an orders table with columns order_id, customer_id, status, and amount, return each customer's total amount and the count of completed orders.

SELECT
    customer_id,
    SUM(amount) AS total_amount,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC;

This pattern avoids multiple passes over the table and is far more efficient than writing separate queries and joining them.

2. Window Functions for Ranking

Window functions are the single most important topic for mid-level interviews. They let you compute values across rows without collapsing them like GROUP BY does.

Problem: For each customer, find their most recent order and its amount.

WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        amount,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC
        ) AS rn
    FROM orders
)
SELECT customer_id, order_id, amount, created_at
FROM ranked_orders
WHERE rn = 1;

Using ROW_NUMBER instead of RANK ensures exactly one row per customer even when there are ties on created_at. If ties should be preserved, switch to RANK or DENSE_RANK and clarify the requirement with your interviewer.

3. Running Totals and Moving Averages

Time-series problems frequently require cumulative sums or rolling averages. Frame clauses give you precise control over the window.

Problem: Compute a 7-day rolling average of daily revenue.

WITH daily_revenue AS (
    SELECT
        DATE(created_at) AS order_date,
        SUM(amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE(created_at)
)
SELECT
    order_date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_avg
FROM daily_revenue
ORDER BY order_date;

Note the difference between ROWS and RANGE in frame clauses. ROWS counts physical rows, while RANGE considers values, which matters when dates are missing from the dataset.

4. Self-Joins for Comparisons

Self-joins are useful when you need to compare rows within the same table, such as finding employees who earn more than their managers.

Problem: Given an employees table with id, name, salary, and manager_id, find employees who earn more than their direct manager.

SELECT e.name AS employee, e.salary AS employee_salary,
       m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Always consider what happens when manager_id is null. If the requirement is to include top-level employees, use a LEFT JOIN and adjust the WHERE clause accordingly.

5. Gaps and Islands

This classic pattern identifies consecutive rows that share a property. It often appears in sessionization and streak problems.

Problem: Find the longest streak of consecutive login days per user from a logins table with user_id and login_date.

WITH deduped AS (
    SELECT DISTINCT user_id, login_date
    FROM logins
),
grouped AS (
    SELECT
        user_id,
        login_date,
        DATE(login_date) - ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY login_date
        ) AS grp
    FROM deduped
)
SELECT
    user_id,
    MAX(streak_length) AS longest_streak
FROM (
    SELECT user_id, grp, COUNT(*) AS streak_length
    FROM grouped
    GROUP BY user_id, grp
) s
GROUP BY user_id
ORDER BY longest_streak DESC;

The trick is that subtracting a row number from a date produces a constant value for consecutive dates, allowing you to group them into islands.

6. Top-N per Group

Combining window functions with filtering is a frequent interview favorite.

Problem: Find the top 3 highest-paid employees in each department.

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

Using DENSE_RANK means tied salaries all count as the same rank, so you may return more than three rows for a department if there are ties. Clarify whether the interviewer wants exactly three rows or all employees in the top three salary tiers.

Best Practices for the Interview

Common Pitfalls to Avoid

A Realistic Practice Problem

Problem: You have a transactions table with user_id, txn_date, amount, and category. Write a query to find, for each user, the category with the highest total spend, along with that total. If there is a tie, return all tied categories.

WITH category_totals AS (
    SELECT
        user_id,
        category,
        SUM(amount) AS total_spend
    FROM transactions
    GROUP BY user_id, category
),
ranked AS (
    SELECT
        user_id,
        category,
        total_spend,
        RANK() OVER (
            PARTITION BY user_id
            ORDER BY total_spend DESC
        ) AS rnk
    FROM category_totals
)
SELECT user_id, category, total_spend
FROM ranked
WHERE rnk = 1
ORDER BY user_id, category;

This solution uses RANK rather than ROW_NUMBER to preserve ties, directly addressing the requirement. Walking through this reasoning with your interviewer demonstrates both technical skill and product awareness.

Conclusion

Mid-level SQL interviews reward structured thinking, careful edge-case handling, and fluency with a small set of powerful patterns. By mastering aggregations, window functions, self-joins, and the gaps-and-islands technique, and by approaching every problem with a disciplined clarify-then-build framework, you can confidently tackle the vast majority of questions you will face. Practice these patterns on realistic datasets, verbalize your assumptions, and always test your logic against edge cases before declaring a query complete. With consistent preparation, SQL interviews become less about memorizing syntax and more about demonstrating the clear, methodical thinking that hiring managers value.

— Ad —

Google AdSense will appear here after approval

← Back to all articles