I've been on both sides of the SQL interview table somewhere around 20 times in a single job search. The pattern is always the same: candidates know SELECT, they know JOIN, they can write a WHERE clause. Then the interviewer asks them to deduplicate a table, find missing rows, or sessionize an event stream, and the wheels come off. Not because the syntax is hard. Because candidates don't understand what each row represents before they start writing.
32% of data engineering interview questions test GROUP BY. INNER JOIN sits at 29%, PARTITION BY at 21%, ROW_NUMBER at 15%. These twelve problems cover roughly 80% of what interviewers actually ask. Master them and you're ahead of most candidates walking into a live coding round.
Want to practice these for real? Solve these problems live here with a real editor and graded solutions.
1. Customers Who Spent Over $500
The question: Given an orders table with columns customer_id, order_date, and amount, find all customers whose total spend exceeds $500. Return customer_id and total_spent, sorted by total descending.
SELECT customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500
ORDER BY total_spent DESC;
Why it matters: 70% of candidates confuse WHERE and HAVING. SQL's execution order runs FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. You cannot filter on an aggregate in WHERE because the aggregate doesn't exist yet. Putting SUM(amount) > 500 in WHERE is a parse error in PostgreSQL. The interviewer isn't testing whether you know HAVING exists; they're testing whether you understand when each clause runs. If you can state the execution order aloud before writing, you'll never make this mistake.
2. The Double-Counting Trap
The question: Given orders(order_id, customer_id, order_date) and order_items(item_id, order_id, product_id, quantity, price), find each customer's total revenue and total number of orders. An order can have multiple items.
WITH order_totals AS (
SELECT order_id,
customer_id,
SUM(quantity * price) AS order_revenue
FROM orders
JOIN order_items USING (order_id)
GROUP BY order_id, customer_id
)
SELECT customer_id,
COUNT(*) AS num_orders,
SUM(order_revenue) AS total_revenue
FROM order_totals
GROUP BY customer_id;
Why it matters: 80% of candidates fail the aggregation problem at Meta data engineering interviews, typically due to double-counting. If you join orders to order_items and then do COUNT(DISTINCT order_id) alongside SUM(quantity * price) in one pass, the count might look right but you've still joined at the wrong grain. The fix is CTEs: aggregate at each grain level separately, then join the results. Strong candidates use CTEs defensively. Weaker ones skip them and wonder why the revenue number is 3x too high.
3. Latest Record Per Customer
The question: Given a customer_updates table with customer_id, updated_at, email, and status, return only the most recent row per customer.
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_at DESC
) AS rn
FROM customer_updates
)
SELECT customer_id, updated_at, email, status
FROM ranked
WHERE rn = 1;
Why it matters: This pattern shows up in 80% of analyst and DE interview loops. The trap is candidates who reach for GROUP BY customer_id with MAX(updated_at), then realize they can't retrieve the email and status columns without aggregating them too. Window functions preserve the full row while computing per-group statistics, which is exactly why interviewers frame this as "try GROUP BY first, then reframe." The follow-up they'll push: "What happens when two rows have the same updated_at?" Your ROW_NUMBER is non-deterministic without a tiebreaker. Add a secondary sort column. Always.
4. Deterministic Deduplication With Tie-Breaking
The question: Same table as above, but now two updates can arrive at the exact same timestamp. Write a query that always returns the same row for a given customer, regardless of physical storage order.
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_at DESC,
ctid DESC , Postgres: use a surrogate/sequence in production
) AS rn
FROM customer_updates
)
SELECT customer_id, updated_at, email, status
FROM ranked
WHERE rn = 1;
Why it matters: Most candidates write the ROW_NUMBER query from Problem 3 and stop. Real production deduplication adds a secondary sort: ORDER BY updated_at DESC, source_sequence_number DESC to ensure the same row wins every run. Without it, two executions on different database instances can return different results. Interviewers probe this when your first answer looks clean. The lesson: ROW_NUMBER is deterministic in its assignment but non-deterministic in its choice when ties exist in the ORDER BY. Name the tiebreaker or admit the query is unstable.
5. Customers With No Orders (Anti-Join)
The question: Given customers(customer_id, name) and orders(order_id, customer_id, order_date), find all customers who have never placed an order.
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
Why it matters: Anti-joins expose whether you understand missingness. The LEFT JOIN plus IS NULL pattern finds rows in A that have no match in B. Interviewers test this because funnel leaks hide in production analytics; "which users signed up but never converted?" is exactly this query. The follow-up is always: "Would NOT EXISTS work?" Yes, and it's semantically safer when join keys might be nullable. Modern query planners treat both identically, so the choice is about clarity, not performance.
6. NOT IN vs NOT EXISTS With NULLs
The question: Given the same tables, explain why this query returns zero rows when the orders table contains a NULL customer_id:
, This silently returns ZERO rows if any order has customer_id IS NULL
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
, Safe version: NOT EXISTS handles NULLs correctly
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
Why it matters: This is a silent killer in sql interview rounds. When a subquery contains even one NULL, NOT IN returns zero rows because every comparison against NULL evaluates to UNKNOWN. The query runs without error. It returns a result set. The result set is wrong. Candidates who default to NOT EXISTS signal they've been burned by this in production. Candidates who reach for NOT IN signal they haven't.
7. Recursive CTE Org Chart
The question: Given employees(employee_id, name, manager_id), find all employees (direct and indirect reports) under employee_id = 1. Return employee_id, name, and depth level.
WITH RECURSIVE org AS (
SELECT employee_id, name, manager_id, 0 AS depth
FROM employees
WHERE employee_id = 1
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, org.depth + 1
FROM employees e
JOIN org ON e.manager_id = org.employee_id
)
SELECT employee_id, name, depth
FROM org
ORDER BY depth, name;
Why it matters: JPMorgan explicitly asks this. Recursive CTEs separate candidates who understand anchor members, recursive members, and termination conditions from those who guess. The recursive step stops when it returns zero rows; if your data has circular references (employee A reports to B, B reports to A), you get an infinite loop. Mention this unprompted and you signal production experience. Ironically, recursive CTEs are vanishingly rare in production code because most hierarchies are shallow (3 to 5 levels) and materialized paths are faster. Interviewers test it as a proxy for "can you think recursively?" not "will you ship this?"
8. Self-Join for Consecutive-Day Logins
The question: Given logins(user_id, login_date) (deduplicated to one row per user per day), find users who logged in on at least 3 consecutive days. Return user_id and the start date of each streak.
WITH islands AS (
SELECT user_id,
login_date,
login_date - ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
)::int AS grp
FROM logins
)
SELECT user_id,
MIN(login_date) AS streak_start,
COUNT(*) AS streak_length
FROM islands
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
Why it matters: This is the classic gaps and islands problem disguised as a product question. The ROW_NUMBER minus date trick works because consecutive dates produce the same constant when you subtract a sequential integer. Non-consecutive dates break the constant, creating a new group. Interviewers love it because candidates who haven't seen the pattern struggle to invent it under pressure, and candidates who have seen it still need to explain why it works. The follow-up: "What if login_date has duplicates?" Your entire approach breaks. Deduplicate first or use DENSE_RANK instead of ROW_NUMBER.
9. ROW_NUMBER vs RANK vs DENSE_RANK
The question: Given sales(salesperson_id, region, revenue), rank each salesperson within their region by revenue. Show how results differ across all three ranking functions when two salespeople tie.
SELECT salesperson_id,
region,
revenue,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn,
RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS drnk
FROM sales;
, Ties at revenue=500: ROW_NUMBER gives 2,3 (arbitrary); RANK gives 2,2 then skips to 4; DENSE_RANK gives 2,2 then 3
Why it matters: 80% of data engineering interviews include window functions questions, yet candidates report feeling least prepared for them. The distinction is semantic, not performance; all three execute at similar speeds in modern query planners. ROW_NUMBER for arbitrary tie-breaking (deduplication). RANK when gaps after ties are acceptable (competition scoring). DENSE_RANK when you need continuous ranks. A real incident: a product ranking dashboard showed positions 1,2,3,4,5,6,6,8 because RANK skipped 7 after a tie. Leadership spent half a day troubleshooting before anyone realized the function was correct but the business needed DENSE_RANK. The choice isn't about data; it's about what the business question actually needs.
10. Running Total With Correct Window Frame
The question: Given transactions(account_id, txn_date, amount), compute a running balance per account ordered by transaction date. Then explain why LAST_VALUE often returns unexpected results.
SELECT account_id,
txn_date,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY txn_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_balance
FROM transactions;
Why it matters: The default window frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats ties as a group. ROWS treats each row individually. For running totals this distinction matters when two transactions share the same date. The buried edge case: LAST_VALUE without ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING returns the current row, not the last row in the partition. 70% of first attempts get this wrong. Interviewers test frame specifications because they separate candidates who memorized OVER(PARTITION BY...) from those who actually understand how window boundaries work.
11. Find the Gap in a Sequence
The question: Given seat_reservations(seat_number) containing occupied seats (integers, not necessarily consecutive), find all gaps (unreserved ranges). Return gap_start and gap_end.
WITH boundaries AS (
SELECT seat_number,
LEAD(seat_number) OVER (ORDER BY seat_number) AS next_seat
FROM seat_reservations
)
SELECT seat_number + 1 AS gap_start,
next_seat - 1 AS gap_end
FROM boundaries
WHERE next_seat - seat_number > 1;
Why it matters: Gaps and islands questions slow candidates down because they don't look like standard "join and group by" prompts. The business story sounds custom; the right answer takes a couple of structured steps. LEAD() compares each row to its successor; any difference greater than 1 is a gap. The interviewer will push on edge cases: what about the gap before the first seat? What about the gap after the last? Those require knowing the full domain (e.g., seats 1 through 100) and adding boundary rows. Pattern recognition, not syntax, is the bottleneck.
12. Sessionize an Event Stream
The question: Given page_views(user_id, event_time, page_url), assign a session_id to each event. A new session starts when the gap between consecutive events for the same user exceeds 30 minutes.
WITH flagged AS (
SELECT *,
CASE WHEN event_time - LAG(event_time) OVER (
PARTITION BY user_id ORDER BY event_time
) > INTERVAL '30 minutes'
THEN 1 ELSE 0 END AS new_session
FROM page_views
),
sessioned AS (
SELECT *,
SUM(new_session) OVER (
PARTITION BY user_id ORDER BY event_time
) AS session_id
FROM flagged
)
SELECT user_id, event_time, page_url, session_id
FROM sessioned;
Why it matters: This is the hardest pattern most sql interview rounds test. It combines LAG for gap detection, CASE for flag generation, and cumulative SUM for group assignment; three window functions in one query. The first event per user has a NULL lag, which the CASE treats as 0 (no new session), which is correct because it belongs to session 0. If you don't think through that NULL explicitly, your session IDs are off by one. Interviewers also ask: "What if the 30-minute threshold should vary by user?" Now you need a join to a config table inside the CASE expression. That's where it gets real.
The tools change every 18 months. The problems don't change. Schema drift, late-arriving data, upstream teams breaking contracts without telling you. These are eternal. And so is SQL.
These twelve problems aren't a random sample. They're the patterns I've seen repeated across FAANG loops, fintech screens, and startup onsites for years. The syntax is the easy part; the hard part is understanding grain, execution order, and what happens when your data has NULLs, ties, or gaps you didn't expect. If you can solve all twelve cold, you're solid. If you want structured reps with graded feedback, datadriven.io is good for sql interview questions like these.
What's the one SQL problem you'd add to this list? The pattern that tripped you up in a real interview that none of the prep guides cover?