Interview Drills — SQL Technical Screen

Open-ended interview questions for the most common analyst screen: reading and writing queries under pressure. Each has a Framework (the structure a strong answer follows), a Model answer (a concise example), and a Rubric (what an interviewer listens for). Practice thinking aloud — say the question in English, name the tables and the grain, then write the SQL — and always end by stating how you'd sanity-check the result. The app can role-play these as mock interviews (see mock-interview.md).

The universal SQL-screen structure: Restate the question and the grain you want (one row per…) → name the table(s) and the key to join on → write SELECT / FROM / WHERE / GROUP BY in that logical order → state exactly what the result set means → name one cheap sanity check that would catch a wrong answer. Use it on almost any "read this query" or "write a query" prompt.

D1

difficulty: easy
concept: sql-basics Here's a table orders(order_id, customer_id, country, amount, status, created_at). Write a query that returns the 10 most recent completed orders from Israel, showing the order id, amount, and date.
Framework: Pick the columns (SELECT) → name the table (FROM) → filter rows with the two conditions (WHERE) → sort so "most recent" is on top (ORDER BY … DESC) → cut to 10 (LIMIT). Read it back in English before you call it done.
Model answer: "SELECT order_id, amount, created_at FROM orders WHERE country = 'Israel' AND status = 'completed' ORDER BY created_at DESC LIMIT 10; This returns one row per qualifying order, newest first. I'd double-check the exact strings status uses — 'completed' vs 'complete' vs a code — by running SELECT DISTINCT status FROM orders first, since a wrong literal silently returns zero rows."
Rubric: Strong answers get SELECT/FROM/WHERE/ORDER BY/LIMIT in the right roles, combine the two filters with AND, sort descending for "most recent," and flag that the filter literal must match the real values. Weak answers forget LIMIT, sort the wrong way, use a comma instead of AND, or never question whether 'completed' is the actual stored value.

D2

difficulty: easy
concept: aggregation Same orders table. Write a query for "total revenue and number of orders per country, for completed orders only, busiest country first." Then tell me exactly what one row of the output means.
Framework: Decide the grain of the output (one row per country) → that means GROUP BY country → choose aggregates (SUM(amount), COUNT(*)) → apply the row filter in WHERE before grouping → sort by the aggregate. Then describe a single output row in plain English.
Model answer: "SELECT country, SUM(amount) AS revenue, COUNT(*) AS order_count FROM orders WHERE status = 'completed' GROUP BY country ORDER BY revenue DESC; One row means: 'for this country, across all its completed orders, here is the summed amount and the count of those orders.' Sanity check: the sum of all order_count values should equal the total number of completed orders, and revenue per country should never exceed total revenue."
Rubric: Strong answers GROUP BY the same column they SELECT non-aggregated, put the status filter in WHERE (not after aggregation), alias the aggregates readably, and can articulate the output grain. Weak answers select a column that isn't in the GROUP BY or aggregated, confuse COUNT(*) with COUNT(DISTINCT …), or can't say what one row represents.

D3

difficulty: medium
concept: joins You have orders(order_id, customer_id, amount, created_at) and customers(customer_id, name, country, signup_date). Write a query showing each order's id and amount alongside the customer's name and country. Which join do you use, and why does the choice matter?
Framework: Identify the shared key (customer_id) → pick the join type based on whether you want orders without a matching customer (INNER drops them, LEFT keeps them) → write the join on the key → qualify columns with table aliases to avoid ambiguity → state the assumption you're making.
Model answer: "SELECT o.order_id, o.amount, c.name, c.country FROM orders o JOIN customers c ON o.customer_id = c.customer_id; I use an INNER JOIN if every order should have a customer and I want to see if some don't — but I'd first run a LEFT JOIN and count WHERE c.customer_id IS NULL to detect orphan orders. The choice matters because an INNER JOIN silently drops orders whose customer is missing, which would understate revenue without warning."
Rubric: Strong answers join on the correct key, use table aliases, and reason explicitly about INNER vs LEFT and the risk of silently dropping rows. Weak answers join on the wrong column, forget the ON clause (producing a cross join), leave column references ambiguous, or treat "which join" as arbitrary rather than a correctness decision about dropped rows.

D4

difficulty: medium
concept: reading-queries Read this query aloud and tell me what it returns — and one thing that could make it misleading: SELECT country, COUNT(*) AS n FROM customers WHERE signup_date >= '2024-01-01' GROUP BY country ORDER BY n DESC;
Framework: Translate clause by clause (filter → group → count → sort) → state the output grain in one sentence → then probe a hidden assumption: data quality, NULLs, or the filter boundary.
Model answer: "It returns one row per country, counting customers who signed up on or after Jan 1 2024, ordered from the country with the most such signups down. What could mislead: if country has inconsistent values like 'USA' and 'United States', those split into two rows and undercount the real country; and rows with a NULL country are grouped into their own bucket or, depending on the engine, may surprise you. I'd run SELECT DISTINCT country to check for formatting variants before trusting the ranking."
Rubric: Strong answers translate every clause correctly, name the output grain, and surface a real data-quality trap (inconsistent formatting, NULLs, or the inclusive >= boundary). Weak answers misread COUNT(*) as counting something other than rows-per-group, miss that the result is per-country, or offer no plausible failure mode.

D5

difficulty: medium
concept: sanity-checking You wrote a query for "monthly revenue in 2024" and it returns one month with revenue 10x every other month. The interviewer asks: walk me through how you'd figure out whether that spike is real or a bug.
Framework: Don't trust or dismiss the number — investigate. Check the data (duplicates, an outlier order, a currency/unit mix) → check the query (a join fan-out double-counting rows, a wrong filter) → drill into that one month and eyeball the raw rows → confirm against a second source if one exists. Decide and document.
Model answer: "First I'd query that single month's orders ordered by amount descending and eyeball the top rows — one giant order or a cluster of suspicious ones explains a lot. I'd check for duplicates with SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1, since a join to a one-to-many table can fan out and double-count revenue. I'd confirm amount is in one currency. If the rows are real and clean, the spike is real — maybe a promotion — and I'd note that; if it's a fan-out or duplicate, I'd fix the join. Either way I write down what I found."
Rubric: Strong answers treat a surprising result as a prompt to investigate (per Topic 7: "suspect the data before believing the surprise"), name concrete causes — duplicates, join fan-out, unit/currency mix, bad filter — drill into the raw rows, and commit to documenting the finding. Weak answers either accept the number uncritically or hand-wave "it's probably a bug" without a method to confirm.

D6

difficulty: hard
concept: aggregation-filtering orders(order_id, customer_id, amount, status, created_at). Write a query that finds customers who placed more than 5 completed orders in 2024, showing the customer id and their order count, highest first. Then explain why your filter for "more than 5" is where it is.
Framework: Filter rows first (status + year) in WHERE → group to one row per customer → compute the per-customer count → filter groups by the count using HAVING (not WHERE, which runs before grouping) → sort. Articulate the WHERE-vs-HAVING distinction.
Model answer: "SELECT customer_id, COUNT(*) AS completed_orders FROM orders WHERE status = 'completed' AND created_at >= '2024-01-01' AND created_at < '2025-01-01' GROUP BY customer_id HAVING COUNT(*) > 5 ORDER BY completed_orders DESC; The > 5 lives in HAVING because it's a condition on the aggregated count, which doesn't exist until after GROUP BY — WHERE is evaluated row-by-row before grouping, so it can't see COUNT(*). Sanity check: spot-check one returned customer by listing their 2024 completed orders and confirm there are indeed more than five."
Rubric: Strong answers correctly split row-level filters (WHERE) from group-level filters (HAVING), use a half-open date range (>= start AND < next-year) rather than a fragile BETWEEN/<= on timestamps, order by the count, and can explain why HAVING is required. Weak answers try to put COUNT(*) > 5 in WHERE, mishandle the year boundary, or can't explain the WHERE/HAVING execution order.