The practice-round bench

Strict on rows. One extra row, one missing row, or one average off in the second decimal is a fail. That is deliberate. A screen that asks "how many orders" has one right number, and an answer that is close is the answer of someone who did not check. Interviewers who run your query see the same thing.

Relaxed on style. The checker ignores column names and accepts any query that produces the reference rows: a join with COUNT(DISTINCT ...), an EXISTS subquery, or a common table expression all pass the same question. Row order is ignored unless the question asks for an order, and then it is checked. Column order is checked, because the question names the columns in order.

The first differing row. When you fail, the bench prints the first row where your result and the reference part company, with both sides sorted the same way. That one row is usually enough to find the bug: a count that is too high points at a join, a share that is too low points at NULLs, a missing seller points at a filter in the wrong place.

The Brazilian E-Commerce Public Dataset by Olist is published on Kaggle under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 licence. The sample here draws 2,000 people with a fixed seed and keeps every order they placed: 2,085 orders, 2,359 order items from 775 sellers. Ids are renumbered; there are no names, reviews or text. Drawing people rather than orders keeps repeat customers repeat, which question 6 depends on.

The engine is SQLite compiled to WebAssembly (sql.js 1.10.3, which reports SQLite 3.45.2). The canon result for every question was produced twice, once by that engine and once by Python's own sqlite3 module running SQLite 3.51.1, and the two agree on all 356 values compared. Nothing you type leaves the page.

Q2. Joining orders to order items returns one row per item. Of the 2,085 orders, 203 have more than one item, so any count taken after the join is a count of items unless you say COUNT(DISTINCT o.order_id). The trap counts 850; the right answer is 802 orders with at least one item priced above 100. Six percent too high, and fluent.

Q3. 59 orders in the sample never arrived, so their delivered_date is NULL. A comparison with NULL is neither true nor false; it is NULL, and CASE WHEN ... THEN 1 ELSE 0 sends it to the ELSE branch. The trap scores every undelivered order as late and reports 0.916 on time; restricted to delivered orders, as the question asks, the answer is 0.943. SQLite's own documentation says that avg() ignores NULL inputs, which is exactly why writing the CASE so that undelivered orders produce NULL, or filtering them out, gives the right denominator.

The question asks for sellers whose average price over all their items is above 150, among sellers with at least five items. The trap filters WHERE price > 150 first. That throws away each seller's cheap items before the average is taken, so every surviving average is above 150 by construction, and "at least five items" becomes "at least five pricey items".

The reference lists 24 sellers; the trap lists 18. Only 14 appear in both: the trap misses 10 real answers, adds 4 sellers who do not qualify, and of the 14 it shares, 8 carry an inflated average. Seller 1286, for one, averages 239.72 over all items and 415.00 in the trap. WHERE filters rows before grouping; HAVING filters groups after. Say that sentence out loud in the interview while you write the query, and the interviewer hears that you know which one the question needs.

Q5. purchase_ts is a timestamp stored as text. BETWEEN '2017-01-01' AND '2017-12-31' compares strings, and '2017-12-31 09:14:00' sorts after '2017-12-31', so every order placed on 31 December falls outside the range. In this sample that is 2 orders: December reads 109 instead of 111. Eleven months are right and one is quietly short, which is the worst kind of wrong. A half-open range, >= '2017-01-01' AND < '2018-01-01', has no such edge.

Q6. In this dataset customer_id is issued with each order, so no customer_id ever has two orders, and the trap reports 0 repeat customers. The person is customer_unique_id: 57 people placed more than one order. The schema note says so in one line, which is the point. In an interview, asking "what does one row of this table represent, and what identifies a person?" before writing anything is the move that catches this, and it costs ten seconds.

Status. For each competency the best evidence in the bank decides: a strong story makes it covered, a thin one makes it thin, nothing makes it missing. Coverage is covered plus half of thin, divided by the number of competencies. Tagged or untagged. An untagged bank uses each story only for the competency it was written for; a tagged bank uses each story for every competency it can honestly evidence, which is the session 2 exercise. The story you tell is the shortest one at the best level. Fits in 2 minutes counts the competencies that have evidence and whose chosen story runs 120 seconds or less.

The two-minute line is a common coaching rule of thumb, not a research finding; no study behind a particular answer length was found, and the widget says so. It is in the bench because a behavioural loop has a clock, and a four-minute story eats the next question.

Every fit in the matrix is a judgement, and here it is Ren's own. A candidate's self-assessment is generous in predictable places: a story you are proud of feels strong for more competencies than it really evidences. The habit worth building is to mark a fit strong only when the story contains a specific action you took and a result you can state, the situation, task, action and result shape session 2 used. The bench's arithmetic is only as honest as those marks.

3 to 5 stories, untagged: 37.5 to 62.5 percent. Each new story adds the one competency it was written for. 8 stories, tagged: 93.8 percent, 7 covered and 1 thin, told with 6 distinct stories. Tagging is the big jump, and it costs no new stories: it is the work of asking, for every story you already have, which other questions it can honestly answer. The one thin row, turning a vague ask into a question, is the gap the bank cannot fill by rearranging. The fix is a new story, not a cleverer tag.

Trimmed: coverage does not move, and 0 of 8 answers fitting in two minutes becomes 8 of 8. Trimming changes nothing about what you can evidence and everything about whether you get to finish saying it.

Ren's best story is the A/B test that was stopped early: a clear conflict, a statistical point, a good ending. The temptation is to tell it for every question. The model: the same story answers all eight competencies. Claimed coverage reads 100 percent, because every question got an answer. Honest coverage is 31.3 percent: the story is strong evidence for experiments and for pushing back, thin for metric choice, and evidence of nothing for the other five. Distinct stories told: 1. Most-reused story: 8 of 8.

The honest nuance: a strong story told twice in a loop is fine, and the matrix itself picked one story for two rows on the top rung. The cost of stretching is not the repetition. It is that five competencies were asked about and none was evidenced, and a structured interviewer scoring each question against its own rubric scores those five low, however good the story was.

A bench that can only go up is measuring nothing. The break button keeps Ren's bank exactly as it is and swaps the job description for B, a constructed machine learning engineer role. Coverage falls from 93.8 to 62.5 percent: 4 covered, 2 thin, and 2 missing, ML system design and deploying models, competencies none of an analyst's stories evidence. Stretching the A/B test story across job description B scores 0 percent honest coverage, because it fits none of the eight.

The lesson is the session 2 habit in reverse: a bank is built for a job description, and a new job description means a new pass of tagging and, usually, one or two new stories. Reusing last month's prep for a different role is the break button pressed by accident.