The SQL round

Grain is the answer to "what does one row represent?". In orders one row is an order. In order_items one row is an item within an order. Join the two and one row becomes an item again, with the order's columns copied onto every item it has. Nothing warns you. The query runs, and any COUNT(*) after the join counts items.

Ask grain twice: once for each table you will touch, and once for the answer the question wants. "How many orders include at least one item priced above 100?" wants one number counting orders. The join's grain is items. That mismatch is the whole trap, and COUNT(DISTINCT o.order_id) or an EXISTS subquery closes it. In the sample, 203 of the 2,085 orders have more than one item, which is why the trap reads 850 instead of 802: 48 extra item rows.

A column called customer_id sounds like a person. In this dataset it is issued with each order, so every customer_id has exactly one order and "customers with more than one order" grouped by it returns nothing. The person is customer_unique_id. The schema note says so in one line, and the question rewards whoever read it.

Say it out loud: "the question counts people, so I need the column that identifies a person; is that customer_id or customer_unique_id?" If the schema does not say, ask the interviewer. Asking what identifies an entity is an ordinary professional question, and in most rooms it earns credit rather than costing it.

Two last questions. What range does the question ask for, and is the boundary a date, a timestamp, or text that looks like either? And does it ask for a row order? "Orders per month in 2017, in month order" asks for one; "count the orders in each status" does not. A checker that compares rows will fail a correct answer returned in the wrong order when an order was asked, and should not when none was.

Then the fourth check from the title, NULL, which Part 2 takes in full. Write the four answers in the margin before the query: one row is ..., the id is ..., NULL can appear in ..., range and order are .... It looks slow. It is about forty seconds, and it is the forty seconds the interviewer most wants to hear.

The documentation is precise: avg() "returns the average value of all non-NULL X within a group", and its result "is NULL if there are no non-NULL inputs". In the same family, count(X) counts the rows where X is not NULL while count(*) counts rows, and sum() over no non-NULL values is NULL where total() returns 0.0.

Now the trap. Q3 asks what share of delivered orders arrived on or before the estimated date. 59 orders in the sample were never delivered, so their delivered_date is NULL. CASE WHEN delivered_date <= estimated_date THEN 1 ELSE 0 END sends a NULL comparison to the ELSE branch, so every undelivered order is scored as late and lands in the denominator. The trap reads 0.916; restricted to delivered orders, as asked, the answer is 0.943. Either filter with WHERE delivered_date IS NOT NULL, or let the undelivered rows produce NULL so that avg() skips them. Then say which you did, and why.

SQLite excludes rows where the WHERE expression evaluates "to either false or NULL", and HAVING "is evaluated once for each group". Q4 asks for sellers with at least five items whose average price over all their items is above 150. Put price > 150 in WHERE and the cheap items are gone before any average is taken: every surviving average is above 150 by construction, and "at least five items" has become "at least five pricey items".

The reference returns 24 sellers; the WHERE version returns 18. The condition on the group's average belongs in HAVING COUNT(*) >= 5 AND AVG(price) > 150. One more rule from the same page: an aggregate query without GROUP BY "always returns exactly one row", which is why SELECT COUNT(*) FROM orders WHERE 1 = 0 returns a single row holding 0, not an empty result.

purchase_ts is a timestamp stored as text. BETWEEN '2017-01-01' AND '2017-12-31' compares strings, and a value such as '2017-12-31 09:14:00' sorts after '2017-12-31', so every order placed during 31 December falls outside the range. In the sample that is 2 orders, and December reads 109 instead of 111 while the other eleven months are right. Write the range half-open, purchase_ts >= '2017-01-01' AND purchase_ts < '2018-01-01', and the edge disappears whatever the time of day.

For an outer join, the NULL rows for unmatched records "are added after ON clause processing but before WHERE clause processing". So a LEFT JOIN from orders to order_items keeps orders with no items, padding the item columns with NULL, and then WHERE i.price > 100 removes exactly those padded rows, because a comparison with NULL is not true. The query is now an inner join wearing a LEFT JOIN's name. Move the condition into the ON clause when you mean "join only the pricey items but keep every order", and leave it in WHERE when you mean "only orders that have a pricey item".

The documentation also says its own step order is "purely illustrative": engines are free to execute in any order that gives the same result. Use the pipeline in the figure as a way to reason about where a filter applies, not as a claim about what the engine physically does first.

Behroozi, Shirolkar, Barik and Parnin (2020, ESEC/FSE) randomly assigned 48 computer science students to solve a technical problem on a whiteboard either privately or while an interviewer watched. Their abstract reports that "performance is reduced by more than half, by simply being watched by an interviewer". Only the abstract was read for this page.

Read it at its real size. It is one study, 48 people, students rather than working analysts, and a whiteboard problem rather than a SQL round. It does not tell you how much any particular interview costs you. What it does show, cleanly because it was randomized, is that being watched is not a neutral background: the same person, given the same kind of problem, does measurably worse with an audience. That is a practice problem, and practice problems have practice answers.

If watching changes performance, then practice done alone at a desk trains a slightly different skill from the one the round tests. Three moves close the gap. Practise with someone watching, even a friend who knows no SQL; a video call with the camera on is enough. Narrate the four checks every time, so that the first minute of a real round is a habit rather than a decision. And test on a tiny case first: before the full query, write the three-row example you expect, as in Part 1's toy, and say what it should return.

Narration also changes what the interviewer can score. A silent wrong answer is just wrong. A narrated wrong answer shows the four checks, the plan and the place it went off, and most rubrics have lines for reasoning as well as for the final number. How you sound while you do it, voice and pace and presence, is delivery, and Learn Public Speaking with Phoebe ↗ covers it.

Everyone stalls in a round at some point. Silence is the expensive version. The cheap version is one sentence: "I am not sure the join keeps one row per order, so next I would check the row count before and after the join." It tells the interviewer what you know, what you doubt and how you would find out, which is most of what an analyst does on a normal Tuesday.

Nerves in a round are real and are treated here as a workload and practice question: rehearsal in the watched condition, a fixed opening routine (the four checks), and fewer decisions left for the day. Nothing here is medical or clinical advice.