A Full Mock SQL Round

Five questions on the schema from the intro lesson. Each shows a wrong first attempt, because that is what actually happens and the recovery is what is scored.

Q1 — warm-up: revenue by country

“Total shipped revenue per country, highest first.”

Attempt one:

SELECT country, sum(amount) AS revenue
FROM customers JOIN orders USING (customer_id)
WHERE status = 'shipped'
GROUP BY country ORDER BY revenue DESC;

┌─────────┬─────────┐
│ country │ revenue │
├─────────┼─────────┤
│ UK      │  400.50 │
│ US      │  310.00 │
│ India   │  150.00 │
└─────────┴─────────┘

Correct — and incomplete. Total shipped revenue in the table is 935.50, and these sum to 860.50. Order 108 has a NULL customer_id and the inner join dropped it.

The recovery, said out loud:

“These sum to 860.50 but the shipped total is 935.50. There’s an order with a NULL customer, so it has no country. Should it be excluded, or bucketed as ‘unknown’? I’d surface it rather than hide it.”

SELECT coalesce(c.country, '(unknown)') AS country, sum(o.amount) AS revenue
FROM orders o LEFT JOIN customers c USING (customer_id)
WHERE o.status = 'shipped'
GROUP BY 1 ORDER BY revenue DESC;

┌───────────┬─────────┐
│  country  │ revenue │
├───────────┼─────────┤
│ UK        │  400.50 │
│ US        │  310.00 │
│ India     │  150.00 │
│ (unknown) │   75.00 │
└───────────┴─────────┘

Scored: noticing the total does not reconcile. Almost nobody checks; the ones who do get the next question weighted more favourably.

Q2 — the LEFT JOIN filter

“Every customer with their shipped order count, including customers with none.”

Attempt one:

SELECT c.name, count(*) AS shipped_orders
FROM customers c LEFT JOIN orders o USING (customer_id)
WHERE o.status = 'shipped'
GROUP BY c.name ORDER BY c.name;

┌─────────┬────────────────┐
│  name   │ shipped_orders │
├─────────┼────────────────┤
│ Ana     │              3 │
│ Bo      │              1 │
│ Di      │              1 │
└─────────┴────────────────┘

Three rows from six customers, when the question explicitly said “including customers with none”. Two bugs stacked:

• WHERE o.status = 'shipped' filters after the join, discarding the NULL-filled rows — the LEFT JOIN became an inner join.
• count(*) would have counted the unmatched row as 1 even if the first bug were fixed.

Fixed:

SELECT c.name, count(o.order_id) AS shipped_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'shipped'
GROUP BY c.name ORDER BY shipped_orders DESC, c.name;

┌─────────┬────────────────┐
│  name   │ shipped_orders │
├─────────┼────────────────┤
│ Ana     │              3 │
│ Bo      │              1 │
│ Di      │              1 │
│ Cy      │              0 │
│ Eve     │              0 │
│ Fay     │              0 │
└─────────┴────────────────┘

Scored: knowing the filter belongs in ON, and count(col) not count(*). This exact question, in this exact shape, is asked constantly.

Q3 — top N per group

“For each customer, their single largest shipped order — id, date, and amount.”

Attempt one:

SELECT customer_id, order_id, max(amount)
FROM orders WHERE status = 'shipped' GROUP BY customer_id;

Binder Error: column "order_id" must appear in the GROUP BY clause or must be part of an
aggregate function.

The error is the useful signal: MAX returns a value, not a row.

The clarifying question first:

“If a customer has two orders at the same amount, do you want both, or exactly one? I’ll use ROW_NUMBER for exactly one and add a tiebreaker so it’s deterministic — say so if you’d rather see all ties, and I’ll switch to RANK.”

WITH ranked AS (
    SELECT o.customer_id, c.name, o.order_id, o.order_date, o.amount,
           row_number() OVER (PARTITION BY o.customer_id
                              ORDER BY o.amount DESC, o.order_id) AS rn
    FROM orders o JOIN customers c USING (customer_id)
    WHERE o.status = 'shipped'
)
SELECT name, order_id, order_date, amount FROM ranked
WHERE rn = 1 ORDER BY amount DESC;

┌─────────┬──────────┬────────────┬────────┐
│  name   │ order_id │ order_date │ amount │
├─────────┼──────────┼────────────┼────────┤
│ Bo      │      103 │ 2024-03-18 │ 310.00 │
│ Ana     │      105 │ 2024-04-20 │ 200.00 │
│ Di      │      107 │ 2024-05-30 │ 150.00 │
└─────────┴──────────┴────────────┴────────┘

Scored: the tie question, the tiebreaker in the ORDER BY, and the status filter being inside the CTE. Filtering after ranking would rank a cancelled order first and then delete it, leaving that customer with no row.

Q4 — month over month, with the gap

“Monthly shipped revenue and the percentage change from the previous month, for the whole of 2024 so far.”

Attempt one:

SELECT date_trunc('month', order_date)::DATE AS month,
       sum(amount) AS revenue,
       round(100.0 * (sum(amount) - lag(sum(amount)) OVER (ORDER BY 1))
             / lag(sum(amount)) OVER (ORDER BY 1), 1) AS pct
FROM orders WHERE status = 'shipped'
GROUP BY 1 ORDER BY 1;

┌────────────┬─────────┬────────┐
│   month    │ revenue │  pct   │
├────────────┼─────────┼────────┤
│ 2024-03-01 │  510.50 │   NULL │
│ 2024-04-01 │  200.00 │  -60.8 │
│ 2024-05-01 │  150.00 │  -25.0 │
│ 2024-06-01 │   75.00 │  -50.0 │
└────────────┴─────────┴────────┘

Four months. The question said “the whole of 2024 so far”, and January and February are missing — not because revenue was zero, but because no rows exist to group.

Every percentage after the first is therefore comparing to the previous row, which happens to be the previous month here only by luck.

Fixed, with a spine:

WITH months AS (
    SELECT range::DATE AS month
    FROM range(DATE '2024-01-01', DATE '2024-07-01', INTERVAL 1 MONTH)
),
monthly AS (
    SELECT date_trunc('month', order_date)::DATE AS month, sum(amount) AS revenue
    FROM orders WHERE status = 'shipped' GROUP BY 1
),
joined AS (
    SELECT m.month, coalesce(x.revenue, 0) AS revenue
    FROM months m LEFT JOIN monthly x USING (month)
)
SELECT month, revenue,
       lag(revenue) OVER (ORDER BY month) AS prev,
       round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month))
             / nullif(lag(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM joined ORDER BY month;

┌────────────┬─────────┬────────┬────────────┐
│   month    │ revenue │  prev  │ pct_change │
├────────────┼─────────┼────────┼────────────┤
│ 2024-01-01 │    0.00 │   NULL │       NULL │
│ 2024-02-01 │    0.00 │   0.00 │       NULL │
│ 2024-03-01 │  510.50 │   0.00 │       NULL │
│ 2024-04-01 │  200.00 │ 510.50 │      -60.8 │
│ 2024-05-01 │  150.00 │ 200.00 │      -25.0 │
│ 2024-06-01 │   75.00 │ 150.00 │      -50.0 │
└────────────┴─────────┴────────┴────────────┘

March now correctly shows NULL rather than a growth figure — you cannot compute percentage growth from zero, and nullif(prev, 0) says so instead of throwing or returning infinity.

Scored: noticing the missing months, and the division-by-zero guard. Both are invisible in the wrong version’s output, which is exactly why they are asked.

Q5 — open-ended: the churn question

“Which customers are at risk of churning?”

There is no single right answer, and that is the question. Do not start writing.

“That needs a definition. Churn usually means no activity for longer than the normal gap between orders. I’d propose: a customer whose days since last order exceeds twice their own median inter-order gap, and who has at least two orders so the gap is meaningful. Does that match how the business thinks about it, or is there a fixed window like 90 days?”

Then build it in visible steps:

WITH gaps AS (
    SELECT customer_id, order_date,
           datediff('day', lag(order_date) OVER (PARTITION BY customer_id
                                                 ORDER BY order_date), order_date) AS gap_days
    FROM orders WHERE status = 'shipped' AND customer_id IS NOT NULL
),
per_customer AS (
    SELECT customer_id,
           count(*)                          AS orders,
           max(order_date)                   AS last_order,
           median(gap_days)                  AS typical_gap,
           datediff('day', max(order_date), DATE '2024-07-01') AS days_quiet
    FROM gaps GROUP BY customer_id
)
SELECT c.name, p.orders, p.last_order, p.typical_gap, p.days_quiet,
       CASE
           WHEN p.orders < 2                          THEN 'insufficient history'
           WHEN p.days_quiet > 2 * p.typical_gap      THEN 'at risk'
           ELSE 'healthy'
       END AS assessment
FROM per_customer p JOIN customers c USING (customer_id)
ORDER BY p.days_quiet DESC;

┌─────────┬────────┬────────────┬─────────────┬────────────┬──────────────────────┐
│  name   │ orders │ last_order │ typical_gap │ days_quiet │      assessment      │
├─────────┼────────┼────────────┼─────────────┼────────────┼──────────────────────┤
│ Ana     │      3 │ 2024-04-20 │        25.0 │         72 │ at risk              │
│ Di      │      1 │ 2024-05-30 │        NULL │         32 │ insufficient history │
│ Bo      │      1 │ 2024-03-18 │        NULL │        105 │ insufficient history │
└─────────┴────────┴────────────┴─────────────┴────────────┴──────────────────────┘

Then volunteer the limitations before being asked:

“Three caveats. The dataset is too small for a median gap to mean much — with three orders I have two gaps. Customers with one order can’t be assessed this way at all, and they may be the most important churn segment, so a separate rule based on days since signup would be needed. And the hardcoded DATE '2024-07-01' should be current_date in production; I pinned it so the result is reproducible.”

Scored: refusing to write until churn is defined, building in named steps, and stating the limitations unprompted. The query itself is almost incidental.

The scoring, restated

Q1  reconciling totals — noticing 860.50 != 935.50
Q2  ON versus WHERE, count(col) versus count(*)
Q3  the tie question, deterministic ordering, filter inside the CTE
Q4  the missing months, and nullif on the divisor
Q5  refusing to write before defining the metric, then stating the limits

Notice how little of that is syntax. Every one of the five is scored on a decision made before or after the query, not on the query itself.

What to do when you are stuck

1. SAY SO, WITH A DIRECTION
   "I'm stuck on getting one row per group. I know it's a window function —
    let me write the ranking first and filter it after."

2. SIMPLIFY THE INPUT
   Solve it for one customer, then generalise. Interviewers help candidates
   who have narrowed the problem; they cannot help silence.

3. WRITE THE SHAPE IN COMMENTS
   -- 1. shipped orders only
   -- 2. rank per customer by amount
   -- 3. keep rank 1
   A commented skeleton with a syntax gap scores far better than nothing.

4. NAME THE CONCEPT IF NOT THE KEYWORD
   "This is the first-row-per-group pattern — DISTINCT ON in Postgres,
    ROW_NUMBER elsewhere." That is the thing being tested.

Practice

860.50 grouped vs 935.50 total — one order has a NULL customer_id

3 rows returned, 6 customers exist

January and February vanish; every lag() compares the wrong pair.

Wrong move. Define churn first, agree it, then build in named steps.

That closes the SQL track. For the surrounding rounds — pipeline design, data modelling, and the take-home — see Data Engineering Interviews; for the coding round in Python or Java, DSA in Python and DSA in Java.