How to Approach a SQL Interview: 5-Step Framework and a 45-Minute Mock Round

Knowing SQL isn't enough to pass a SQL round: interviewers also score how you clarify the problem, reason out loud, check edge cases and talk about performance. This guide gives you a 5-step framework for every question, the phrases to use when stuck, and a full 45-minute mock SQL interview with model answers.

What Interviewers Evaluate

The 5-Step Framework for Every SQL Problem

Clarifying Questions to Ask Every Time

What to Say When You Are Stuck

Optimization Discussion — What Interviewers Want to Hear

-- After writing your solution, say:

-- "This query does a full table scan on employees (N rows) and joins to departments.
--  If employee table has 10M rows, I'd add a composite index on (dept_id, salary)
--  to support the PARTITION BY + ORDER BY in the window function."

-- "The RANK() window function sorts within each partition — O(N log N) overall.
--  For a one-time report this is fine. For a live dashboard, I'd materialise
--  this as a scheduled view refreshed every hour."

-- "I used NOT EXISTS instead of NOT IN because the orders.customer_id column
--  allows NULLs and NOT IN with NULLs returns zero rows — a silent bug."

-- "This correlated subquery runs once per row — O(N²) complexity.
--  I can rewrite it as a window function to make it O(N log N)."

Handling Follow-Up Questions

Common Mistakes That Cost Candidates the Job

Mock Interview: A Full 45-Minute SQL Round

Warm-Up (5 min): Database Design

Easy Questions (15 min): 3 Questions × 5 min

-- Answer: GROUP BY + HAVING with two conditions
SELECT customer_id,
       COUNT(order_id)   AS order_count,
       SUM(amount)       AS total_spent
FROM orders
WHERE YEAR(order_date) = 2025
  AND status != 'cancelled'       -- edge case: should we count cancelled orders?
GROUP BY customer_id
HAVING COUNT(order_id) > 3
   AND SUM(amount) > 10000
ORDER BY total_spent DESC;

-- Edge cases to mention:
-- 1. Include or exclude cancelled orders? (Ask the interviewer!)
-- 2. Is order_date a DATE or DATETIME? (YEAR() works for both)
-- 3. What if amount can be NULL? (SUM ignores NULLs — is that correct?)

-- Answer: Window function approach (handles ties correctly)
SELECT e.name, d.dept_name, e.salary
FROM (
    SELECT emp_id, name, dept_id, salary,
           RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
) e
JOIN departments d ON e.dept_id = d.dept_id
WHERE rnk = 1
ORDER BY d.dept_name;

-- Why RANK not ROW_NUMBER?
-- ROW_NUMBER gives only 1 result per dept even for ties.
-- RANK gives same rank to ties — both tied employees get rank=1.

-- Alternative: subquery approach
SELECT e.name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE (e.dept_id, e.salary) IN (
    SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id
);

-- Answer: Gaps and Islands — ROW_NUMBER subtraction technique
WITH deduped 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 deduped
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*)        AS consecutive_days
FROM grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id, streak_start;

Medium Questions (15 min): 2 Questions × 7.5 min

WITH monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
           SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
    2) AS mom_growth_pct,
    SUM(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM monthly
ORDER BY month;

-- Key points:
-- NULLIF prevents divide-by-zero for first month (prev=NULL)
-- ROWS BETWEEN UNBOUNDED PRECEDING ensures correct running total
-- LAG() called twice — mention could use CTE to compute once for cleanliness

WITH cohorts AS (
    SELECT user_id,
           DATE_FORMAT(event_date, '%Y-%m') AS cohort_month
    FROM user_events
    WHERE event_type = 'signup'
)
SELECT
    c.cohort_month,
    COUNT(DISTINCT c.user_id)                   AS signups,
    COUNT(DISTINCT CASE WHEN e.event_type = 'first_purchase'
          THEN e.user_id END)                   AS first_purchase,
    COUNT(DISTINCT CASE WHEN e.event_type = 'second_purchase'
          THEN e.user_id END)                   AS second_purchase,
    COUNT(DISTINCT CASE WHEN e.event_type = 'churned'
          THEN e.user_id END)                   AS churned,
    ROUND(COUNT(DISTINCT CASE WHEN e.event_type = 'first_purchase'
          THEN e.user_id END) * 100.0
          / NULLIF(COUNT(DISTINCT c.user_id), 0), 2) AS conversion_pct,
    ROUND(COUNT(DISTINCT CASE WHEN e.event_type = 'churned'
          THEN e.user_id END) * 100.0
          / NULLIF(COUNT(DISTINCT c.user_id), 0), 2) AS churn_pct
FROM cohorts c
LEFT JOIN user_events e ON c.user_id = e.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

Hard Question (10 min)

WITH signed_amounts AS (
    -- Convert debit to negative amount
    SELECT txn_id, account_id, txn_date, txn_type, amount,
           CASE WHEN txn_type = 'credit' THEN amount ELSE -amount END AS signed_amt
    FROM transactions
),
running AS (
    SELECT txn_id, account_id, txn_date, txn_type, amount, signed_amt,
           SUM(signed_amt) OVER (
               PARTITION BY account_id
               ORDER BY txn_date, txn_id  -- txn_id breaks ties on same date
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS running_balance
    FROM signed_amounts
)
SELECT
    txn_id,
    account_id,
    txn_date,
    txn_type,
    amount,
    running_balance,
    CASE WHEN running_balance < 0 THEN '⚠️ OVERDRAFT' ELSE 'OK' END AS status,
    FIRST_VALUE(CASE WHEN running_balance < 0 THEN txn_date END) OVER (
        PARTITION BY account_id
        ORDER BY txn_date, txn_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_overdraft_date
FROM running
ORDER BY account_id, txn_date, txn_id;

FAQs

How do you prepare for a SQL interview?

Practise the core patterns (joins, aggregation, window functions, CTEs, dates), solve problems out loud with a timer, and rehearse a framework: clarify, plan, write, test edge cases, then discuss optimisation.

What should I ask before writing a SQL query in an interview?

Ask about duplicates and NULLs, how ties should be handled, the expected output format, which dialect is used, and the data size.

What if I get stuck in a SQL interview?

Say what you know, solve a simpler version first, and describe the approach in steps; interviewers reward structured reasoning even when the final query isn't perfect.