The Two Tables to Practise Against
Every query below runs against the same schema, so set it up once and you can test all 25. The customers table has four columns: customer_id (integer, primary key), customer_name (text), region (text, values like 'West', 'East', 'Central'), and signup_date (date). The orders table has five columns: order_id (integer, primary key), customer_id (integer, foreign key to customers), order_date (date), status (text, values 'completed', 'shipped', 'cancelled', 'returned'), and total_amount (numeric). The customers table holds about 12,000 rows and the orders table about 240,000, which matters for the performance questions later. When a hiring manager asks this, they are usually listening for how you frame trade-offs rather than a textbook definition — one candidate told me, "I always check the row count before and after a join, because a fan-out is the fastest way to double your revenue numbers by accident." That single habit, stated plainly, lands better than reciting syntax.
Two details will decide half your answers. First, some customers have never ordered, so an inner join between the two tables silently drops them; questions 8 and 9 exist specifically to catch that. Second, the status column means you must decide whether cancelled and returned orders count toward revenue. Interviewers rarely specify this, and the correct move is to ask before you write. Stating 'I'll exclude cancelled and returned orders and flag if you want them included' is worth more than a cleverer query. If your JOIN syntax is rusty, the SQL joins explained walkthrough covers the mechanics with the same table shapes.
Create both tables and insert at least five customers, one of whom has no orders, and roughly ten orders spread across three months with at least one cancelled and one returned row. A small dataset you fully understand beats a realistic one you cannot verify by hand. When a query returns an unexpected number, you need to be able to count the correct answer manually.
For each question, say out loud what the output columns should be and roughly how many rows. A question like 'top 3 customers by revenue' should produce exactly 3 rows with two columns. This habit catches the join fan-out bug, where joining before aggregating multiplies your totals and you never notice because the query still runs.
Tier 1: SELECT, WHERE, and Filtering (Questions 1-5)
These look easy and they still eliminate people, because the interviewer is watching your filtering logic rather than your syntax. NULL handling, date ranges, and the difference between WHERE and HAVING account for most of the losses here.
SELECT customer_id, customer_name, signup_date
FROM customers
WHERE region = 'West'
ORDER BY signup_date DESC;Use single quotes for strings, and never use double quotes, because some engines read double quotes as an identifier and the query will error. What the interviewer is listening for: that you can write a WHERE clause without reaching for a cheat sheet. A common mistake is writing region = West without quotes, which throws a column-not-found error in Postgres and silently fails in other engines.
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days';The trap is using BETWEEN with two hardcoded dates. The interviewer wants to see relative date arithmetic, because in production nobody will tell you the dates. What they listen for: whether you know CURRENT_DATE, and whether you hint that the exact syntax varies by engine. Say out loud that you would check whether the warehouse uses DATEADD or INTERVAL before shipping it.
SELECT order_id, customer_id, status, total_amount
FROM orders
WHERE total_amount > 500 AND status <> 'cancelled'
ORDER BY total_amount DESC;The interesting part is status <> 'cancelled'. That keeps rows where status is 'shipped', 'completed', and 'returned', which is usually not what the business means by a live order. Walk through that reasoning out loud and ask whether returned orders should also be excluded. This will break if status contains NULL, because NULL <> 'cancelled' evaluates to unknown and the row disappears.
SELECT region, COUNT(*) AS customer_count
FROM customers
GROUP BY region
ORDER BY customer_count DESC;Alias the aggregate so the output reads well. What they listen for: whether you know that every non-aggregated column in the SELECT must appear in GROUP BY. Dropping region from the GROUP BY is the single most common error at this level, and the error message from the engine says so directly.
SELECT region, COUNT(*) AS customer_count
FROM customers
GROUP BY region
HAVING COUNT(*) > 2000
ORDER BY customer_count DESC;The question exists to test WHERE versus HAVING. WHERE filters rows before grouping, HAVING filters groups after aggregation, and count-based conditions must use HAVING. What they listen for: that you can state that sentence, not just produce the query. Candidates who write WHERE COUNT(*) > 2000 have memorised the pattern without understanding it, and the follow-up question exposes that.
Tier 2: JOINs and Set Logic (Questions 6-12)
Join questions dominate analyst screens. The interviewer is not testing whether you can type LEFT JOIN; they are testing whether you know what happens to rows that have no match, and whether you notice when a join changes your grain. Keep the orders-to-customers direction straight: orders has many rows per customer, customers has one.
SELECT o.order_id, o.order_date, c.customer_name, c.region
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id;Alias your tables and prefix every column. It costs nothing and it saves you when a later join brings in a second customer_id. INNER JOIN returns only orders that have a matching customer, which is correct here because a foreign key should always match.
SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY order_count ASC;The critical detail is COUNT(o.order_id), not COUNT(*). COUNT(*) counts the unmatched row produced by the LEFT JOIN and returns 1 for a customer with no orders, which is wrong and is exactly the bug this question is designed to catch. What they listen for: whether you explain that choice unprompted. It is the fastest senior signal in the whole set.
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;The anti-join pattern. After a LEFT JOIN, unmatched rows have NULL in every column from the right table, so filtering on that NULL isolates them. What they listen for: that you know the predicate must go in WHERE and not in the ON clause, because moving it to ON undoes the outer join and turns the query into an inner join with zero rows.
SELECT c.customer_id, c.customer_name, COALESCE(SUM(o.total_amount), 0) AS revenue
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id AND o.status = 'completed'
GROUP BY c.customer_id, c.customer_name
ORDER BY revenue DESC;Two things matter here. The status filter belongs in the ON clause because a WHERE filter on the right table would remove the non-ordering customers again. And COALESCE turns the NULL total into 0. Note that SUM already returns NULL for no rows, while COUNT returns 0, so only the sum needs the wrapper.
SELECT o.order_id,
o.customer_id, o.order_date, MAX(o2.order_date) AS previous_order_date
FROM orders o
LEFT JOIN orders o2
ON o2.customer_id = o.customer_id AND o2.order_date < o.order_date
GROUP BY o.order_id, o.customer_id, o.order_date
ORDER BY o.customer_id, o.order_date;A self join. The second alias o2 supplies the earlier order and MAX picks the most recent one among the matches. Explain that this can also be solved with LAG, and that on a large table the window function version will be faster because it avoids the join.
SELECT DATE_TRUNC('month',
order_date) AS order_month,
COUNT(*) FILTER (
WHERE status = 'completed') AS completed_orders,
COUNT(*) FILTER (
WHERE status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY 1
ORDER BY 1;Conditional aggregation solves a huge share of real analyst questions. GROUP BY 1 is shorthand for the first select expression, which is fine in Postgres and looks careless in front of some interviewers, so I recommend writing the expression out in full during an interview.
SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders j
ON c.customer_id
= j.customer_id
AND j.order_date >= '2026-01-01'
AND j.order_date
< '2026-02-01'
JOIN orders f ON c.customer_id
= f.customer_id
AND f.order_date >= '2026-02-01'
AND f.order_date
< '2026-03-01';Two joins to orders, one per month, and the intersection is implicit because INNER JOIN requires both to exist. Use half-open date ranges instead of dates between month ends, so you never miss a timestamp late on the last day. This is a very common real-world requirement and the interviewer is watching for exactly that date-boundary instinct.
Before you run any join, ask yourself which table has one row per key and which has many. If you join a one-to-many relationship and then sum a column from the many side, you are fine. If you sum a column from the one side — say a customer's lifetime value — the fan-out multiplies it by the number of orders and you get a number that is wrong by a factor of ten and looks plausible. I have seen this ship to a dashboard more than once.
Tier 3: GROUP BY, Aggregates, and NULLs (Questions 13-17)
Aggregation questions are where the difference between a number that is right and a number that is plausible gets exposed. The interviewer is watching three things: whether your WHERE and HAVING sit in the correct clauses, whether you reach for COUNT(DISTINCT) when the question means people rather than rows, and whether you notice when NULLs quietly shrink a denominator. On the orders table, status filters interact with all three, so state your exclusion rule before you write the query.
SELECT c.region, ROUND(AVG(o.total_amount), 2) AS avg_order_value
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.region
ORDER BY avg_order_value DESC;AVG ignores NULLs, which is usually what you want but occasionally is not, because a region with many NULL amounts will still look healthy. What they listen for: whether you mention that AVG and COUNT treat NULL differently, and whether you ask whether returns should be netted off.
SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS lifetime_revenue
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.customer_name
HAVING SUM(o.total_amount) > 5000
ORDER BY lifetime_revenue DESC;HAVING on a sum, with the row-level filter in WHERE. This combination is asked constantly. Write the WHERE first, then the GROUP BY, then the HAVING, and let the clauses stack in that order.
SELECT DATE_TRUNC('month',
order_date) AS order_month, COUNT(DISTINCT customer_id) AS active_customers
FROM orders
WHERE status = 'completed'
GROUP BY 1
ORDER BY 1;COUNT(DISTINCT) versus COUNT is a classic. A single customer with three orders contributes 3 to COUNT and 1 to COUNT(DISTINCT). On the 240,000-row orders table this is also the slowest query in the basic set, which makes it a natural bridge into the performance section.
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'cancelled') / COUNT(*),
2) AS cancelled_pct
FROM orders;Two pitfalls. Multiplying by 100.0 rather than 100 forces decimal arithmetic, because integer division truncates and a real ratio of 4.7 percent becomes 4. And when the denominator comes from a filtered set, the denominator changes with it, so you get a percentage of the wrong base. Watch for that pattern in the answer you are shown.
SELECT COUNT(*) AS all_rows,
COUNT(total_amount) AS non_null_amounts,
COUNT(*) - COUNT(total_amount) AS null_amounts
FROM orders;Say the rule plainly: aggregates except COUNT(*) skip NULLs, NULL compared to anything is unknown rather than false, so WHERE region = NULL returns nothing and you need WHERE region IS NULL. A common mistake is writing != 'cancelled' and assuming NULL rows are included. They are not, and silent row loss is the hardest bug to spot in a report.
Tier 4: Window Functions and CTEs (Questions 18-22)
Window functions separate the mid-level from the strong candidates. The core idea to state early: aggregate functions collapse rows, window functions keep every row and add a value computed over a defined set. If your answer explains that distinction before writing code, the interviewer stops worrying about the rest of the question. The dedicated window functions and CTEs guide has more worked cases if this tier is new.
WITH customer_revenue AS (
SELECT c.customer_id,
c.customer_name,
c.region,
SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.customer_name, c.region)
SELECT customer_id,
customer_name,
region,
revenue, RANK() OVER (PARTITION BY region
ORDER BY revenue DESC) AS region_rank
FROM customer_revenue
ORDER BY region, region_rank;The CTE handles the aggregation and the window function ranks the result, which is cleaner than nesting a window inside an aggregate. Say out loud the difference between ROW_NUMBER, RANK, and DENSE_RANK: with a tie at the top, ROW_NUMBER gives 1 and 2, RANK gives 1 and 1 then 3, and DENSE_RANK gives 1 and 1 then 2.
SELECT o.order_id,
o.customer_id,
o.order_date,
o.total_amount,
SUM(o.total_amount) OVER (PARTITION BY o.customer_id
ORDER BY o.order_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
AS running_total
FROM orders o
WHERE o.status = 'completed'
ORDER BY o.customer_id, o.order_date;The ROWS BETWEEN clause is explicit on purpose. Leaving it out gives the default frame, which is a range frame that includes ties, and two orders on the same date will both show the same running total. That surprise is a favourite follow-up question.
WITH ranked_orders AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date DESC,
o.order_id DESC) AS rn
FROM orders o)
SELECT order_id, customer_id, order_date, total_amount
FROM ranked_orders
WHERE rn = 1;The top-N-per-group pattern. Include order_id as a tiebreaker in the ORDER BY, otherwise two orders on the same date give a non-deterministic winner and the result changes between runs. Interviewers love asking why the tiebreaker is there, and the honest answer is reproducibility.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS order_month,
SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1)
SELECT order_month,
revenue,
LAG(revenue) OVER (
ORDER BY order_month) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (
ORDER BY order_month)) / NULLIF(LAG(revenue) OVER (
ORDER BY order_month), 0),
2) AS mom_pct
FROM monthly
ORDER BY order_month;The NULLIF in the denominator prevents a division-by-zero when a month has no revenue. Without it the query errors or returns a division error on the first real run. Mention the missing first month, where LAG returns NULL, and that you would usually filter it out for a chart.
SELECT o.order_id,
o.customer_id,
o.total_amount,
ROUND(AVG(o.total_amount) OVER (PARTITION BY o.customer_id),
2) AS customer_avg,
o.total_amount - AVG(o.total_amount) OVER (PARTITION BY o.customer_id)
AS diff_from_avg
FROM orders o
WHERE o.status = 'completed'
ORDER BY o.customer_id, o.order_date;A window function using an aggregate over a partition, which is the exact pattern the exam-style question is probing. The row count stays the same, unlike with GROUP BY, and that is the point you should state.
Window functions run after WHERE and GROUP BY, so you cannot filter on a window result in the same query. If you try WHERE rn = 1 with ROW_NUMBER aliased in the select list, the engine rejects it because the alias does not exist yet. Wrap the query in a CTE and filter outside. This will break if you forget it, and it is the single most common error I see in live SQL screens.
Tier 5: Performance, Indexes, and Query Plans (Questions 23-25)
Performance questions carry less weight than query questions in junior screens and more weight from mid-level upward, because a correct query that never finishes is not a solution. You will not be asked to tune a query in isolation; you will be handed one that works on a sample and asked why it stalls on production. Reach for the execution plan before you reach for a rewrite, and name the column and the index you would add rather than saying 'add an index somewhere'. If when to use SQL versus Excel is still an open question for you, the answer is usually that anything past a few hundred thousand rows belongs in the database.
Take
SELECT *
FROM orders
WHERE YEAR(order_date) = 2025Wrapping the column in a function makes the index on order_date unusable, because the engine must evaluate YEAR() for every row. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01' so the predicate is sargable and the index can be seeked. On the 240,000-row orders table the difference is small;
On a 240-million-row table it is the difference between two seconds and two minutes. What they listen for: whether you reach for the index rather than for more hardware.
Answer by query pattern, not by column. Foreign keys need one: create an index on orders(customer_id), because without it every join to customers scans the whole orders table. Date range filters want an index on orders(order_date). A composite index on orders(customer_id, order_date) serves both the join and the per-customer date ordering, and is usually better than two separate indexes. Say out loud that indexes cost write speed and storage, so you would not add them everywhere, and that you would confirm with EXPLAIN rather than assuming.
WITH dupes AS (
SELECT order_id,
ROW_NUMBER() OVER (
PARTITION BY customer_id,
order_date,
total_amount
ORDER BY order_id) AS rn
FROM orders)
SELECT *
FROM dupes
WHERE rn > 1;Then delete the rows where rn > 1 after you have reviewed them. The trap is defining a duplicate on too few columns: two different orders from the same customer on the same day are not duplicates, and deleting them destroys revenue. I would always run the SELECT first, export the list, and get sign-off before any DELETE, because a delete on the wrong key is not reversible outside a restore.
Scenario Questions That Show Up Without Warning
Beyond the query drills, most analyst loops include one open scenario where there is no single right query. Two recur constantly. First: revenue dropped 15 percent last month, find out why. The strong answer is a sequence, not a query — confirm the drop is real and not a pipeline gap, then slice by region, then by product, then by new versus returning customers, then check order status mix for a spike in cancellations. Second: a stakeholder says a number is wrong. The strong answer starts by reproducing their number with their filters, then checking join grain, status filters, and timezone on the date column before touching anyone's code.
Say three things in order: what you would check first and why, which table and column you would slice on, and what result would confirm or rule out each hypothesis. Candidates who start typing immediately usually answer the wrong question confidently. The interviewer is scoring the reasoning, and a clear structure with a smaller query beats a clever query with no explanation.
Close every answer with one limit: this excludes cancelled orders, this assumes customer_id is unique in customers, this will be slow on the full table without an index. That habit is what separates someone who has shipped reports from someone who has only done exercises. It also gives the interviewer an obvious thread to pull on, which is how interviews go well for you.
If you are preparing the behavioural half of the loop at the same time, the common interview questions guide covers the structure that works, and pairing SQL practice with a written walkthrough of your own portfolio queries is a reliable way to sound like you have done the work. Pick eight questions from the list above, run them against your seed tables tonight, and write down the output you expected versus what came back. That notebook is your revision material for the final week.
