Top 50 SQL Interview Questions with Answers (2026)
Quick Overview
A checklist of the 50 SQL interview questions data analysts, data scientists, and analytics engineers face, grouped by topic and difficulty, with a worked answer for each one. Every query runs on PostgreSQL as written, with sample data and results inlined where results are shown, and the hardest patterns are tied to real questions asked at Meta, DoorDash, TikTok, and OpenAI.
SQL interviews reward pattern recognition, not trivia. Nearly every question you will face is a variation on five families: filtering with correct NULL logic, joining tables, aggregating with GROUP BY and CASE, ranking and comparing rows with window functions, and decomposing multi-step logic into CTEs. This page lists the 50 questions that come up most often for data analyst, data scientist, and analytics engineer screens, grouped by topic and ordered by difficulty, with a worked PostgreSQL answer for each one.
Use it as a checklist. If you can solve every category below from a blank editor, you are ready. When you want real questions to practice on, the SQL interview questions page lists the SQL questions reported in our bank, and most of them run in a browser SQL editor.
Key Takeaways
- Joins and window functions decide most SQL screens. If you are short on time, drill those two families first and skip the rest.
- "Who is missing" always means
LEFT JOIN ... IS NULLorNOT EXISTS— neverNOT IN, which silently returns zero rows the moment the subquery actually produces a NULL. ROW_NUMBER,RANK, andDENSE_RANKonly differ when ties appear. Interviewers create ties on purpose to see whether you chose deliberately.- NULL handling is scored even when the question never mentions it: guard divisions with
NULLIF, compare withIS DISTINCT FROM, and remember aggregates skip NULLs exceptCOUNT(*). - Every query below runs on PostgreSQL as written, with sample data inlined where results are shown. Read the answer, close the page, then re-derive it. Recognition is not recall.

How to use this list
The 50 questions are grouped into five sections: basics and NULL logic (1–8), joins and set operations (9–18), aggregation and CASE (19–28), window functions (29–40), and CTEs plus DML and optimization (41–50). Each section opens with the full question list, then gives a worked answer for every question in it. Several answers are tied to questions asked at Meta, DoorDash, TikTok, and OpenAI, taken from reports in our bank.
If analytics SQL is newer to you, the six core patterns in SQL for Data Analysis are the gentler on-ramp; come back here once GROUP BY feels automatic.
Map the question to a pattern first
Before you type, name the family. Most prompts announce themselves once you know the tells.
| Question signal | Likely pattern | First move |
|---|---|---|
| "Who has never / who is missing" | LEFT JOIN + IS NULL | Outer join, filter on the null side |
| "Top N per group", "rank within" | Window function | ROW_NUMBER/RANK with PARTITION BY |
| "Compared to previous/next period" | Window function | LAG/LEAD over an ordered set |
| "Running total", "moving average" | Window function | SUM/AVG OVER (ORDER BY ...) |
| "Step by step", "then using that..." | CTE | Chain named WITH blocks |
| "Hierarchy", "longest streak" | Recursive CTE / gaps-and-islands | Anchor + recursive member |
| "Per group totals with a condition" | Aggregation + HAVING | GROUP BY then filter groups |
| "Pivot rows into columns" | Conditional aggregation | SUM(CASE WHEN ...) |
Freshers: start with the basic SQL questions, Q1–28
If this is your first analyst or data-engineering screen, the fresher set is Q1–28: basics and NULL logic (1–8), joins and set operations (9–18), aggregation and CASE (19–28). Entry-level screens rarely ask for a window function. They ask you to filter with the right operator, join two or three tables without inflating the row count, and group with a HAVING clause that filters groups rather than rows.
What gets scored is often not the query. Whether you notice NULLs comes first: = NULL matching nothing, and NOT IN collapsing to zero rows, are the two traps that show up in almost every entry-level screen. Then whether you reach for LEFT JOIN on a "who has never" prompt instead of an inner join. Then whether you say the output grain out loud — one row per customer, per month, per what.
Work Q1–28 in order before touching the rest. If joins still take effort, SQL Joins walks every join type with exact result sets, and SQL GROUP BY, Aggregate Functions, and HAVING covers the aggregation half. For an honest sense of the runway, How Long Does It Take to Learn SQL? puts hours on it.
For experienced candidates: Q29–50 is where the screen is decided
With a few years of SQL behind you, the first 28 questions are a warm-up you should clear without thinking. The decision happens in Q29–50: window functions (29–40), then CTEs, DML, and optimization (41–50).
Senior screens probe past correctness. Deliberate choice: not "does RANK work" but why DENSE_RANK rather than ROW_NUMBER given the ties in the data, and what a missing tiebreaker does to a "latest row" query. Frame awareness: ROWS versus RANGE, why LAST_VALUE under the default frame stops at the current row's peers instead of the partition's last row, whether a 7-day moving average spans 7 rows or 7 calendar days. Then debugging, where you are handed someone else's wrong query and asked which stage lost the rows.
Expect at least one multi-step problem stated in a single sentence and left for you to decompose: cohort the users, order each user's events, aggregate per group. Chaining named CTEs is what keeps that legible while you narrate it. If the frame-clause details read as new, the window functions guide builds them properly, and SQL Order of Operations is why you cannot filter on a window function in WHERE.
Questions 1–8: Basics, filtering, and NULL logic
Screens for junior analyst roles often start and end here. Senior screens skip the syntax but still probe the NULL semantics, because that is where production bugs live.
- Select the top 10 rows sorted by a column (
ORDER BY+LIMIT) - Filter rows on multiple conditions with
AND,OR, and correct parenthesization - Explain
<>vs!=, and why neither ever matches a NULL - Find rows where a column is NULL (and why
= NULLreturns nothing) - Replace NULLs with a default using
COALESCE - Deduplicate values:
DISTINCTvsGROUP BY - Explain the difference between
WHEREandHAVING - Extract parts of a date:
date_trunc,EXTRACT, and grouping by month
Worked answer (Q1): top 10 rows. Sort first, then cut. Without ORDER BY, LIMIT returns whichever 10 rows the engine reaches first, and those can change from one run to the next.
SELECT order_id, customer_id, amount
FROM orders
ORDER BY amount DESC, order_id
LIMIT 10;
The second sort key, order_id, settles ties so the same 10 rows come back every time. If the interviewer wants every row that ties with the 10th amount, drop the tiebreaker and write ORDER BY amount DESC FETCH FIRST 10 ROWS WITH TIES in PostgreSQL. SQL Server spells the plain version SELECT TOP 10.
Worked answer (Q2): AND, OR, and parentheses. AND binds tighter than OR, so an unbracketed mix can let in rows that fail the condition you meant to apply to all of them:
WITH orders(order_id, region, status, amount) AS (
VALUES (1, 'NA', 'completed', 80),
(2, 'NA', 'refunded', 500),
(3, 'EU', 'pending', 200),
(4, 'NA', 'pending', 20)
)
SELECT order_id
FROM orders
WHERE region = 'NA'
AND (status = 'completed' OR amount > 100)
ORDER BY order_id;
| order_id |
|---|
| 1 |
| 2 |
Without the parentheses the filter reads as (region = 'NA' AND status = 'completed') OR amount > 100, which also returns order 3 from the EU. Bracket every WHERE that mixes the two, even when precedence happens to give the right answer, so nobody has to work it out.
Worked answer (Q3–Q4): the NULL comparison trap. Any comparison with NULL evaluates to unknown, and WHERE drops unknown rows. Watch what happens with this table:
WITH products(product_id, discount) AS (
VALUES (1, 10), (2, 15), (3, NULL)
)
SELECT product_id
FROM products
WHERE discount <> 10;
| product_id |
|---|
| 2 |
Product 3 vanished. NULL <> 10 is unknown, not true, so the row is filtered out even though its discount is plainly not 10. If you want NULLs to count as "not equal", say so explicitly:
WITH products(product_id, discount) AS (
VALUES (1, 10), (2, 15), (3, NULL)
)
SELECT product_id
FROM products
WHERE discount IS DISTINCT FROM 10;
| product_id |
|---|
| 2 |
| 3 |
The <> vs != half of the question is a softball (they are identical in every major engine; <> is the standard spelling), but the NULL behavior behind it fails real candidates. The full set of traps, including how NOT IN inherits the same problem, is worked through in SQL Not Equal: <> vs !=, and the NULL Traps That Follow.
Worked answer (Q5): defaulting NULLs. COALESCE returns its first non-NULL argument, which makes it the standard tool for both display defaults and NULL-safe math:
SELECT
order_id,
COALESCE(discount, 0) AS discount_applied,
price * (1 - COALESCE(discount, 0) / 100.0) AS final_price
FROM orders;
Two details interviewers listen for: COALESCE short-circuits left to right, and it is not the same as NULLIF, which goes the other direction (turns a value into NULL). Engine-specific variants and the classic mistakes are covered in SQL COALESCE: Syntax, Engine Differences, and the NULL Traps Interviewers Test.
Worked answer (Q6): DISTINCT vs GROUP BY. For a plain list of unique values they return the same rows: SELECT DISTINCT city FROM orders and SELECT city FROM orders GROUP BY city both give one row per city. Switch to GROUP BY as soon as you need a number per value:
WITH orders(order_id, customer_id, city) AS (
VALUES (1, 10, 'Austin'), (2, 10, 'Austin'),
(3, 11, 'Austin'), (4, 12, 'Boston')
)
SELECT
city,
COUNT(*) AS orders,
COUNT(DISTINCT customer_id) AS customers
FROM orders
GROUP BY city
ORDER BY city;
| city | orders | customers |
|---|---|---|
| Austin | 3 | 2 |
| Boston | 1 | 1 |
DISTINCT applies to the whole selected row, not to the first column: SELECT DISTINCT city, customer_id returns every distinct pair. Adding DISTINCT to hide duplicate rows after a join usually covers up a fan-out bug rather than fixing it (see Q17).
Worked answer (Q7): WHERE vs HAVING. WHERE decides which rows go into the groups, and HAVING decides which finished groups come out. Many queries need both:
WITH orders(customer_id, status, amount) AS (
VALUES (1, 'completed', 70), (1, 'completed', 60),
(2, 'completed', 90), (2, 'refunded', 400),
(3, 'completed', 30)
)
SELECT customer_id, SUM(amount) AS completed_spend
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 100
ORDER BY customer_id;
| customer_id | completed_spend |
|---|---|
| 1 | 130 |
Drop the WHERE and customer 2's refunded order counts toward the total, so 490 passes the HAVING test and customer 2 shows up. An aggregate can't go in WHERE at all, because the groups don't exist yet when it runs. Q21 below uses HAVING with two aggregate conditions.
Worked answer (Q8): grouping by month. date_trunc('month', ts) keeps the year, so January 2025 and January 2026 stay apart. EXTRACT(MONTH FROM ts) returns the number 1 for both and merges them:
WITH orders(order_id, order_ts, amount) AS (
VALUES (1, TIMESTAMP '2025-01-15 10:00', 40),
(2, TIMESTAMP '2026-01-05 09:30', 60),
(3, TIMESTAMP '2026-01-28 18:10', 30),
(4, TIMESTAMP '2026-02-02 11:00', 25)
)
SELECT
date_trunc('month', order_ts)::date AS month_start,
COUNT(*) AS orders,
SUM(amount) AS revenue
FROM orders
GROUP BY date_trunc('month', order_ts)
ORDER BY month_start;
| month_start | orders | revenue |
|---|---|---|
| 2025-01-01 | 1 | 40 |
| 2026-01-01 | 2 | 90 |
| 2026-02-01 | 1 | 25 |
Use EXTRACT when you want the part on its own, such as the hour of day for a "busiest hour" question or the day of the week for a weekday pattern. For month-by-month reporting, truncate.
Questions 9–18: Joins and set operations
These are table stakes. If you cannot write a LEFT JOIN without pausing, drill here until it is automatic, because a stumble on joins ends screens faster than anything else.
- Find all customers who have never placed an order (
LEFT JOIN+ NULL check) - Explain
INNERvsLEFTvsFULL OUTERjoin: which rows survive each one - Join users and orders to compute total spend per user
- Self-join: find all pairs of employees in the same department
- Find orders placed within 7 days of signup (date-range join)
- Find customers who ordered in both January and February
- Explain
UNIONvsUNION ALL, including what "duplicate" means for NULLs - Anti-join correctly:
NOT EXISTSvsNOT INand the NULL trap - Join three tables and keep the row count under control (fan-out)
- Show each product with its most recent order date
Worked answer (Q9): customers with no orders. Outer-join, then keep only the rows where the right side failed to match:
WITH customers(customer_id, name) AS (
VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Chen')
),
orders(order_id, customer_id) AS (
VALUES (101, 1), (102, 1), (103, 3)
)
SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;
| customer_id | name |
|---|---|
| 2 | Ben |
An INNER JOIN can never answer a "who is missing" question. The missing rows have nothing to match, so an inner join discards exactly the rows you were asked to find.
Worked answer (Q10): which rows each join keeps. Count the output of each join on two small key lists:
WITH a(id) AS (VALUES (1), (2), (3)),
b(id) AS (VALUES (2), (3), (4))
SELECT
(SELECT COUNT(*) FROM a JOIN b ON b.id = a.id) AS inner_rows,
(SELECT COUNT(*) FROM a LEFT JOIN b ON b.id = a.id) AS left_rows,
(SELECT COUNT(*) FROM a FULL OUTER JOIN b ON b.id = a.id) AS full_rows;
| inner_rows | left_rows | full_rows |
|---|---|---|
| 2 | 3 | 4 |
INNER keeps the pairs that match (2 and 3). LEFT adds the unmatched left row (1), with NULLs in b's columns. FULL OUTER also adds the unmatched right row (4). With duplicate keys the counts grow, because every match produces its own row. SQL Joins shows each join type with its full result set.
Worked answer (Q11): total spend per user. Start from users, so that users with no orders stay in the result, then turn their missing total into 0:
WITH users(user_id, name) AS (
VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Chen')
),
orders(order_id, user_id, amount) AS (
VALUES (101, 1, 30), (102, 1, 20), (103, 3, 50)
)
SELECT
u.user_id,
u.name,
COALESCE(SUM(o.amount), 0) AS total_spend
FROM users AS u
LEFT JOIN orders AS o
ON o.user_id = u.user_id
GROUP BY u.user_id, u.name
ORDER BY u.user_id;
| user_id | name | total_spend |
|---|---|---|
| 1 | Ana | 50 |
| 2 | Ben | 0 |
| 3 | Chen | 50 |
An inner join would drop Ben. Without COALESCE, Ben's total would be NULL, because SUM over no rows returns NULL rather than 0. Ask whether users with no orders belong in the answer, and say which way you went.
Worked answer (Q12): pairs in the same department. Join the table to itself under two aliases, and keep only pairs where the second id is larger, so each pair appears once:
WITH employees(emp_id, name, dept_id) AS (
VALUES (1, 'Ana', 10), (2, 'Ben', 10), (3, 'Cy', 10), (4, 'Dee', 20)
)
SELECT
a.name AS employee_1,
b.name AS employee_2,
a.dept_id
FROM employees AS a
JOIN employees AS b
ON b.dept_id = a.dept_id
AND b.emp_id > a.emp_id
ORDER BY a.emp_id, b.emp_id;
| employee_1 | employee_2 | dept_id |
|---|---|---|
| Ana | Ben | 10 |
| Ana | Cy | 10 |
| Ben | Cy | 10 |
With <> in place of >, every pair comes back twice (Ana with Ben, then Ben with Ana). With no id condition at all, each employee is also paired with themselves. Dee has no colleague in department 20, so she doesn't appear.
Worked answer (Q13): orders within 7 days of signup. Put the date window in the join condition and say what "within 7 days" means. Here it means the signup day plus the next 6 days:
WITH users(user_id, signup_date) AS (
VALUES (1, DATE '2026-03-01'), (2, DATE '2026-03-05')
),
orders(order_id, user_id, order_date) AS (
VALUES (11, 1, DATE '2026-03-03'),
(12, 1, DATE '2026-03-09'),
(13, 2, DATE '2026-03-11'),
(14, 2, DATE '2026-03-12')
)
SELECT o.order_id, o.user_id, o.order_date
FROM orders AS o
JOIN users AS u
ON u.user_id = o.user_id
AND o.order_date >= u.signup_date
AND o.order_date < u.signup_date + 7
ORDER BY o.order_id;
| order_id | user_id | order_date |
|---|---|---|
| 11 | 1 | 2026-03-03 |
| 13 | 2 | 2026-03-11 |
Order 14 lands exactly 7 days after signup, and the < leaves it out. Use <= if the interviewer counts day 7. With timestamps instead of dates, add INTERVAL '7 days' rather than the integer 7.
Worked answer (Q14): active in both months. Aggregate per customer and count distinct months, rather than joining the table to itself:
SELECT customer_id
FROM orders
WHERE order_date >= DATE '2026-01-01'
AND order_date < DATE '2026-03-01'
GROUP BY customer_id
HAVING COUNT(DISTINCT date_trunc('month', order_date)) = 2;
The half-open date range (>= start, < next period) is a habit worth showing off: it works identically for dates and timestamps and never drops the last day of the month.
Worked answer (Q15): UNION vs UNION ALL. UNION deduplicates the combined result set; UNION ALL keeps everything and is much cheaper because no dedup sort or hash is needed. Two wrinkles catch candidates: UNION treats two NULLs as duplicates of each other (unlike =), and dedup applies across the entire row, not one column. When the inputs cannot overlap, say "these are disjoint, so UNION ALL is correct and faster" — that sentence alone is a senior signal. The full semantics are in UNION vs UNION ALL in SQL: Dedup Semantics, NULL Rules, and the Interview Traps.
Two small lists show both rules, NULLs included:
WITH jan(email) AS (VALUES ('a@x.com'), ('b@x.com'), (NULL)),
feb(email) AS (VALUES ('b@x.com'), (NULL))
SELECT
(SELECT COUNT(*) FROM (SELECT email FROM jan UNION SELECT email FROM feb) AS u) AS union_rows,
(SELECT COUNT(*) FROM (SELECT email FROM jan UNION ALL SELECT email FROM feb) AS u) AS union_all_rows;
| union_rows | union_all_rows |
|---|---|
| 3 | 5 |
UNION keeps a@x.com, b@x.com and a single NULL: the two NULLs count as duplicates even though NULL = NULL is not true.
Worked answer (Q16): why NOT IN betrays you. The tempting alternative to the Q9 query is NOT IN, and it breaks the moment the subquery can produce a NULL:
WITH customers(customer_id) AS (
VALUES (1), (2), (3)
),
orders(customer_id) AS (
VALUES (1), (3), (NULL)
)
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
This returns zero rows. For customer 2, the predicate expands to 2 <> 1 AND 2 <> 3 AND 2 <> NULL; that last comparison is unknown, which poisons the whole conjunction. NOT EXISTS has no such failure mode and is what you should reach for by default:
WITH customers(customer_id) AS (
VALUES (1), (2), (3)
),
orders(customer_id) AS (
VALUES (1), (3), (NULL)
)
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id
);
| customer_id |
|---|
| 2 |
Worked answer (Q17): three tables without fan-out. An order with two items and two payments becomes four rows when you join all three tables, and every sum doubles. Bring each one-to-many table down to one row per order before joining:
WITH orders(order_id, customer_id) AS (
VALUES (1, 100), (2, 200)
),
items(order_id, price) AS (
VALUES (1, 10), (1, 15), (2, 40)
),
payments(order_id, paid) AS (
VALUES (1, 20), (1, 5), (2, 40)
),
item_totals AS (
SELECT order_id, SUM(price) AS item_total
FROM items
GROUP BY order_id
),
payment_totals AS (
SELECT order_id, SUM(paid) AS paid_total
FROM payments
GROUP BY order_id
)
SELECT o.order_id, i.item_total, p.paid_total
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
ORDER BY o.order_id;
| order_id | item_total | paid_total |
|---|---|---|
| 1 | 25 | 25 |
| 2 | 40 | 40 |
Joining the raw tables instead gives order 1 an item total and a paid total of 50 each. In the interview, state the grain of each table before joining ("items: one row per line item") and check the row count after every join.
Worked answer (Q18): latest order date per product. MAX per product answers it, and a LEFT JOIN keeps products that never sold:
WITH products(product_id, name) AS (
VALUES (1, 'Lamp'), (2, 'Desk'), (3, 'Chair')
),
orders(order_id, product_id, order_date) AS (
VALUES (10, 1, DATE '2026-02-01'),
(11, 1, DATE '2026-03-15'),
(12, 2, DATE '2026-01-20')
)
SELECT
p.product_id,
p.name,
MAX(o.order_date) AS last_order_date
FROM products AS p
LEFT JOIN orders AS o
ON o.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY p.product_id;
| product_id | name | last_order_date |
|---|---|---|
| 1 | Lamp | 2026-03-15 |
| 2 | Desk | 2026-01-20 |
| 3 | Chair | NULL |
If the question also wants columns from that latest order, such as its id or amount, MAX can't carry them. Number each product's orders with ROW_NUMBER and keep row 1, as in Q37.
Questions 19–28: Aggregation and CASE
Basic GROUP BY is assumed. What actually gets tested is HAVING, conditional aggregation, and whether you understand the grain of the table you are producing.
- Find the top 5 customers by total order value
- Count unique products ordered per month
- Departments with more than 10 staff and average salary above 100k (
HAVING) - Conditional counts: how many completed vs refunded orders per region
- Pivot rows into columns without a PIVOT clause
- Find the mode (most frequent value) of a column
- Calculate the median without a built-in median function
- Average order value excluding the top 1% as outliers
- Group by multiple dimensions: category, region, and month at once
- Find users who took at least 3 actions in a single day
Worked answer (Q19): top 5 customers by order value. Aggregate to one row per customer, then sort and cut:
SELECT customer_id, SUM(amount) AS total_value
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY total_value DESC, customer_id
LIMIT 5;
Ask two things first. Do refunded or cancelled orders count? Here only completed ones do. And what should happen when two customers tie for fifth place? LIMIT keeps one of them. To keep both, compute RANK() OVER (ORDER BY SUM(amount) DESC) in a subquery and keep rank 5 or better in the outer query.
Worked answer (Q20): unique products per month.
SELECT
date_trunc('month', order_date)::date AS month_start,
COUNT(DISTINCT product_id) AS unique_products
FROM orders
GROUP BY date_trunc('month', order_date)
ORDER BY month_start;
COUNT(*) would count order lines, so a product bought ten times would count ten times. COUNT(DISTINCT product_id) counts each product once per month. A month with no orders produces no row at all. If the report needs every month, join to a calendar first; the SQL CTE guide builds one with a recursive date spine.
Worked answer (Q21): filtering groups. WHERE filters rows before grouping; HAVING filters groups after aggregation Practice this question. Mixing them up is the single most common conceptual miss:
SELECT
department_id,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10
AND AVG(salary) > 100000;
Worked answer (Q22–Q23): conditional aggregation, the pivot in disguise. Wrap a CASE inside an aggregate and rows become columns with no special syntax:
WITH orders(region, status, amount) AS (
VALUES ('NA', 'completed', 100), ('NA', 'refunded', 40),
('EU', 'completed', 80), ('EU', 'completed', 20)
)
SELECT
region,
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_revenue,
COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders
FROM orders
GROUP BY region
ORDER BY region;
| region | completed_revenue | refunded_revenue | completed_orders |
|---|---|---|---|
| EU | 100 | 0 | 2 |
| NA | 100 | 40 | 1 |
COUNT(*) FILTER (WHERE ...) is the cleaner Postgres form; fall back to SUM(CASE WHEN ... THEN 1 ELSE 0 END) in dialects without FILTER. Conditional counts, ratio-of-counts, and the ELSE-clause traps that follow are worked through in SQL CASE WHEN: Syntax, Conditional Counts, and the Interview Patterns That Matter.
Worked answer (Q24): the mode. Count each value, then keep the highest count. RANK keeps every value tied for first:
WITH purchases(category) AS (
VALUES ('books'), ('toys'), ('books'), ('games'), ('toys')
)
SELECT category, freq
FROM (
SELECT
category,
COUNT(*) AS freq,
RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
FROM purchases
GROUP BY category
) AS counted
WHERE rnk = 1
ORDER BY category;
| category | freq |
|---|---|
| books | 2 |
| toys | 2 |
PostgreSQL also has mode() WITHIN GROUP (ORDER BY category), which returns a single value and picks one arbitrarily when there is a tie. Say which behavior you want before using it.
Worked answer (Q25): median. In PostgreSQL the direct route is an ordered-set aggregate:
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;
If the interviewer bans percentile functions (they often do, to test window fluency), number the rows from both ends Practice this question and keep the middle: rows where ROW_NUMBER ascending and descending differ by at most 1, averaged. Sketch that verbally before writing it; the idea matters more than the exact code.
Worked answer (Q26): average excluding the top 1%. Find the 99th-percentile cutoff, then average the orders at or below it:
WITH cutoff AS (
SELECT percentile_cont(0.99) WITHIN GROUP (ORDER BY amount) AS p99
FROM orders
)
SELECT AVG(o.amount) AS trimmed_avg_order_value
FROM orders AS o
CROSS JOIN cutoff AS c
WHERE o.amount <= c.p99;
Agree on the definition first. This version drops orders above the 99th percentile of value. Dropping "the largest 1% of orders" by count is a different cut: number the orders with NTILE(100) OVER (ORDER BY amount DESC) and drop bucket 1. It is also worth asking whether the outliers are errors to remove or real large orders that deserve their own metric.
Worked answer (Q27): several dimensions at once. List every dimension in GROUP BY. Each output row is one combination that exists in the data:
WITH sales(category, region, sale_date, amount) AS (
VALUES ('books', 'NA', DATE '2026-01-10', 20),
('books', 'NA', DATE '2026-01-22', 30),
('books', 'EU', DATE '2026-01-05', 15),
('toys', 'NA', DATE '2026-02-03', 40)
)
SELECT
category,
region,
date_trunc('month', sale_date)::date AS month_start,
SUM(amount) AS revenue
FROM sales
GROUP BY category, region, date_trunc('month', sale_date)
ORDER BY category, region, month_start;
| category | region | month_start | revenue |
|---|---|---|---|
| books | EU | 2026-01-01 | 15 |
| books | NA | 2026-01-01 | 50 |
| toys | NA | 2026-02-01 | 40 |
Combinations with no sales, such as toys in the EU, do not appear. If the interviewer also wants subtotals and a grand total in the same result, write GROUP BY ROLLUP (category, region, date_trunc('month', sale_date)). It adds a subtotal row at each level plus one grand-total row, with NULL in the columns that were rolled up.
Worked answer (Q28): threshold per group. The grain is (user, day), so group by exactly that:
SELECT user_id, event_date, COUNT(*) AS actions
FROM events
GROUP BY user_id, event_date
HAVING COUNT(*) >= 3;
Saying "the output grain is one row per user per qualifying day" out loud is worth more than the query itself. Fan-out bugs and double-counting almost always trace back to an unexamined grain.
Questions 29–40: Window functions
Window functions separate junior from senior candidates more reliably than any other topic. They compute a per-row value relative to a group without collapsing rows, which is exactly what rankings, comparisons to previous periods, and dedup all require. If this section feels shaky, the window functions guide builds it from the ground up.
- Rank employees by salary within their department
- Explain
ROW_NUMBERvsRANKvsDENSE_RANK(ties!) - Find the top 3 products by revenue in each category
- Calculate a running total of sales by date
- Calculate the 7-day moving average of daily active users
- Month-over-month revenue growth using
LAG - Difference between each row and the previous row
- First and last order per customer (
FIRST_VALUE/LAST_VALUE) - Deduplicate: keep only the most recent row per key
- Sessionize: group events separated by 30 minutes or less
- Compute 7-day retention after signup
- Cumulative percentage of total sales
Worked answer (Q29): rank within each department. PARTITION BY restarts the numbering for every department:
WITH employees(name, dept, salary) AS (
VALUES ('Ana', 'Eng', 190), ('Ben', 'Eng', 140), ('Cy', 'Eng', 140),
('Dee', 'Sales', 120), ('Eve', 'Sales', 95)
)
SELECT
dept,
name,
salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
FROM employees
ORDER BY dept, salary_rank, name;
| dept | name | salary | salary_rank |
|---|---|---|---|
| Eng | Ana | 190 | 1 |
| Eng | Ben | 140 | 2 |
| Eng | Cy | 140 | 2 |
| Sales | Dee | 120 | 1 |
| Sales | Eve | 95 | 2 |
Ben and Cy tie, so they share rank 2. Which ranking function to use when ties exist is Q30.
Worked answer (Q30): the three ranking functions. They look interchangeable until ties appear, and interviewers plant ties to force the choice:
WITH employees(name, salary) AS (
VALUES ('Dana', 300), ('Eli', 200), ('Fay', 200), ('Gus', 100)
)
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
| name | salary | row_num | rnk | dense_rnk |
|---|---|---|---|---|
| Dana | 300 | 1 | 1 | 1 |
| Eli | 200 | 2 | 2 | 2 |
| Fay | 200 | 3 | 2 | 2 |
| Gus | 100 | 4 | 4 | 3 |
Note the tiebreaker (, name) inside ROW_NUMBER's ordering: without it, Eli and Fay could swap between runs, and a nondeterministic "latest row" is a real production bug, not a style nit.

| Function | Ties get | Sequence after a tie | Use when |
|---|---|---|---|
ROW_NUMBER() | Distinct numbers | Continues 1,2,3,4 | You need exactly one row per group (dedupe, "latest") |
RANK() | Same number | Skips (1,2,2,4) | Leaderboard where gaps are meaningful |
DENSE_RANK() | Same number | No gaps (1,2,2,3) | "Nth highest distinct value" regardless of how many rows tie |
That last row also answers the classic "second highest salary" question: DENSE_RANK() = 2 handles duplicated top salaries where ROW_NUMBER would not.
Worked answer (Q31): top N per group. Number rows inside each partition Practice this question, then filter in an outer query. You cannot filter on a window function in WHERE because WHERE runs before windows are computed:
SELECT product_id, category, revenue
FROM (
SELECT
product_id,
category,
revenue,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY revenue DESC
) AS rn
FROM product_revenue
) AS ranked
WHERE rn <= 3;
Asked at Meta — Find the most-used app You work with Oculus engagement data: a
user_activitytable of sessions with durations, joined to anappslookup. For the trailing 7 days from a given as-of date, you must identify the "most used app" — and defend your definition, since total duration, distinct users, and session count can each crown a different winner. Follow-ups probe how your query behaves under ties and whether your date window is inclusive on both ends.
That Meta question is this exact top-N pattern plus a definitional negotiation, which is the realistic version: in production interviews, half the battle is pinning down what "most used" means before you write a line.
Worked answer (Q32): running total. An ORDER BY inside OVER turns a plain aggregate into a cumulative one:
WITH sales(sale_date, amount) AS (
VALUES (DATE '2026-01-01', 100),
(DATE '2026-01-02', 50),
(DATE '2026-01-03', 75)
)
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;
| sale_date | amount | running_total |
|---|---|---|
| 2026-01-01 | 100 | 100 |
| 2026-01-02 | 50 | 150 |
| 2026-01-03 | 75 | 225 |
Asked at DoorDash — Compute Fitness App DAU Given a
userstable (with a test-user flag and timezone) and anapp_eventslog, define DAU as distinct non-test users with at least one event per UTC calendar day, and return one row per day for the last 30 days. The traps are the days with zero events — which must still appear as rows — and excluding test users without accidentally dropping legitimate ones. It reads like a one-liner and is actually a calendar-spine problem.
Worked answer (Q33): moving average. Once you have a daily series like that DAU output, the 7-day moving average is a frame clause:
SELECT
activity_date,
dau,
AVG(dau) OVER (
ORDER BY activity_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS dau_7d_avg
FROM daily_active_users
ORDER BY activity_date;
Say the caveat before the interviewer does: ROWS BETWEEN 6 PRECEDING counts rows, not days. If dates are missing from the table, the "7-day" average silently spans more than 7 calendar days — which is exactly why the DoorDash question forces zero-event days into the series first.
Worked answer (Q34): month-over-month growth. LAG pulls the previous row's value into the current row:
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0)
, 1) AS mom_pct
FROM monthly_revenue
ORDER BY month;
The NULLIF(..., 0) guard matters: a zero-revenue month would otherwise crash the query with a division error. Interviewers notice when you add it unprompted.
Practice question: Compute monthly signups, conversion, and YoY growth (Intuit · Data Scientist)
Worked answer (Q35): change from the previous row. Subtract LAG of the same column:
WITH readings(reading_date, value) AS (
VALUES (DATE '2026-03-01', 100),
(DATE '2026-03-02', 130),
(DATE '2026-03-03', 115)
)
SELECT
reading_date,
value,
value - LAG(value) OVER (ORDER BY reading_date) AS change_from_previous
FROM readings
ORDER BY reading_date;
| reading_date | value | change_from_previous |
|---|---|---|
| 2026-03-01 | 100 | NULL |
| 2026-03-02 | 130 | 30 |
| 2026-03-03 | 115 | -15 |
The first row has no previous row, so its change is NULL. Leave it NULL or wrap the expression in COALESCE(..., 0), and say which. When the table holds many series, one per user or store, add PARTITION BY so a series never takes its previous value from another one.
Worked answer (Q36): the LAST_VALUE trap. FIRST_VALUE works as expected, but LAST_VALUE with a default frame returns the last peer of the current row — the current row itself whenever the ORDER BY values are unique — because the default RANGE frame ends at CURRENT ROW. You must widen it explicitly:
SELECT DISTINCT
customer_id,
FIRST_VALUE(order_id) OVER w AS first_order,
LAST_VALUE(order_id) OVER (
PARTITION BY customer_id ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_order
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);
Knowing this trap cold is one of the fastest ways to signal real window-function experience, because everyone who has used LAST_VALUE in production has been burned by it once.
Worked answer (Q38): sessionization. Flag rows that start a new session, then running-sum the flags into session ids:
Practice question: Detect sessions and gaps using SQL LEAD (Apple · Data Scientist)
WITH flagged AS (
SELECT
user_id,
event_ts,
CASE
WHEN event_ts - LAG(event_ts) OVER (
PARTITION BY user_id ORDER BY event_ts
) <= INTERVAL '30 minutes'
THEN 0
ELSE 1
END AS new_session
FROM events
)
SELECT
user_id,
event_ts,
SUM(new_session) OVER (
PARTITION BY user_id ORDER BY event_ts
) AS session_id
FROM flagged;
The ELSE 1 branch also catches each user's first event, where LAG returns NULL and the comparison is unknown. That is a rare case where SQL's three-valued logic works in your favor — but only if you put the "same session" test in the WHEN and let everything else fall through.
Worked answer (Q39): 7-day retention after signup. Define it before writing anything. Here a user counts as retained if they were active on any of the 7 days after their signup day. Flag each user, then add up the flags per signup cohort:
WITH users(user_id, signup_date) AS (
VALUES (1, DATE '2026-03-01'), (2, DATE '2026-03-01'),
(3, DATE '2026-03-02'), (4, DATE '2026-03-02'),
(5, DATE '2026-03-02')
),
activity(user_id, activity_date) AS (
VALUES (1, DATE '2026-03-01'), (1, DATE '2026-03-05'),
(2, DATE '2026-03-01'),
(3, DATE '2026-03-09'),
(4, DATE '2026-03-12'),
(5, DATE '2026-03-03')
),
flags AS (
SELECT
u.user_id,
u.signup_date,
EXISTS (
SELECT 1
FROM activity AS a
WHERE a.user_id = u.user_id
AND a.activity_date > u.signup_date
AND a.activity_date <= u.signup_date + 7
) AS retained
FROM users AS u
)
SELECT
signup_date,
COUNT(*) AS signups,
SUM(CASE WHEN retained THEN 1 ELSE 0 END) AS retained_users,
ROUND(100.0 * SUM(CASE WHEN retained THEN 1 ELSE 0 END) / COUNT(*), 1) AS retention_pct
FROM flags
GROUP BY signup_date
ORDER BY signup_date;
| signup_date | signups | retained_users | retention_pct |
|---|---|---|---|
| 2026-03-01 | 2 | 1 | 50.0 |
| 2026-03-02 | 3 | 2 | 66.7 |
Activity on the signup day itself doesn't count, so user 2 is not retained. User 4 isn't either: they came back on day 10, outside the window. Leave out cohorts that signed up less than 7 days ago: their window isn't over yet, so their retention looks lower than it will end up. Some teams define D7 retention as being active on exactly day 7, so ask which one the interviewer means.
Worked answer (Q40): cumulative percentage of total. Divide the running total from Q32 by the grand total. An empty OVER () is a window over the whole result set:
WITH sales(sale_date, amount) AS (
VALUES (DATE '2026-01-01', 100),
(DATE '2026-01-02', 50),
(DATE '2026-01-03', 50)
)
SELECT
sale_date,
amount,
ROUND(
100.0 * SUM(amount) OVER (ORDER BY sale_date)
/ SUM(amount) OVER ()
, 1) AS cumulative_pct
FROM sales
ORDER BY sale_date;
| sale_date | amount | cumulative_pct |
|---|---|---|
| 2026-01-01 | 100 | 50.0 |
| 2026-01-02 | 50 | 75.0 |
| 2026-01-03 | 50 | 100.0 |
The 100.0 makes the division decimal. With integer columns, SUM(amount) OVER (ORDER BY sale_date) / SUM(amount) OVER () is integer division in PostgreSQL and returns 0 on every row but the last.
Questions 41–50: CTEs, DML, and optimization
Data engineering screens hit this section hard; analyst and data science screens sample from it. Expect at least one conceptual "explain the difference" question here alongside the coding.
- Rewrite a nest of subqueries as chained CTEs
- Recursive CTE: build an employee hierarchy from
manager_id - Longest streak of consecutive login days (gaps and islands)
- Build a funnel: signup, activation, first purchase
UPDATEa table from another table; write an UPSERT- Delete duplicate rows while keeping one copy
- Explain
DELETEvsTRUNCATEvsDROP - When does an index help, and when does it hurt?
- Debug and optimize a slow or wrong query someone else wrote
- Explain the logical execution order of a query
Worked answer (Q41): nested subqueries to chained CTEs. The nested version has to be read from the inside out:
SELECT customer_id, total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM (
SELECT customer_id, amount
FROM orders
WHERE status = 'completed'
) AS completed
GROUP BY customer_id
) AS totals
WHERE total > 100;
As chained CTEs, the same logic reads top to bottom, one named step at a time:
WITH completed AS (
SELECT customer_id, amount
FROM orders
WHERE status = 'completed'
),
totals AS (
SELECT customer_id, SUM(amount) AS total
FROM completed
GROUP BY customer_id
)
SELECT customer_id, total
FROM totals
WHERE total > 100;
Both return the same rows. Name each CTE after what one of its rows represents, and you can check any step by pointing the final SELECT at it. The SQL CTE guide compares CTEs and subqueries side by side.
Worked answer (Q37/Q46 pattern): dedupe, keep the latest. This is the single most reused CTE-plus-window pattern in interviews, and it answers both the read version (Q37) and the delete version (Q46):
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC
) AS rn
FROM users
)
SELECT *
FROM ranked
WHERE rn = 1;
For the destructive version, delete where rn > 1 using the table's primary key. Mention that you would run the SELECT first and check counts — interviewers score the safety habit.
Worked answer (Q42): recursive hierarchy. An anchor selects the roots; the recursive member repeatedly joins children onto what has been found so far:
WITH RECURSIVE org AS (
-- anchor: top-level managers
SELECT employee_id, manager_id, name, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- recursive member: everyone reporting up the chain
SELECT e.employee_id, e.manager_id, e.name, org.depth + 1
FROM employees AS e
JOIN org ON e.manager_id = org.employee_id
)
SELECT employee_id, name, depth
FROM org
ORDER BY depth, employee_id;
Worked answer (Q43): gaps and islands. Consecutive dates minus a consecutive row number is constant within a streak. That constant becomes the group key:
Practice question: Determine Maximum Consecutive Order Days Per User (Netflix · Data Scientist)
WITH distinct_days AS (
SELECT DISTINCT user_id, login_date
FROM logins
),
grouped AS (
SELECT
user_id,
login_date,
login_date - (ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
))::int AS streak_key
FROM distinct_days
)
SELECT
user_id,
COUNT(*) AS streak_length,
MIN(login_date) AS streak_start
FROM grouped
GROUP BY user_id, streak_key
ORDER BY streak_length DESC;
The DISTINCT step is not optional. Two logins on the same day would otherwise increment the row number without advancing the date and split a real streak in two.
Asked at OpenAI — Write SQL for repeat churn You are given an
experiment_userstable assigning users to a control or free-month variant, plus asubscription_eventslog, and asked to measure how the promotion changed retention and churn. Eligibility is already handled upstream — part of the test is whether you resist re-deriving it. Ordering each user's event history with window functions and comparing outcomes per variant does most of the work.
That question is the production shape of Q39 and Q44 combined: cohort the users, walk each one's event sequence in order, then aggregate per group. If you can chain those three steps as named CTEs, you can solve most experiment-analysis SQL on sight.
Worked answer (Q44): a signup, activation, purchase funnel. Take each user's first time at each step, then count the users who reached every step in order:
WITH events(user_id, event_name, event_ts) AS (
VALUES (1, 'signup', TIMESTAMP '2026-03-01 09:00'),
(1, 'activate', TIMESTAMP '2026-03-01 09:20'),
(1, 'purchase', TIMESTAMP '2026-03-02 10:00'),
(2, 'signup', TIMESTAMP '2026-03-01 11:00'),
(2, 'activate', TIMESTAMP '2026-03-03 08:00'),
(3, 'signup', TIMESTAMP '2026-03-02 12:00'),
(4, 'purchase', TIMESTAMP '2026-03-02 13:00')
),
firsts AS (
SELECT
user_id,
MIN(CASE WHEN event_name = 'signup' THEN event_ts END) AS signup_ts,
MIN(CASE WHEN event_name = 'activate' THEN event_ts END) AS activate_ts,
MIN(CASE WHEN event_name = 'purchase' THEN event_ts END) AS purchase_ts
FROM events
GROUP BY user_id
)
SELECT
COUNT(signup_ts) AS signed_up,
SUM(CASE WHEN activate_ts >= signup_ts THEN 1 ELSE 0 END) AS activated,
SUM(CASE WHEN purchase_ts >= activate_ts
AND activate_ts >= signup_ts THEN 1 ELSE 0 END) AS purchased
FROM firsts;
| signed_up | activated | purchased |
|---|---|---|
| 3 | 2 | 1 |
User 4 has a purchase but no signup event. Comparisons against a NULL signup_ts are unknown, so the CASE returns 0 for that user. Whether such users should count is a question for the interviewer, and so is a time limit between steps (activated within 7 days of signup, for example). Divide each step by the one before it to get step conversion.
Worked answer (Q45): UPDATE from another table, and UPSERT. PostgreSQL's UPDATE ... FROM joins during the update; INSERT ... ON CONFLICT handles the upsert case:
UPDATE employees AS e
SET salary = a.new_salary
FROM salary_adjustments AS a
WHERE a.employee_id = e.employee_id;
INSERT INTO daily_metrics (metric_date, dau)
VALUES (DATE '2026-01-03', 4210)
ON CONFLICT (metric_date)
DO UPDATE SET dau = EXCLUDED.dau;
Worth saying in the room: MERGE is the standard spelling (and exists in PostgreSQL 15+), but every warehouse dialect has its own upsert idiom, so name the engine before writing one.
Worked answer (Q47): DELETE vs TRUNCATE vs DROP. Conceptual, but precise wording matters:
| Command | Removes | Keeps table structure? | Logged per row? | Can filter with WHERE? |
|---|---|---|---|---|
DELETE | Selected rows | Yes | Yes (slower, rollback-friendly) | Yes |
TRUNCATE | All rows | Yes | Minimal (fast; resets sequences in MySQL/SQL Server, in Postgres only with RESTART IDENTITY) | No |
DROP | The whole table | No | n/a | No |
Asked at TikTok — Debug a Hive query The interviewer hands you a prewritten Hive query that returns wrong numbers and asks you to find the logic errors: join conditions, filters, aggregation grain, window definitions, partition predicates. Then you fix it, design test cases to prove the fix, and propose performance improvements. Nothing here is exotic — the skill being tested is a systematic read of someone else's SQL.
Asked at TikTok — Debug a Hive insert query The write-path variant: given a target table schema, an incoming table, and an
INSERT/SELECTthat fails, explain why. The checklist runs through column alignment, type casts, partition columns and dynamic-partition settings, file formats, and reserved keywords. Data engineering screens use this to check that you know how tables are written, not just read.
Both TikTok questions reward the same habit: verify the grain and row count at every stage instead of staring at the final output. When debugging under time pressure, comment out everything after the first join, check the count, and work outward.
Worked answer (Q48): when an index helps, and when it hurts. An index pays off when a query reads a small share of a large table through the indexed columns: a selective WHERE filter, a join key, or an ORDER BY ... LIMIT that follows the index order. In a composite index, put the columns compared with = first and the range column last:
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);
SELECT order_id, amount
FROM orders
WHERE customer_id = 42
AND order_date >= DATE '2026-01-01'
AND order_date < DATE '2026-02-01';
Indexes cost something on the write side: each insert adds entries to the table's indexes, and an update to an indexed column changes them too. A filter that matches a large share of the table, such as a status column with three values, is often cheaper to answer with a full scan, and the planner then skips the index. Wrapping the column in a function, as in WHERE EXTRACT(MONTH FROM order_date) = 1, stops a plain index on order_date from being used unless you build an index on that exact expression. Run EXPLAIN ANALYZE before and after to show the plan changed.
Worked answer (Q49): debugging someone else's query. Read it in execution order and check the row count after each step, starting from FROM. One bug to look for early is a filter on the right table placed in WHERE after a LEFT JOIN:
-- Meant: every user with their number of 2026 orders, including 0
SELECT u.user_id, COUNT(o.order_id) AS orders_2026
FROM users AS u
LEFT JOIN orders AS o
ON o.user_id = u.user_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY u.user_id;
Users with no 2026 orders vanish instead of showing 0. Their joined rows carry either an older order_date or, when they have no orders at all, a NULL one, and the WHERE drops both, so the query behaves like an inner join. Move the filter into the join condition:
SELECT u.user_id, COUNT(o.order_id) AS orders_2026
FROM users AS u
LEFT JOIN orders AS o
ON o.user_id = u.user_id
AND o.order_date >= DATE '2026-01-01'
GROUP BY u.user_id;
For the "optimize" half, run EXPLAIN ANALYZE and look for the step that takes the most time, or where the planner's row estimate is far from the actual count. That step is where an index, an earlier filter, or a pre-aggregation helps.
Worked answer (Q50): logical execution order. SQL evaluates clauses in a fixed order that is not the order you write them: FROM (and joins), then WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT.

This one fact resolves a whole cluster of interview gotchas: you cannot reference a SELECT alias in WHERE (it does not exist yet), you can in ORDER BY (it does), and you cannot filter on a window function anywhere except an outer query, because windows are computed after HAVING.
Common mistakes that fail SQL screens
| Do | Don't |
|---|---|
| State your assumptions about the schema out loud | Silently assume column names and join keys |
Use LEFT JOIN ... IS NULL or NOT EXISTS for "who is missing" | Reach for NOT IN with a nullable subquery (returns nothing if any value is NULL) |
| Name CTEs after what they produce | Nest five subqueries no one can read |
Guard division with NULLIF(denominator, 0) | Let a zero denominator crash the query |
| Verify row counts after each join | Assume the join is one-to-one and ship a fan-out |
Pick ROW_NUMBER/RANK/DENSE_RANK on purpose | Default to RANK and get surprised by gaps |
Add a tiebreaker to every ORDER BY inside ROW_NUMBER | Ship a nondeterministic "latest row" |
Practice these on PracHub
Reading answers builds recognition; writing them under pressure builds recall. These questions from our SQL question bank map directly onto the sections above, and many run in an in-browser SQL editor against real PostgreSQL, so you get the same "wrong row count" feedback you would get in the screen itself.
- Solve window-function SQL without joins — forces every join-shaped instinct through
PARTITION BYinstead; the fastest way to find window-function gaps. - Query conversion and retention with SQL windows — the Q39 retention pattern on a real schema with users and events.
- Write SQL to analyze response accuracy and speed — multi-table aggregation where getting the grain right is the whole problem.
- Build SQL pivot with lookups and currency conversion — conditional aggregation plus an as-of join for FX rates; Q23 at production difficulty.
- Diagnose zero IE uploads with SQL — the debugging-with-SQL skill the TikTok questions test, framed as a metrics investigation.
- Manipulate data efficiently in Python — the pandas mirror of these patterns, for interviews that let you choose your tool.
The bank goes well beyond these six: it holds SQL questions from interviews at Google, Meta, and Amazon, filterable by company and difficulty. If you are targeting an analytics-heavy role, pair this list with the Data Scientist question set and the broader interview guides for the case and behavioral rounds.
If you want structured drilling beyond the bank, we have compared the main platforms head-to-head in LeetCode SQL vs DataLemur and reviewed StrataScratch's free tier.
How to turn this list into a prep plan
Do not treat this as passive reading. Convert it into a loop: learn one pattern, close the page, re-derive the worked answer on a blank editor, then solve one bank question that uses it.
| Prep area | What you need to prove | Practice artifact |
|---|---|---|
| Pattern matching | Name the family before you type | One sentence: "this is a top-N-per-group problem" |
| Join correctness | Right join type, right keys, no fan-out | A query with a row-count check after each join |
| Window fluency | Partition, order, and frame chosen on purpose | A ROW_NUMBER dedupe and a LAG comparison from memory |
| NULL discipline | Unknowns handled explicitly | NULLIF guards and IS DISTINCT FROM used without prompting |
| Communication | Turn a result into a recommendation | One concise business interpretation of the output |
A week per section is a comfortable pace from a cold start; candidates who already write SQL daily usually need only the window-function and gaps-and-islands sections plus timed practice. Either way, finish with two full mock screens where you talk while you type. The talking is the part nobody practices.
FAQ
What are the most common SQL interview questions?
Joins with a NULL check ("customers who never ordered"), top-N-per-group with ROW_NUMBER, running totals and moving averages, month-over-month comparisons with LAG, dedup keeping the latest row, and WHERE vs HAVING. Joins and window functions appear in nearly every screen, so prioritize sections two and four of this list.
What SQL interview questions do freshers get?
Freshers get the first three families on this list, roughly Q1–28: filtering and NULL logic, joins and set operations, and aggregation with GROUP BY and CASE. Typical prompts are "find customers who never ordered", "total spend per user", "departments with more than 10 staff", and "explain WHERE vs HAVING". Window functions turn up occasionally as a stretch question, but entry-level screens weight correct NULL handling and clear reasoning far more than advanced syntax.
How long does it take to prepare for a SQL interview?
From a cold start, most people get screen-ready in three to five weeks by drilling one section of this list per week: basics, joins, aggregation, window functions, then CTEs and DML. If you already write SQL at work, one to two weeks focused on window functions and timed practice is usually enough. Practicing on a live SQL editor that reports row counts beats reading answers by a wide margin.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Use WHERE to drop rows you never want counted and HAVING for conditions on aggregates like COUNT(*) > 10. Because WHERE runs first, it is also the cheaper place to filter whenever the condition does not need an aggregate.
What SQL interview questions do experienced candidates get?
Experienced screens start where the basics end, around Q29–50: window functions (ranking with ties, LAG comparisons, running totals, frame clauses), gaps-and-islands problems such as the longest login streak, sessionization, cohort and retention analysis built from chained CTEs, and debugging or optimizing a query someone else wrote. Follow-ups ask why you chose an approach and how it behaves at scale, which counts for more than getting the first draft right.
Do data science interviews test SQL or Python?
Usually both, in separate rounds: SQL for the analytics screen, Python for the coding round, and either one for take-homes. The patterns transfer — a GROUP BY is a pandas groupby, a window function is a transform — so drilling one sharpens the other. When you get to choose the tool, Python vs SQL in Data Science Interviews breaks down which to pick per question type.
Comments (0)