October 12, 2026·6 min read
Hand a candidate three tidy tables and ask for monthly revenue by region. Nearly everyone can do it. The ones who cannot are usually filtered out before the interview anyway.
Then they join and meet the real warehouse. A sync that ran twice and left duplicate rows in a fact table. A status column where null means "not asked" in records before 2024 and "declined" after it. A timestamp that is stored in UTC in one table and local time in another, so the month boundary moves depending on which one you trust. A dashboard that has said one thing for a year while finance has said another.
An analyst who produces a confident wrong number in that environment costs more than one who produces nothing. That is the thing worth interviewing for, and it does not show up on clean data.
Because they are easy to write and easy to grade. One right answer, one query, done.
They are also the part of the job that is most completely automatable now. A model will write a correct window function faster than any candidate. What it will not do is notice that the number it just produced counts the same order twice, because nothing in the question said the table might be dirty.
So make the data dirty and ask a business question instead of a SQL question.
The setup. A fact table where a subset of rows was loaded twice during a backfill. Totals look plausible, which is what makes it dangerous. Ask for revenue for a period that includes the affected window.
Ask: what was revenue last quarter?
Strong looks like: they check the grain before they trust a total. Count rows, count distinct on the business key, look at whether any order id appears more than once. They find the duplication, say so plainly, and then give you a number with the correction and an explanation of what they did.
Weak looks like: a single query and a single number, delivered with confidence. This is the central failure mode of the role, and it is invisible unless the data has something wrong in it.
Why it works: it tests the one habit that separates reliable analysts from fast ones: sanity-checking the shape of the data before reporting from it.
The setup. A column where null changed meaning when a form was redesigned. Before the change, the field was not collected. After it, null means the customer actively declined.
Ask: what percentage of customers opted out last year?
Strong looks like: they notice the distribution changes sharply at a date and ask why rather than assuming. They go looking for a reason, in a schema change, a migration, or by asking you. They then answer with the split stated explicitly: this is the rate since the change, this is why the earlier period is not comparable.
Weak looks like: treating every null the same way and producing one percentage for the whole year. It is a defensible query and a misleading answer.
Why it works: it is the most common real analytical trap, and it rewards curiosity about the business rather than fluency in SQL.
The setup. Two tables with timestamps, one in UTC and one in local time, and a reporting period defined by calendar month. Orders near midnight at the end of a month fall on different sides depending on which column you use.
Ask: these two reports disagree by about one percent every month. Find out why.
Strong looks like: they reach for the edges immediately, looking at rows near the boundary rather than at the totals. They can explain the difference precisely and they ask which definition the business wants, because that is a decision, not a fact. Then they say how they would stop this recurring, usually by defining the reporting timezone once and converting at the edge.
Weak looks like: declaring one report broken and the other correct without finding out which definition the company uses.
Why it works: small persistent discrepancies are how trust in a data team is lost, and chasing one properly is a very specific skill.
The setup. A dashboard metric defined one way (orders placed) and a finance figure defined another way (orders invoiced, excluding refunds and internal accounts). Both are right. Nobody has written either definition down.
Ask: finance says our active customer number is wrong. Reconcile them.
Strong looks like: they do not try to win. They put the two definitions side by side, quantify each difference (refunds account for this much, internal accounts for that much), and get the two numbers to agree arithmetically. Then they recommend a single documented definition and say who should own it.
Weak looks like: adjusting the query until it matches finance's number, with no account of where the difference came from. It ends the argument and guarantees it returns next quarter.
Why it works: most senior analytics work is definitional rather than technical, and this is the cleanest way to see whether someone can do it. The same instinct, one level down the stack, is what interviewing data engineers on a pipeline that actually breaks is testing.
Four rows, the same for every candidate:
None of those rows is "knows window functions". That shows up for free in the first ten minutes.
These tasks need a real database with real mess in it, not a CSV. The duplicates have to be in a table, the timezone difference has to exist between two columns, and the candidate needs to be able to run exploratory queries freely.
EasyEnv gives the candidate a box with the database already loaded, the duplicate backfill already applied, the null semantics already changed at a date, and a query tool open. Every candidate gets the same recipe, so the mess is identical and comparison is fair. The box is disposable, so it does not matter what they run. The session is recorded, so you can see whether they counted distinct keys before reporting a total.
The limits, plainly. Someone has to build the messy dataset once, and it should look like your business rather than a generic shop. Reviewing takes an analyst, not a generalist. And for a junior reporting role where every query is reviewed before it ships, two tasks is enough.
Stop handing out clean tables. Give the candidate a fact table with duplicates, a null that changed meaning, a month boundary that moves and a number finance disputes.
If you only run one, run the duplicates. Whether somebody checks the grain before trusting a total is the difference between an analyst you can rely on and a very fast source of wrong numbers.
Run live coding sessions and take-home challenges in real production environments. Watch sessions back, score consistently, and hire with confidence.
More posts you might like
PHP hiring still runs on framework questions while the failures are operational and silent. Here are four tasks on a running Laravel application, a queue nobody noticed had stopped, a listing page that issues hundreds of queries, a migration that ran on one server, and uploads that vanish behind a load balancer.
.NET gives you so much for free that the mistakes hide inside the framework rather than in the code under review. Here are four tasks on a running service, a deadlock under load, a captured dependency, a query that drags the whole table into memory, and a background worker that died quietly.
Read more
Node interviews ask candidates to recite the event loop, then production fails in ways no recitation covers. Here are four tasks on a running service, an unhandled rejection, a heap that only climbs, a retry storm and a blocked event loop, with what separates a strong answer from a plausible one.
Read more
