What a Modern SQL Interview Looks Like

SQL interviews still show up in analytics, data science, data engineering, and product roles, but the format has evolved. Whiteboards are rare. Live collaborative editors, warehouse-style dialects, and follow-up business questions are common. The candidate who wins is usually not the one who memorized the most syntax. It is the one who can define the problem, write clean SQL, and explain the result like a working data professional.

1. The formats you should expect today

A modern SQL round is usually one of these formats:

• A live coding interview in a browser-based editor with the interviewer watching in real time.
• An online assessment with one or more timed SQL tasks.
• A combined analytics interview where SQL is followed by interpretation, product reasoning, or experiment questions.
• A take-home or async exercise that expects production-quality SQL and a written explanation.

Ask the recruiter which SQL dialect is closest to the interview environment. Even when the company says "any dialect is fine," it helps to state your assumptions if the syntax would differ in Postgres, BigQuery, Snowflake, or Databricks SQL.

2. What interviewers are really scoring

Interviewers care about more than whether your final query runs. They are usually evaluating five things at once:

• Problem framing: did you clarify definitions such as "active user," "top," or "conversion" before coding?
• Correctness: does the query actually answer the prompt and handle edge cases?
• Structure: are you using clear aliases, readable CTEs, and sensible naming?
• Communication: are you explaining your logic and tradeoffs as you go?
• Debugging: when something is off, do you inspect intermediate results instead of guessing?

That is why strong candidates pause first. They confirm the grain of the data, define the metric, and only then start typing.

3. A representative interview prompt

Imagine you are interviewing for a subscription product team. You receive a table of daily listening minutes by creator:

-- creator_daily_minutes
-- event_date DATE
-- country VARCHAR
-- creator_id BIGINT
-- minutes_played BIGINT

The interviewer asks: return the top three creators in the United States for the last full week, include their total minutes, and compare each creator to their previous week's total.

A clean solution might look like this:

WITH weekly_minutes AS (
    SELECT
        DATE_TRUNC('week', event_date) AS week_start,
        creator_id,
        SUM(minutes_played) AS total_minutes
    FROM creator_daily_minutes
    WHERE country = 'US'
      AND event_date >= CURRENT_DATE - INTERVAL '14 days'
    GROUP BY 1, 2
),
ranked_weeks AS (
    SELECT
        week_start,
        creator_id,
        total_minutes,
        LAG(total_minutes) OVER (
            PARTITION BY creator_id
            ORDER BY week_start
        ) AS prior_week_minutes,
        DENSE_RANK() OVER (
            PARTITION BY week_start
            ORDER BY total_minutes DESC
        ) AS creator_rank
    FROM weekly_minutes
)
SELECT
    week_start,
    creator_id,
    total_minutes,
    prior_week_minutes,
    total_minutes - COALESCE(prior_week_minutes, 0) AS delta_minutes
FROM ranked_weeks
WHERE week_start = DATE_TRUNC('week', CURRENT_DATE - INTERVAL '7 days')
  AND creator_rank <= 3
ORDER BY creator_rank, creator_id;

The SQL matters, but so does the conversation around it. A strong candidate would also ask whether the company uses Monday- or Sunday-based weeks, whether ties should all be returned, and whether "last full week" excludes the current partial week.

4. Common mistakes that cost good candidates

• Starting to code before defining the metric or output grain.
• Writing one huge query instead of using CTEs to separate steps.
• Ignoring nulls, ties, duplicate rows, or time boundaries.
• Using a window function when a simple aggregate would be clearer, or vice versa.
• Returning the right answer but failing to explain what the result means.

One subtle mistake is over-optimizing too early. In most interview loops, readable and correct beats clever and fragile.

5. How to practice efficiently

Practice in three layers:

• Core mechanics: joins, grouping, subqueries, dates, case expressions, and window functions.
• Interview patterns: top-N, retention, funnels, deduplication, running totals, ranking, and period-over-period change.
• Communication: say the assumptions, verify the intermediate outputs, and summarize the business takeaway.

If you want realistic reps, solve problems in the SQLPad practice library and then rerun your answer in the SQL playground so you get used to iterating under time pressure.