How to use this guide

SQL interviews are unusually predictable. The same six or seven question patterns show up across every analyst, data-engineer and backend role: filter & aggregate, join with conditions, window-function ranking or running totals, CTE-based step-by-step transforms, gap-and-island sessionisation, and one or two query-optimisation discussions. The bar varies — junior screens stop at GROUP BY; senior loops add execution plans and indexing — but the core vocabulary does not.

This guide is structured so you can prep in any order. If you have a date for an Amazon interview, jump to the Amazon hub. If you know your weak spot is window functions, jump to that scenario category. If you are starting from scratch, follow the level ladder: Level 1 → Level 4 covers 80% of what an analyst loop will throw at you, Level 5 → Level 7 covers senior IC and TL interviews, and Level 8 → Level 10 is staff-and-above territory.

The seven SQL interview question patterns

After grading several hundred candidate sessions, every "new" SQL interview question fits into one of seven shapes. Memorise the pattern and the answer template falls out automatically.

Prep by difficulty level: from screening to staff

We grade interview questions on a 10-point ladder that mirrors what real loops ask. The bar moves not just in question difficulty but in expected discussion depth — a Level 7 candidate is expected to talk about index design and write the same query, a Level 1 candidate is just expected to write it.

Prep by question type

Most SQL interviews are coding rounds, but they are not all coding. The seven question types we see in the wild — and how to prepare for each — are:

• Query-based (write the SQL): the canonical coding problem; biggest payoff for time spent.
• Scenario-based: ambiguous business prompt, you ask clarifying questions then write SQL — common at FAANG and high-bar startups.
• Output-prediction: given a schema and a query, predict the result set — checks if you actually understand JOIN semantics and NULL behaviour.
• Debugging: given a broken query, find and fix the bug — favoured by Microsoft and SQL-heavy data-engineering roles.
• Optimisation: given a working query, make it 10× faster — paired with index design at L5+.
• Multiple-choice and true/false: knowledge screens; common in third-party platforms and HR-led screens.
• Communication prompts: explain a concept (window functions, MVCC) in plain English — hybrid IC + manager interviews.

Browse a curated bank of each type at /interview/topic — pick one type per practice session and run 5 problems back-to-back.

Prep by company: 8 hubs of real, sourced questions

Each company hub below collects publicly reported SQL interview questions from that company's loops, mapped to the exact pattern (from the seven above) and to a runnable problem you can solve in the editor. Companies are listed in rough order of how SQL-heavy their interviews tend to be.

• Amazon — heavy on optimisation, joins and aggregations against retail-style schemas. /company/amazon
• Google — leans into window functions, gap-and-island, and ambiguous scenario prompts. /company/google
• Meta — analytics SQL, A/B-test result interpretation, funnel queries. /company/meta
• Microsoft — debugging round is common; expect output-prediction and CTE-heavy problems. /company/microsoft
• Apple — schema-design discussion plus moderate query difficulty. /company/apple
• Netflix — analytics-heavy; window functions and event-stream sessionisation. /company/netflix
• Uber — geospatial and time-series flavoured queries; high bar on optimisation. /company/uber
• Airbnb — funnel analysis, cohort queries, marketplace ranking. /company/airbnb

Prep by topic: 14 scenario categories

If your weak spot is a specific topic — joins, ranking, dates — drill that scenario category until you can write the answer template from muscle memory. Each category has 10–25 progressive problems, beginner to advanced, with runnable solutions.

Five sample problems with answer templates

1. Second-highest salary (the canonical screen question)

WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE r = 2;

2. Customers who placed orders in two consecutive months

WITH monthly AS (
  SELECT customer_id, DATE_TRUNC('month', order_date) AS m
  FROM orders
  GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
paired AS (
  SELECT customer_id, m,
         LAG(m) OVER (PARTITION BY customer_id ORDER BY m) AS prev_m
  FROM monthly
)
SELECT DISTINCT customer_id
FROM paired
WHERE m = prev_m + INTERVAL '1 month';

3. Top 3 products by revenue per category

WITH ranked AS (
  SELECT category_id, product_id, SUM(line_total) AS revenue,
         RANK() OVER (PARTITION BY category_id ORDER BY SUM(line_total) DESC) AS r
  FROM order_items
  GROUP BY category_id, product_id
)
SELECT * FROM ranked WHERE r <= 3;

4. Detect 3+ consecutive login days (gap-and-island)

WITH d AS (
  SELECT user_id, login_date,
         login_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY login_date
         ) AS grp
  FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, COUNT(*) AS streak_len
FROM d
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

5. Manager chain (recursive CTE)

WITH RECURSIVE chain AS (
  SELECT employee_id, manager_id, 1 AS depth
  FROM employees
  WHERE employee_id = :start_id
  UNION ALL
  SELECT e.employee_id, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.employee_id = c.manager_id
)
SELECT * FROM chain;

A four-week structured prep plan

• Week 1 — Fundamentals review: SELECT, WHERE, JOIN, GROUP BY, ORDER BY. 30 problems from /scenario/select-statements, /scenario/where-clause, /scenario/aggregations.
• Week 2 — Intermediate patterns: subqueries, CASE, deduplication, basic ranking. 30 problems from /scenario/subqueries, /scenario/case-statements, /scenario/finding-duplicates, /scenario/ranking-nth-value.
• Week 3 — Advanced patterns: window functions, CTEs, self-joins, recursive CTEs. 25 problems from /scenario/ctes-window and /scenario/self-joins-hierarchical, plus the entire window-functions module at /learn/window-functions.
• Week 4 — Company-specific drilling: pick your target hub (Amazon, Google, Meta, etc.) and run 20 problems back-to-back, talking through your reasoning out loud. End each session with one optimisation/explain-plan question from /interview/level-5-senior or above.

What interviewers actually grade (and what trips up candidates)

• Correctness on edge cases: ties, NULLs, empty result sets. The single biggest cause of fail.
• Readable structure: prefer CTEs over deeply nested subqueries; one transformation per CTE.
• Stating assumptions out loud: "I am assuming order_date is never null" — interviewers want to hear this.
• Knowing when JOIN vs EXISTS vs IN matters and when it does not.
• For senior roles, talking through the execution plan: is this an index seek? Hash join? Sort merge? What changes if the table is 1B rows?