Amazon Data Engineer — SQL Set 1
Ten questions, fully worked, in the order candidates meet them
There is a particular kind of silence that falls in a SQL interview about twenty seconds after the question has been read aloud. The candidate has understood the words. The tables look familiar. Nothing on the screen is exotic. And yet the first line refuses to write itself, because the real difficulty was never the syntax. It was deciding what the question is actually asking for underneath the polite business wording, which rows survive the filters, which duplicates are accidents and which are information, and what happens at the edges where a customer has one order instead of many, a month is missing entirely, or a value the candidate assumed would always exist turns out to be null.

This first SQL set for the Amazon data engineer loop is built for that silence. It contains ten original questions, written as rewritten variants in the pattern candidates report from screens and loops, each answered completely: the question itself, what the interviewer is really testing, a full worked answer with SQL you can run and reason about, the common wrong answer and why it fails, a worked variant that changes one condition, and the follow-up question an interviewer is likely to ask next. Nothing here is copied from a proprietary question bank. The value is not in memorising these ten shapes. The value is in learning to recognise them when they arrive wearing different table names, different column names, and a stakeholder story you have never heard before.
Read the set actively. Cover the answer, write your own attempt on paper or in an editor, then compare line by line. Where your version differs, ask whether the difference is cosmetic or whether it changes the rows returned. In SQL, most interview failures are not cosmetic. A missing tie rule changes a top-five list. A distinct in the wrong place hides a deduplication problem instead of solving it. An inner join where an anti-join was needed quietly deletes exactly the customers the question was about. The gap between a candidate who passes and a candidate who nearly passes is usually three or four such decisions, made calmly, explained out loud, and defended when the interviewer pushes.
What the Amazon data engineer SQL loop actually tests
Public guides describe, and candidates consistently report, a structure with two SQL moments that test different things. The first is a recruiter or hiring screen, often a timed online assessment or a phone screen with a shared editor, where the emphasis is on correctness under mild time pressure: can you produce working queries for filtering, aggregation, joins and one or two window functions without freezing. The second is the loop itself, a series of interviews where SQL appears inside a broader data engineering conversation. There the emphasis shifts. Interviewers probe whether your query would survive production: a billion-row orders table, an upstream feed that delivered the same file twice, a metric definition that changed last quarter, a dashboard that must reconcile to the cent with finance.
Treat those descriptions as reported patterns, not as a promise about any individual interview. Teams differ, levels differ, and a question that is a screen question for one candidate becomes a deep loop discussion for another. What does not change is the underlying standard, and it is worth stating plainly because every answer in this set is written to it.
First, production-ready SQL filters early. You reduce rows before you join, before you window, before you sort, and you can explain why the order matters for cost on large tables. Second, it handles nulls deliberately rather than hopefully: you know which columns can be null, what a null comparison does, what an aggregate silently ignores, and where a coalesce is honest and where it conceals a data quality problem that should be surfaced instead. Third, it guards arithmetic: division by zero, integer division truncating a ratio, percentages computed on an empty denominator. Fourth, it uses explicit joins with named join conditions, never comma joins inherited from another era, and it states the grain of every table before joining it. Fifth, it names its intermediate steps. Common table expressions with meaningful names — deduplicated_orders, monthly_activity, ranked_products — are not decoration. They are how an interviewer follows your reasoning, how a reviewer audits your logic at midnight, and how you yourself debug a query when the first result looks wrong.
Underneath those habits sits a simpler test that candidates underestimate: can you translate an ambiguous English request into a precise row-level definition, and can you say the definition back before you code. "Top products" might mean by revenue, by units, or by distinct customers. "Recent" might mean calendar days or business days. "Active customer" might mean ordered, logged in, or merely not churned under a definition owned by another team. Strong candidates spend the first minute negotiating meaning. Weaker candidates spend twenty minutes solving a problem nobody set. Every worked answer below therefore begins by pinning the definition down, and several of the follow-ups are precisely the moment an interviewer changes that definition to see whether you notice.
Difficulty tiers: how to pace yourself through this set
The ten questions are ordered basic to intermediate to advanced, following the shape of the leading preparation guides, but the tiers deserve a more honest description than easy, medium and hard.
The foundational tier, Questions 1 and 2 in spirit even though both contain a trap, tests whether your everyday tools are reliable: ranking with ties, and deduplication with a chosen survivor. These are the questions a prepared candidate should finish cleanly and quickly, banking time and confidence. Losing marks here is expensive because the interviewer reasonably concludes the foundations are shaky and spends the rest of the hour checking them.
The intermediate tier, Questions 3 to 7, is where most loops are actually decided. Consecutive-period logic, recency comparisons, rolling windows, anti-joins and join fan-out are the daily bread of data engineering. Each question has a well-known wrong answer that produces plausible-looking output, which is exactly why interviewers love them: they separate candidates who test their logic from candidates who trust their first draft. If you can work this tier methodically — small example in your head, edge cases named before you run — you are performing at the level the loop is calibrated for.
The advanced tier, Questions 8 to 10, adds judgement under mess. A second-highest pattern that must survive nulls and ties at warehouse grain. An incremental load that must be safe to re-run after a failure at 3 a.m. A duplicate-charge detector that must define "duplicate" in time without flagging legitimate repeat purchases. These questions have more than one defensible answer, and the interviewer's interest moves from the final rows to your trade-offs: what you index, what you assume about the source, what you would monitor after shipping. Answer them like an engineer who will own the table tomorrow, not like a student finishing an exercise.
A note on pacing in the room. Candidates report that loop SQL rounds often allow roughly fifteen to twenty-five minutes per substantial question, including clarification and follow-up. Practise to that clock occasionally, but do not train only to the clock. Speed built on unexamined habits produces fast wrong answers. Build accuracy first at untimed pace, then compress.
The most-asked functions, as a working list
Rather than a table, here is the toolkit as structured notes. For each family, learn what it does, its null behaviour, and one trap.
Ranking and row-numbering windows
ROW_NUMBER — assigns a unique sequence within each partition; your deduplication and recency workhorse; ties are broken arbitrarily unless you add deterministic ordering columns.
RANK — shares a rank across ties and then skips; useful when the business genuinely wants tied rows treated identically and gaps in numbering are acceptable.
DENSE_RANK — shares a rank across ties without skipping; the correct tool when “top five” must mean five rank levels even when ties crowd the boundary.
NTILE — splits ordered rows into buckets; handy for quartiles and deciles; remember bucket sizes differ by at most one and small partitions behave oddly.
Analytical windows for comparison across rows
LAG and LEAD — reach the previous or next row in an ordering; the natural way to compute gaps between orders, changes between days, and streak breaks; always state what happens on the first row, where LAG returns null.
SUM, AVG, COUNT as window functions — running and rolling calculations with an explicit frame; the frame clause, not the function name, decides whether you get a cumulative figure or a true rolling one.
FIRST_VALUE and LAST_VALUE — pull a value from the edge of a frame; LAST_VALUE is a classic trap because the default frame often ends at the current row, returning the current value instead of the partition’s last.
Aggregation and conditional logic
COUNT DISTINCT — counts unique values and ignores nulls; powerful for customer and product counts; can be expensive on huge data, which is worth mentioning when you use it.
SUM with CASE — conditional aggregation, the pivot pattern; lets one pass over the data produce several labelled measures without self-joins.
COALESCE and NULLIF — COALESCE returns the first non-null argument; NULLIF returns null when two values are equal, and together they form the standard guard that turns a dangerous division into a safe one.
FILTER clauses where supported — a cleaner conditional aggregate in dialects that offer it; know it exists, but be ready to write the CASE equivalent in a generic interview setting.
Dates and strings
DATE_TRUNC — collapses a timestamp to month, week or day grain; the single most useful function in consecutive-month and daily-volume problems.
EXTRACT — pulls a component such as month or day of week; useful for seasonality questions, dangerous when it tempts you to compare month numbers across years.
Date arithmetic with intervals — adding and subtracting days or months; syntax varies by dialect, so in an interview state the intent in words and write one clean dialect’s version.
String functions such as TRIM, LOWER and REPLACE — mostly relevant when keys arrive dirty from upstream; normalising a join key is sometimes the entire hidden point of a question.
You do not need fifty functions. You need these families cold, with their edge behaviour understood, because nearly every reported Amazon-style SQL question is a composition of two or three of them wrapped in a business story.
How each answer in this set is built
Every question follows the same discipline. The question is stated with its tables and grain. Then a short section names what is really being tested, because naming the test changes how you allocate effort: a deduplication question is not finished when duplicates disappear, it is finished when the surviving row is the right one and you can prove it. Then the full worked answer appears, with the SQL in a fenced block, preceded by the definition being used and followed by a walk-through of the logic in prose, including the edge cases. Then the common wrong answer is shown or described, with the precise reason it fails — not "this is wrong" but "this returns seven rows when two products tie at rank five, and here is the row that exposes it." Then a worked variant changes one condition, because interviewers rarely accept the first answer as the end of the conversation. Finally, the follow-up: the next question, and a sketch of how a strong candidate handles it.
Two habits run through all ten. Where a scenario is a composite built for teaching, it is written plainly as a scenario — consider a data engineer who inherits a table, imagine a stakeholder who changes a definition — never dressed up as a sourced anecdote about a real team. And where a claim is about the interview process itself, it is framed as what candidates report or what public guides describe. That honesty is not a legal nicety. It is the same habit of mind that makes the SQL trustworthy: say what you know, label what you are assuming, and never let a confident tone smuggle an unverified claim past the reader.
One more piece of equipment before the questions begin: a tiny mental test dataset. For almost every question below, three to five invented rows are enough to break a wrong answer. A customer with a single order. Two products tied on revenue. An order in January and March but not February. A warehouse that shipped nothing. A charge repeated after four minutes and another after forty. Build the habit of constructing that dataset before you trust any query, including the ones printed here. Interviewers notice the habit immediately, because it is what engineers do on real incidents: reproduce small, reason exactly, then scale.
Question 1 — Top 5 products by revenue per category, handling ties
Question
You are given an orders table at order-line grain: one row per product on an order, with columns order_id, order_date, customer_id, product_id, product_name, category, quantity and unit_price. Revenue for a line is quantity multiplied by unit_price. Return, for each category, the top 5 products by total revenue across all time. If products tie on revenue at the boundary, include every product that shares the fifth rank. State your tie rule before you code, and return category, product, total revenue and rank, ordered so a reader can scan category by category.
What they’re really testing
This is a ranking question disguised as an aggregation question. The aggregation itself — group by category and product, sum the line revenue — is the part candidates find comfortable. The test lives in three decisions after the sum. Do you know the difference between the three ranking functions and can you pick the one that matches the stated tie rule. Do you aggregate fully before you rank, rather than ranking raw lines. And do you notice that "top 5" in English is ambiguous until someone decides whether it means five rows, five rank positions with gaps, or five dense rank levels with ties included. The interviewer is also watching whether you handle money carefully: quantity times price per line, summed, with null prices or quantities considered rather than silently absorbed.
Full worked answer
Definition used here: top 5 means the five highest dense rank levels within each category, with every product tied at any of those levels included, so a category may return more than five rows. Revenue excludes lines where quantity or unit_price is null, and those excluded lines should be countable if the interviewer asks about data quality.
WITH product_revenue AS (Walk through it slowly. The first common table expression collapses order lines to one row per product within a category, which is the grain at which ranking makes sense. Grouping by product_id as well as product_name protects you when two distinct products share a display name, a real occurrence in large catalogues fed by multiple sellers. The second expression ranks those product rows inside each category partition. DENSE_RANK is the deliberate choice: if two products tie for first, both receive rank 1 and the next product receives rank 2, so filtering at rank five or better returns five levels of performance rather than five arbitrary rows. The final ordering adds product_name as a deterministic tiebreaker for presentation, while being careful not to confuse presentation order with rank itself.
Test it against a tiny case. Imagine a headphones category where revenues are 100, 90, 90, 80, 70, 70, 60. Dense ranks run 1, 2, 2, 3, 4, 4, 5, so the filter returns all seven products, correctly honouring the instruction to include ties at the boundary — here the tie at rank 4 and the product at rank 5 both survive. If the business had wanted exactly five rows, that is a different question with a different function, and saying so out loud is the mark of a candidate who reads requirements instead of pattern-matching them.
Null handling deserves its sentence. A line with a null price contributes nothing certain to revenue; summing quantity times price in SQL already yields null for that line and SUM ignores nulls, so the explicit WHERE here is partly documentation and partly a guard for dialects and downstream steps. If every line for a product were null-priced, the product would vanish from the ranking rather than appear with zero revenue, and a thoughtful candidate flags that choice: vanishing and zero are different business statements.
Common wrong answer
The most frequent failure uses ROW_NUMBER and a filter at five. Row number forces a unique sequence, so tied products are separated by an arbitrary or alphabetical accident and one of them is silently discarded at the boundary. The output looks clean — exactly five rows per category, every time — and that cleanliness is the danger, because nothing in the result signals that a tied product was dropped. A second common failure uses RANK while claiming to return five levels: with ties early, rank skips numbers, so rank five or better may return only three or four distinct performance levels. A third failure ranks before aggregating, ordering individual order lines by line revenue and calling the biggest single orders the top products, which answers a question nobody asked.
Worked variant
Variant: the stakeholder wants exactly five products per category, ties broken by number of distinct customers, then by product name, and is comfortable that a tied product may be excluded by that stated rule.
WITH product_revenue AS (Notice what changed and what did not. The aggregation is identical in spirit; the ranking function changed because the requirement changed, and the ordering inside the window now carries the full tiebreak chain so the result is reproducible run after run.
The follow-up an interviewer asks next
Expect: "Now do it for the last ninety days only, and tell me how the query changes if the table has a billion rows." A strong answer adds the date filter inside the aggregation step, before grouping, explains that filtering early shrinks the data every later step touches, mentions partitioning or clustering by date and category as the physical design that makes the filter cheap, and notes that a rolling ninety-day leaderboard is a natural candidate for a scheduled aggregate table rather than a full scan on every dashboard load. If returns exist in a separate table, expect that as the next twist, and be ready to discuss whether revenue is gross or net and who owns that definition.
Question 2 — Deduplicate an orders table: one row per order_id, most recent updated_at wins
Question
An upstream system has delivered some orders more than once, so an orders_raw table contains multiple rows for the same order_id, with differing updated_at timestamps and occasionally differing status or amount values as the order evolved. Produce a clean table with exactly one row per order_id: the row with the latest updated_at. If two rows for the same order share the same latest updated_at, break the tie deterministically and say how. Explain why SELECT DISTINCT does not solve this problem.
What they’re really testing
Deduplication is the most common real task in data engineering and the one most often faked with a keyword. The interviewer is testing whether you understand grain — one row per what — and survivorship: when duplicates disagree, which row deserves to survive and by what rule. They are also testing whether you can explain, crisply, the difference between identical duplicates, where any copy will do, and conflicting versions, where choosing wrong corrupts amounts and statuses downstream. Finally they are listening for determinism. A pipeline whose output depends on the order rows happened to be read is a pipeline that fails audits.
Full worked answer
Survivorship rule: latest updated_at wins; if still tied, the row with the highest amount wins as a stated arbitrary-but-deterministic rule, then the lowest status in alphabetical order purely to stabilise output — in a real setting you would replace these with the business rule, for example preferring a non-cancelled status, and you should say that replacing them is expected.
WITH ranked_rows AS (The pattern is worth naming because you will reuse it all through this set: partition by the key that defines a group, order by the survivorship rule inside the group, number the rows, keep number one. It works because ROW_NUMBER evaluates after the full row is available, so the choice can depend on any column. In a production pipeline this logic usually sits inside an insert into a curated table or a merge, and the raw table is retained so the decision can be audited and replayed. A careful engineer also emits a count of discarded rows per load and samples a few discarded versions, because a sudden spike in duplicates is an upstream incident announcing itself, and silently deduplicating it away destroys the evidence.
Now the DISTINCT explanation, which the question explicitly demands. DISTINCT removes rows that are identical in every selected column. These duplicate orders are not identical: their updated_at differs, and often status or amount differs too, precisely because they are versions of an evolving order. Selecting distinct rows therefore keeps every version, since each version differs somewhere. Selecting DISTINCT order_id alone is not valid in a query that also returns status and amount, because the database would have to guess which version's status to attach to the id — the very decision the question asks you to make explicitly. DISTINCT solves exact-copy duplication. Version duplication needs a ranking rule. Confusing the two is the single most revealing error in junior data engineering work.
Common wrong answer
Beyond the DISTINCT attempt, two failures recur. The first joins the raw table to a subquery computing MAX(updated_at) per order and keeps rows matching that maximum. That is close, and for many datasets it works, but when two versions share the maximum timestamp it returns both, violating the one-row guarantee the question states, and the candidate often does not notice because their test data had no tie. The second failure groups by order_id and takes MAX(status) and MAX(amount) independently, assembling a Frankenstein row whose amount comes from one version and status from another — a row that never existed in the source, which can pair a cancelled status with a pre-cancellation amount and produce finance numbers that reconcile with nothing.
Worked variant
Variant: keep the earliest row instead — the first version ever received — because an audit team wants the order as originally placed, and additionally retain a column counting how many versions were seen.
WITH ranked_rows AS (Only the ordering direction and the added window count changed. That economy is the point: once the partition-and-number pattern is understood, most survivorship variants are one-line changes with a clear business justification attached.
The follow-up an interviewer asks next
Expect: "The re-delivered file arrives tomorrow with the same orders again. How does your clean table avoid absorbing them twice, and how would you know if it did." The bridge to incremental loading is deliberate, and a strong candidate previews it here: the clean table has a primary key on order_id, loads are merges rather than blind appends, each load records source file and ingestion time, and a daily reconciliation compares row counts and summed amounts between raw arrivals and curated output. Hold the full design for Question 9, but signal here that deduplication within a batch and idempotency across batches are siblings, not strangers.
Consider a data engineer who inherits exactly this table on a Monday, discovers the duplication only because a revenue dashboard doubled overnight, and has to explain to a stakeholder why the fix is a ranking rule rather than a keyword. That scenario, composite and illustrative, is the working life this question simulates: the SQL is five lines, the engineering is the rule, the audit trail, and the explanation.
Question 3 — Customers who ordered in 3+ consecutive months
Question
From the same orders table, find customers who placed at least one order in each of three or more consecutive calendar months. A customer with orders in January, February and March qualifies. A customer with orders in January, February and April does not, because March is missing. Multiple orders inside one month count once. Return the customer, the start and end of each qualifying streak of length three or more, and the streak length in months. A customer may have more than one separate qualifying streak; return each streak as its own row.
What they’re really testing
This is the classic gaps-and-islands problem, and it tests a way of thinking more than a function. The interviewer wants to see whether you first collapse the data to the grain the question actually cares about — one row per customer per month — before attempting any cleverness, because candidates who work at order grain drown in noise. They want to see whether you know the island trick or can derive equivalent logic with row numbers and date arithmetic, and whether you handle the calendar honestly: months are not thirty-day intervals, December connects to January of the next year, and a month number alone, divorced from its year, creates false streaks across year boundaries. They are also testing whether you return streaks, with boundaries, rather than merely a yes-or-no flag, since the richer output is what a retention team can act on.
Full worked answer
Definition: a month counts when the customer has at least one order whose order_date falls inside it. A streak is a maximal run of consecutive calendar months each containing at least one order. Report streaks of length three or more.
WITH monthly AS (Unpack the trick in plain language, because in an interview you must narrate it, not merely type it. After collapsing to customer-months, each customer's months are numbered in order: first active month gets 1, second gets 2, and so on. If months are consecutive, then subtracting the row number, expressed in months, from the month itself yields the same value for every month in the run — the calendar advances by one month exactly when the counter advances by one, so the difference stands still. The moment a month is skipped, the calendar jumps by two while the counter moves by one, and the difference shifts, marking a new island. Grouping by that constant difference therefore groups each streak, and counting rows inside the group gives streak length. Depending on dialect, the subtraction is written with interval arithmetic, a months-between helper, or by converting year and month into a single sequential month index — year multiplied by twelve plus month — and subtracting the row number from that integer. The integer version is often the most portable in an interview, and offering it shows dialect awareness: sequential month index equals EXTRACT(YEAR FROM order_month) * 12 + EXTRACT(MONTH FROM order_month); consecutive months then differ by exactly one, including across a year boundary.
Test with a customer active in January, February, April, May and June. The islands split after February, the first streak has length two and is filtered out, the second has length three and is returned with April as its start. Test with December and the following January and February: the sequential index keeps them consecutive, where a naive month-number comparison would see twelve, one, two and declare chaos. Test with a customer who ordered eleven times in March and nowhere else: the distinct month collapse gives a streak of one, correctly, because frequency inside a month is not continuity across months.
Common wrong answer
The seductive wrong answer self-joins or uses LAG twice to check that the previous month and the month before that both exist, then reports the customer as qualifying. That detects the existence of a three-month window, which answers a simpler question, but it cannot report maximal streaks, double-counts overlapping windows inside a five-month run, and grows combinatorially uglier when the threshold becomes six months. Another frequent failure compares EXTRACT(MONTH) values, so November, December and a January from any year appear consecutive, and a customer active every December qualifies as a streak. A third failure forgets the distinct month collapse, counts orders rather than months, and promotes a customer with three orders in a single enthusiastic week into a three-month loyalist.
Worked variant
Variant: the retention team redefines the question — find customers whose longest streak is at least three, one row per customer, showing only that longest streak, and if two streaks tie for longest, show the most recent.
WITH monthly AS (Notice the reuse: the island machinery is untouched, and a second ranking layer, the same partition-and-number pattern from Question 2, selects the winning streak. Composability is a signal interviewers reward, because production SQL grows exactly this way, one verified layer at a time.
The follow-up an interviewer asks next
Expect: "What changes if we want consecutive weeks, or consecutive days with orders, and what if the threshold becomes ninety consecutive days for a subscription product." Strong candidates note that days make the arithmetic simpler — subtract the row number in days from the date — but the data volume grows and the definition of a day becomes timezone-sensitive, which matters for a global marketplace: an order late in Seattle and early in Singapore may share a UTC date while belonging to different local days. Weeks raise a definitional question first — calendar weeks starting Monday, or any rolling seven-day span — and the honest move is to ask which the business means before reaching for DATE_TRUNC. Expect also a performance push: on very large data, pre-aggregating to customer-month in a scheduled table turns an expensive repeated scan into a small read, and the streak logic then runs over the summary.
Question 4 — Second-most-recent order per customer, plus the gap to the most recent
Question
For every customer who has placed at least two orders, return their most recent order, their second-most-recent order, and the number of days between those two orders. Customers with exactly one order must be excluded, and you must say why your query excludes them rather than returning them with nulls. Order timestamps may tie; state a deterministic tiebreak. Return customer, most recent order id and timestamp, second-most-recent order id and timestamp, and the gap in days as a number that may be fractional or whole, with your choice stated.
What they’re really testing
Recency questions test whether you can think in ordered sequences per group rather than in aggregates. MAX(order_date) gives you a timestamp but not the order that owns it, and it certainly does not give you the second one or the gap between them. The interviewer is checking fluency with ROW_NUMBER or LAG for per-customer ordering, care about the exclusion rule — a business decision embedded in the data, since single-order customers are often the most interesting cohort and excluding them should be conscious, not accidental — and care about time arithmetic: what a day difference means when timestamps carry times of day, and whether a gap of twenty-six hours should report as one day, 1.08 days, or two calendar days. There is no universal right choice; there is only a stated choice, consistently applied.
Full worked answer
Definition: recency is by order timestamp descending, ties broken by the larger order_id, on the stated assumption that order_id increases over time within the source system — if that assumption is false in a given system, substitute any stable unique column and say so. Gap is measured in whole days between the calendar dates, with the fractional alternative noted below.
WITH ordered AS (The conditional aggregation pivot deserves explanation, because candidates often reach for two self-joins instead. After numbering, each customer has a row numbered 1 and a row numbered 2. Grouping by customer and taking the order id only from the row where the number equals 1, and only from the row where it equals 2, folds those two rows into one output row. MAX here is not finding a maximum in any meaningful business sense; it is the standard trick for extracting the single non-null value a CASE produces inside a group. The window count supplies the exclusion cleanly: customers whose partition size is one never survive the filter, so no null second order can leak into the result dressed as data.
An equally correct formulation uses LAG on the most recent row: number or lead the sequence so the latest order's row carries the previous order's timestamp beside it, then keep only the latest rows of customers with at least two orders. Mentioning the alternative in the room is valuable, not to show off, but because the LAG version generalises gracefully when the follow-up asks about the pattern of gaps across a customer's whole history rather than only the last interval.
On the gap definition, be explicit. DATE_DIFF in whole days between dates answers "how many midnights apart," which suits cohort reporting. Subtracting timestamps and extracting a fractional day answers "how much elapsed time," which suits service-level thinking. A twenty-six-hour gap is one whole calendar day in the first framing if the dates differ by one, and about 1.08 days in the second. Interviewers rarely mind which you choose; they mind deeply if you cannot say which you chose.
Common wrong answer
The classic failure computes MAX(order_date) per customer in one subquery, joins back to fetch the order, and then attempts a second MAX excluding the first via a correlated condition — a structure that collapses when two orders share the maximum timestamp, returning duplicates or dropping the customer entirely, and that becomes unreadable by the time the gap calculation is bolted on. Another failure returns single-order customers with a null second order and a null gap, then averages the gap column; since averages ignore nulls the number may even look reasonable, while the row count quietly contradicts the stated requirement. A third failure orders by date only, with no tiebreak, so re-running the same query on the same data can swap which of two simultaneous orders is called most recent — a nondeterminism that is poison in any pipeline feeding a report someone reconciles.
Worked variant
Variant: instead of only the last gap, return every consecutive-order gap for every customer, so an analyst can study reorder rhythm across the whole history, excluding each customer's first order, which has no predecessor.
WITH with_previous AS (LAG returns null exactly on each customer's first row in the ordering, so filtering nulls is not a hack here; it is the semantic statement "this order has no predecessor." That is the kind of null you design for, in contrast to the nulls you guard against.
The follow-up an interviewer asks next
Expect a stakeholder twist, the composite scenario this set promised: halfway through your answer, the interviewer says the business now wants the gap to the most recent delivered order, not the most recent placed order, and some orders are cancelled. Consider a data engineer who nods and filters blindly; the strong candidate pauses and redefines the population — cancelled orders excluded entirely, or counted as attempts — adds the status filter before the numbering step so the sequence is built only from qualifying orders, and points out that numbering after filtering is essential: if you number first and filter later, a cancelled latest order leaves you reporting the wrong "most recent." That ordering insight, filter then rank, generalises across this entire set and is worth stating as a principle in the room.
Question 5 — Seven-day rolling average of daily order counts
Question
Build a daily series from the orders table: one row per calendar day with the number of orders placed that day. Then compute, for each day, the average daily order count over that day and the preceding six days — a seven-day rolling average. Days with no orders must appear in the series with a count of zero rather than vanishing. Discuss what happens at the start of the series, where fewer than seven days of history exist, and how weekends should be treated, with a recommendation.
What they’re really testing
Two distinct skills are being examined, and candidates usually bring only one. The first is the window frame: the difference between a cumulative average, a default-frame average that quietly misbehaves, and a true rolling average defined by ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, or by a RANGE frame over dates. The second, rarer skill is series completeness. Aggregating orders by day produces rows only for days that have orders. A rolling average over that gappy series averages the last seven active days, which may span a month of calendar time, and reports a serene number that conceals a dead week. Interviewers plant this trap deliberately because production dashboards fall into it constantly. Generating a calendar spine and left-joining activity onto it is the professional answer, and volunteering it before being prompted is a strong signal.
Full worked answer
Definitions: the series runs from the first order date to the last order date in the data. Rolling average uses the current day plus the six preceding calendar days. For the first six days, the answer below averages the days available and exposes a days_in_window column so a reader can see the partial window rather than mistaking it for a full one. Weekend days are genuine days with genuinely lower counts and stay in the series; the reasoning follows the SQL.
WITH bounds AS (The calendar generation is the one place where dialects genuinely diverge — a generate-series function in some, a recursive expression or a standing calendar dimension table in others — and in an interview the right move is to write one clean version, name the mechanism in words, and note that most mature warehouses keep a permanent date dimension precisely so reports never improvise one. Everything after the spine is dialect-quiet: the left join attaches counts where they exist, COALESCE turns absence into an honest zero, and the frame does the rolling work. ROWS BETWEEN is chosen over RANGE here because the spine guarantees exactly one row per day, making row-based and date-based frames equivalent; if the spine were absent, a range frame over seven days would be the safer expression, and saying so demonstrates you understand why frames exist rather than reciting one.
On weekends, the recommendation is to keep them in the headline metric and, if the business cares, publish a companion view rather than silently altering the main one. Removing weekends changes the meaning of "seven-day" into "seven business days," stretches the window across nine or more calendar days, and makes week-on-week comparisons drift around holidays. E-commerce demand genuinely dips and surges by weekday — weekend behaviour is often the story, not noise. What a thoughtful analyst does add is context: a day-of-week breakdown beside the rolling line, or a comparison of each day against the same weekday four weeks earlier. If a stakeholder insists on a business-day version, build it as a clearly named second metric, never as a quiet edit to the first.
Common wrong answer
The canonical failure groups orders by day and applies the rolling frame directly to that result. On a quiet product or a new marketplace, missing days compress the window: the "seven-day" average may span three weeks, and a complete outage day disappears from the metric entirely — the worst possible behaviour for a monitoring number, since the incident erases its own evidence. A second failure omits the frame clause altogether, producing a cumulative average from the start of time that flattens every trend and still gets labelled rolling in the dashboard title. A third failure averages with integer arithmetic in dialects where that truncates, so a true average of 12.7 orders reports as 12 and small movements vanish.
Worked variant
Variant: the operations team wants a rolling average that requires a full window — no value at all until seven days of history exist — and a flag when today's count deviates from the rolling average of the prior seven days by more than thirty percent, as an early anomaly signal.
WITH bounds AS (Two details carry the lesson. The full-window requirement is enforced by the count of rows in the frame, not by eyeballing dates. And the deviation test compares today against the seven days before today, using a frame that ends one row back, because including today in its own baseline dilutes exactly the spike you are hunting. The division is guarded by checking the prior average is positive before dividing — the division-by-zero habit from the opening section, applied in the wild.
The follow-up an interviewer asks next
Expect: "This runs over five years of data for a dashboard refreshed hourly. How do you make it cheap, and how do you handle a day whose orders arrive late." Strong answers move the daily aggregation into a scheduled summary table keyed by date, let the rolling window read the small summary rather than the raw orders, and discuss late-arriving data explicitly: a restatement window of a few days is reprocessed on each refresh so corrections land, while older days are treated as closed. Mentioning that a rolling average is only as trustworthy as the completeness of its most recent day — a partial today will always look like a dip — marks you as someone who has watched a dashboard mislead a room.
Question 6 — Anti-joins: customers who never ordered, and warehouses with no recent shipments
Question
Two related requests. First, list customers who have never placed an order. Second, list warehouses that have had no shipments in the last ninety days, including warehouses that have never shipped at all. You have customers, orders with customer_id, warehouses, and shipments with warehouse_id and shipped_at. Write both queries safely, and explain the danger of using NOT IN for this pattern when the inner column can contain nulls.
What they’re really testing
The anti-join is a small patter
n with an outsized failure history. The interviewer is testing whether you can express "exists in A with no match in B" in more than one way, whether you understand the three-valued logic that makes NOT IN treacherous around nulls, and whether "no shipments in ninety days" is correctly understood as a different condition from "no shipments ever" — the warehouse that shipped sixty days ago must not appear, the one that shipped two hundred days ago must, and the one that never shipped must, which breaks several naive formulations that only look at warehouses present in the shipments table.
Full worked answer
For customers who never ordered, the left-join form is the clearest to read aloud:
SELECT c.customer_id, c.customer_nameThe equivalent NOT EXISTS form is often the best-performing and is immune to the null trap:
SELECT c.customer_id, c.customer_nameFor warehouses with no shipments in the last ninety days, aggregate shipments per warehouse first, then test the latest date — this handles never-shipped warehouses naturally because their latest date is null, meaning no shipment on record at all, which plainly satisfies "none in the last ninety days":
SELECT w.warehouse_id, w.warehouse_name, MAX(s.shipped_at) AS last_shipped_atNow the NOT IN trap, which the question demands in words. A condition of the form customer_id NOT IN (SELECT customer_id FROM orders) asks, for each customer, whether their id differs from every id in the list. If that list contains even one null — one order whose customer_id failed to load — then for every customer the comparison against the null evaluates to unknown rather than true, because nothing can be proven unequal to an unknown value. NOT IN therefore returns no rows at all. The query does not error. It returns an empty, confident, wrong answer, typically discovered weeks later when someone asks why the win-back campaign mailed nobody. NOT EXISTS and the left-join form do not share this failure, because they test for the existence of a matching row rather than comparing against a list of values. The safe rule to state in the room: prefer NOT EXISTS for anti-joins; use NOT IN only when you can guarantee the inner column is non-null, and even then, say the guarantee out loud.
Common wrong answer
Apart from the NOT IN collapse, the warehouse half catches candidates who write an inner join to shipments filtered to the last ninety days and then look for absence — an inner join can only return warehouses that shipped, the precise opposite population. Another failure filters shipments to recent ones inside the join condition of a left join but then adds a WHERE clause on a shipment column, accidentally converting the outer join back into an inner one and deleting the never-shipped warehouses the question explicitly includes. A third failure uses a per-row condition "shipped_at older than ninety days" and returns any warehouse with at least one old shipment, which is nearly every warehouse that has ever operated, instead of testing the latest shipment.
Worked variant
Variant: find customers who have an account and at least one order historically, but no order in the last one hundred eighty days — the lapsed cohort — and show each one's last order date and lifetime order count, ordered by last order date so the most recently lapsed come first.
SELECT c.customer_id, c.customer_name,The inner join is correct here precisely because the population changed: "at least one order ever" is a requirement, so existence is wanted, and the HAVING clause on the maximum date expresses "even the newest is old." Contrasting this with the anti-join version in the room shows you choose join types from the population definition rather than from habit.
The follow-up an interviewer asks next
Expect: "Your never-ordered list has two million customers and the marketing team wants to mail them. What do you check first." The engineering answer is suspicion: a list that large usually contains identity problems — guest checkouts never linked to accounts, customer ids minted by a new app version that orders under a different key, test accounts. A strong candidate profiles before mailing: how many of these customers registered yesterday versus three years ago, how many have any other activity signal, and whether a sample of them appear in orders under an email match despite missing the id join. Anti-joins find data quality faults as reliably as they find marketing cohorts, and naming that dual use is the insight the follow-up is fishing for.
Question 7 — Join fan-out: revenue double-counted through a one-to-many join
Question
A report joins an orders table, one row per order with an order_total column, to an order_shipments table, one row per shipment parcel, because many orders ship in multiple parcels. The report's total revenue is suddenly far higher than finance's figure, while the order count also looks inflated. Diagnose the problem precisely, demonstrate it with a tiny example, and rewrite the query so revenue is counted exactly once per order while still reporting the number of parcels per order. State the general rule you would teach a junior engineer from this incident.
What they’re really testing
Fan-out is the most expensive silent error in analytical SQL, because every number it produces is wrong by a different, invisible multiplier. The interviewer is testing grain literacy: do you ask, before any join, how many rows each side contributes per key, and do you predict the grain of the result. They are testing whether you can diagnose from symptoms — measures inflated, counts inflated, but ratios sometimes oddly stable — and whether your fix is structural, pre-aggregating the many side to the grain of the one side before joining, rather than cosmetic, sprinkling DISTINCT over the output and hoping. They are also testing whether you know that COUNT DISTINCT and SUM DISTINCT are bandages that fail the moment two orders legitimately share the same total.
Full worked answer
Diagnosis first. The orders side has grain one row per order. The shipments side has grain one row per parcel, so an order with three parcels appears three times there. Joining on order_id repeats every order row once per parcel. The order with a total of 120 and three parcels now contributes 360 to any sum of order_total, and contributes three to any plain count of rows. Nothing errored, no nulls appeared, and the inflation factor differs per order according to its parcel count, which is why the total is not a clean multiple of the truth and why nobody spotted it by glancing at a single row.
The fix: aggregate the many side down to one row per order before the join, so both sides share a grain and the join becomes one-to-one.
WITH parcels_per_order AS (Totals over this result are now safe: SUM(order_total) counts each order once because each order occupies exactly one row, and the parcel information survives as attributes of that row rather than as multipliers of it. The left join preserves orders that have not shipped yet, with a parcel count of zero rather than a disappearance. The general rule, stated the way you would teach it: before joining, write down the grain of each input and the grain you intend for the output; if a join would multiply rows, aggregate one side first or accept and design for the new grain deliberately. A useful mechanical check is to compare row counts before and after a join in testing — a join that grows the left side's rows has explaining to do.
Common wrong answer
The reflex fix is SELECT DISTINCT, which does nothing here because the joined rows genuinely differ in their shipment columns, so nothing is a duplicate row even though the order measure is repeated. The more dangerous fix is SUM(DISTINCT order_total), which undercounts whenever two different orders share the same total — two unrelated orders of 49.99 collapse into one contribution — trading an overcount for a subtler undercount that survives casual testing. A third failure counts orders with COUNT(order_id) after the fan-out and reports parcel-weighted order volumes to leadership, a number with no meaning at all. Each wrong fix shares a root: treating a grain problem as a duplication problem.
Worked variant
Variant: the report now also needs total shipped weight per order from the same shipments table, and separately needs a parcel-level report where each parcel is a row but order_total must still not be summed naively. Show the thinking for both.
For the order-level report, the pre-aggregation simply grows: add SUM(weight) inside parcels_per_order, and the join remains one-to-one. For the parcel-level report, the grain is intentionally one row per parcel, so order_total will repeat on the page; the honest design labels it as an attribute repeated for reference, computes any revenue total in a separate order-grain query, and if a total must appear on the parcel report, allocates revenue across parcels by weight or declares it non-additive in the metric definition. Naming a measure non-additive — it may be displayed but never summed at this grain — is a mature move that prevents the next engineer from reintroducing the bug with confidence.
WITH parcels_per_order AS (The follow-up an interviewer asks next
Expect: "How would you stop this class of bug across a whole team's codebase, not just in this query." Strong answers leave the single query and talk about systems: documented grain in table contracts, a semantic layer where measures are defined once against a stated grain, review checklists that ask for pre- and post-join row counts, automated tests that reconcile report totals against a finance-controlled figure, and lint-style rules flagging sums over columns from the one side of a known one-to-many relationship. Consider a data engineer who has been paged twice for the same fan-out in different dashboards; that engineer stops fixing queries one at a time and starts fixing the layer the queries are written against. Interviewers ask this follow-up to find that engineer.
Question 8 — Second-highest distinct value, in a data-engineering frame
Question
For each warehouse, find the second-largest daily shipment volume — that is, take each warehouse's daily shipment counts, consider the distinct values those counts take, and return the second-highest distinct count, along with the date or dates on which the largest count occurred. Warehouses whose daily counts take only one distinct value have no second-highest and must be reported explicitly as such rather than omitted or given a fabricated value. Handle ties and nulls deliberately and explain your choices.
What they’re really testing
The second-highest salary problem is a classroom cliché, and this question is its grown-up form. Moving it to warehouse grain with distinct values, ties and a completeness requirement exposes whether a candidate memorised a trick or understands the pattern. The tests are: distinctness before ranking, because the second-largest value is not the second row; partition-level completeness, because silently dropping warehouses with no answer corrupts any downstream coverage check; and honest null semantics, because "no second value exists" is information that deserves a labelled row, not an absence that looks like a loading failure.
Full worked answer
Definitions: daily volume is a count of shipments per warehouse per calendar day, over days present in the shipments data. Distinct values of that count, per warehouse, are ranked descending. The answer returns one row per warehouse always: the second distinct value where it exists, a null second value with an explanatory status where it does not, and the earliest date achieving the maximum.
WITH daily AS (A cleaner classroom expression of the ranking core, which you should be able to produce without the reporting scaffolding, ranks distinct daily volumes directly: take the distinct warehouse-and-volume pairs from the daily aggregation, dense-rank them within warehouse, and keep rank two. DENSE_RANK is again the considered choice, because ranks must step across distinct values without skipping; RANK would also step over values correctly in the presence of duplicate rows only if duplicates were removed first, and ROW_NUMBER would mistake the second occurrence of the maximum for a second-highest value, the single most common error on this pattern. That error deserves emphasis: if a warehouse shipped 500 on three different days and 420 on one day, the second-highest distinct value is 420, while a row-number approach proudly returns 500. The output looks plausible, the warehouse looks consistent, and the analysis of peak capacity built on it is fiction.
Nulls enter through two doors. A warehouse with no shipments at all has no daily volumes; the left join from the warehouse list keeps it visible, with null largest and second values and a status that should distinguish "no shipments" from "one distinct value" in a fuller production version — refining that status into separate labels is a good sentence to offer. And within aggregation, days are generated only from shipments, so a zero-shipment day never enters the distinct values at all; if the business instead wanted zero counted as a daily volume, a calendar spine per warehouse, as in Question 5, changes the answer materially for low-volume sites, and a strong candidate names that fork rather than choosing silently.
For the dates of the maximum, a compact approach joins the summary back to the daily aggregation on warehouse and largest volume and takes the minimum date, or lists all achieving dates with a string aggregation where the dialect supports it. Reporting every peak date versus the first is a presentation choice; either is defensible when stated.
Common wrong answer
The memorised-trick failure writes a query for the second-highest value in the whole table — forgetting the per-warehouse partition entirely — because the classroom version had no grouping. The second failure, described above, ranks rows instead of distinct values. The third failure uses a correlated MAX-below-MAX formulation per warehouse that is logically sound but quietly omits warehouses with a single distinct value, since no value exists below their maximum; the question explicitly counts that omission as wrong, and it is the difference between an exercise answer and a reporting answer. A fourth failure returns zero instead of null for the missing second value, fabricating a measurement — zero shipments as a second-highest daily volume — that a capacity planner could act on.
Worked variant
Variant: generalise to the third-highest distinct daily volume per warehouse, and then to an arbitrary N supplied by an analyst, without rewriting the logic each time.
The dense-rank formulation generalises for free: keep rows whose value rank equals the requested N, with warehouses lacking an Nth value still surfaced by the outer join and status labelling. State the parameter as a literal or a bind variable in place of the rank constant. This is worth saying in the room because it contrasts with the MAX-below-MAX style, which must be nested once per level and becomes unreadable by N equals three — a concrete example of choosing a formulation for how it extends, not only for whether it runs.
WITH daily AS (The follow-up an interviewer asks next
Expect: "Why do you distrust the second-highest as a planning metric, and what would you report instead." This is a judgement probe disguised as a SQL follow-up. Peak and second-peak values are fragile statistics, dominated by one-off events — a promotional surge, a backlog cleared after an outage — and a thoughtful engineer supplements them with percentiles of daily volume, a ninety-fifth percentile day, or the average of the top five days, each of which describes sustained near-peak load more honestly. Showing that you can both compute the requested number and question its use is exactly the combination loop interviews are calibrated to reward.
Question 9 — Idempotent incremental load: re-running must not double-count
Question
A daily pipeline loads yesterday's orders from a staging table into a curated orders table. Last night the load failed halfway and the on-call engineer wants to simply run it again, but a plain insert would duplicate every row that already landed. Design the load so that running it any number of times with the same staging data produces the same curated table as running it once. Handle the case where a staged row is a corrected version of an existing order — it should update, not duplicate and not be ignored. Explain your design in dialect-neutral terms, then show one concrete implementation.
What they’re really testing
Idempotency is where SQL interviews become engineering interviews. The tester is checking whether you think in terms of keys and state rather than inserts and hope: does the curated table have a declared primary key, does the load use that key to decide between insert and update, and does the batch carry enough metadata — load timestamps, source identifiers — to audit what happened. They are also testing failure imagination. Loads fail halfway, files arrive twice, schedulers retry automatically, and an upstream correction restates last Tuesday. A design that only works when nothing goes wrong is not a design. Finally, they want the dialect-neutral idea separated from vendor syntax, because warehouses differ in whether they offer a merge statement, an insert-on-conflict clause, or neither, and an engineer who understands the idea can implement it anywhere.
Full worked answer
The idea, stated without any vendor's vocabulary, has four parts. One, identity: every order is identified by its primary key, order_id, and the curated table enforces uniqueness on that key so duplication is structurally impossible rather than merely unlikely. Two, decision per key: for each staged row, if no curated row carries that key, insert it; if a curated row carries the key and the staged row is newer or different, replace the curated values; if it is identical, do nothing. Three, batch discipline: the load processes a defined slice — a day, a file, a batch identifier — and records that it did so, so a retry can be recognised as a retry. Four, deletion honesty: if the source can delete or cancel by removal, the design must say how absence is handled, because a merge driven only by what is present can never propagate a deletion; many pipelines handle this with status flags or periodic full reconciliation rather than pretending the problem away.
One concrete implementation uses the widely available merge pattern:
MERGE INTO curated_orders AS targetRun this twice with identical staging and the second run matches every row, finds no newer timestamp and no differing values, updates nothing, inserts nothing, and the curated table is byte-for-byte the outcome of one run. That is the definition of idempotent, and you should walk the interviewer through the second run explicitly, because the second run is the entire point. The updated_at guard matters as much as the key: without it, a stale restated file arriving late could overwrite fresher data with older values, an idempotent load that is safely, reproducibly wrong. Where a dialect offers insert-on-conflict phrasing instead, the shape is the same decision — insert, and on key conflict update the listed columns under the same freshness condition. Where neither exists, the portable fallback is two steps inside one transaction: update existing keys from staging, then insert staged rows whose keys do not exist, tested with the anti-join pattern from Question 6. Naming that fallback shows the idea owns the syntax, not the reverse.
Operational details separate strong answers from adequate ones. Wrap the load in a transaction where the platform supports it, so a halfway failure leaves the previous good state rather than a torn one. Make the staging slice deterministic — yesterday by event date in a stated timezone — so a retry selects the same rows. Log rows inserted, rows updated and rows unchanged per run, and alert when a supposedly routine reload updates an implausible share of the table, because that pattern means upstream restated history and someone should know. And keep the raw arrivals immutable, so any curated state can be rebuilt by replaying, which is the ultimate insurance when logic itself turns out to have been wrong.
Common wrong answer
The most common failure is the hopeful append: insert everything from staging and rely on the scheduler never retrying and files never repeating — a design whose correctness depends on nothing ever going wrong in a system whose defining trait is that things go wrong. The second failure deletes the target day's rows and reinserts them, which is a legitimate pattern in some partitioned designs but is offered here without its conditions: it destroys corrections that arrived for that day through other paths, it cannot update a row whose event date itself was corrected into a different day, and if the delete commits and the insert fails, the table is left emptier than before — a worse failure state than duplication. The third failure deduplicates only within staging, the Question 2 pattern, and never checks against the curated table, so it is perfectly prepared for a batch it has never seen and helpless before the retry that actually happened.
Worked variant
Variant: the source is an append-only event stream rather than a daily file — order created, updated and cancelled events arrive throughout the day, sometimes out of order, and the curated table must reflect the latest known state per order at any moment.
The design extends rather than changes. Stage events with their event timestamps and a source sequence where available. The merge's freshness condition becomes a comparison on event time, with sequence as tiebreak, so an out-of-order older event updates nothing. Cancellations arrive as explicit events with a status, which solves the deletion honesty problem by design: nothing is inferred from absence. A periodic reconciliation job still compares counts and amount sums per day between a replay of the event log and the curated table, because even a correct design deserves a check that would catch an incorrect implementation. State plainly that exactly-once delivery is not being assumed from the stream; the load is built to absorb duplicates, which is the achievable and testable property.
The follow-up an interviewer asks next
Expect: "How do you test idempotency before this reaches production, and how do you prove it after." The answer is a small, concrete ritual: load a batch, snapshot row counts and checksums of key measures, load the same batch again, load a corrected copy of it, load an older copy after the corrected one, and assert the table after each step — same after the repeat, corrected after the correction, still corrected after the stale copy. In production, the proof is continuous: uniqueness constraints that would scream if violated, per-run insert and update counters trended over time, and the reconciliation totals. Consider a data engineer who automates exactly that ritual after one painful double-counted weekend; the follow-up is looking for the engineer who builds the ritual before the weekend, on the strength of imagination alone.
Question 10 — Duplicate-charge detection: same customer, same product, within five minutes
Question
A payments team suspects some customers are being charged twice for the same product because of a retry bug at checkout. From a charges table — charge_id, customer_id, product_id, amount, charged_at, and a payment status — find suspected duplicate pairs: two different charges, same customer, same product, same amount, charged within five minutes of each other, where both charges succeeded. Each pair should be reported once, not twice, and a cluster of three rapid charges should be handled sensibly. State what you would do with the results, because detection is not the same as refunding.
What they’re really testing
This question tests self-join discipline and temporal reasoning together. Joining a table to itself is conceptually simple and practically treacherous: without a strict inequality on the key or the timestamp, every charge pairs with itself and every pair appears twice in mirror image. The interviewer is also testing definition care — amount equality as part of the duplicate signature, succeeded status only, the five-minute window measured how exactly — and engineering judgement about false positives, because a customer may legitimately buy the same product twice in five minutes, as a gift or a second household item, and an automated refund based on a naive query would steal a genuine purchase back. The last part of the question, what you do with results, is not decoration; it is the difference between an analyst who finds pairs and an engineer who designs a safe process around them.
Full worked answer
Definition: a suspected pair consists of two distinct successful charges sharing customer, product and amount, whose timestamps differ by more than zero and at most five minutes. Report each unordered pair once by requiring the first charge's identifier to be smaller than the second's, or equivalently its timestamp earlier with identifier as tiebreak.
SELECTThat final ordering condition as sketched needs care in the room, and the cleaner canonical form pairs on time alone: require the second timestamp strictly later than the first, which by itself guarantees each unordered pair appears once when timestamps differ, and add the identifier comparison only to resolve pairs with identical timestamps, where two charges in the same second cannot be ordered by time. A tidy way to write the whole predicate is to require the pair ordering (charged_at, charge_id) of the second charge to exceed that of the first, while the window condition bounds the time gap. Presenting it that way — an ordering key for uniqueness, a window for closeness — shows the two concerns separated rather than tangled, and an interviewer will usually accept the tuple description with one dialect's concrete rendering.
Clusters deserve their sentence. Three charges at minute zero, two and four produce three pairs under pair logic: zero-two, zero-four and two-four. For an investigation queue that may be exactly right, since each pair is evidence. For a refund workflow it overstates, because at most two of the three could be duplicates of an original. A strong answer therefore offers a second view that groups rapid charges into clusters per customer, product and amount — using the same gaps-and-islands thinking as Question 3, where a new cluster starts when the gap from the previous charge exceeds five minutes — and reports cluster size and total amount at risk. Pairs for forensics, clusters for operations: naming which output serves which consumer is senior-level clarity.
What happens next matters as much as the query. Suspected pairs go to a review queue joined with fulfilment data — was one product delivered or two, was a second licence issued, did the customer contact support — because delivery evidence separates a retry bug from a genuine second purchase better than any timestamp rule. Refunds, when approved, reference the specific charge identifier through the payment provider, are recorded against the detection run that found them, and the detection query excludes charges already refunded or already reviewed so the same pair is not actioned twice; notice that this bookkeeping is itself the idempotency discipline of Question 9 wearing a payments coat. Volume monitoring closes the loop: a daily count of suspected pairs is a health metric for the checkout system, and a spike is an incident signal even before any individual refund is processed.
Common wrong answer
The unguarded self-join is the headline failure: no inequality condition, so each charge matches itself, the output is dominated by self-pairs with a gap of zero, and genuine pairs drown. The mirror failure keeps both directions of every pair, doubling the apparent exposure in any sum of amounts at risk — a number that will be quoted in a meeting, which makes the error consequential rather than academic. A third failure uses a five-minute window on clock-minute boundaries, grouping by the truncated minute, so charges at 10:00:50 and 10:05:10 — twenty seconds past the boundary logic in one framing, over five minutes apart in truth — are mishandled in both directions: near charges split across a boundary are missed, distant charges inside one block are flagged. Windows must be computed from actual timestamp differences, never from bucketed labels. A fourth failure omits the amount or status conditions and flags subscription renewals, failed-then-succeeded retries that never double-charged anyone, and legitimate repeat purchases, flooding the review queue until the team stops trusting it — the operational death of any detection system.
Worked variant
Variant: the retry bug is fixed, and the team now wants a daily monitoring query with a looser net for trend purposes: same customer and product within ten minutes regardless of amount, because a corrected retry sometimes charges a slightly different amount after a price update, reported as counts per day rather than pairs.
SELECTThe loosened definition is labelled a monitoring metric, not evidence against any individual charge, and its threshold for alerting is set from its own history rather than from the stricter forensic definition. Keeping the two definitions distinct, with distinct names, prevents the slow drift in which a trend query quietly becomes the refund criterion — a governance point worth making aloud, because interviewers who have operated payments systems have all watched that drift happen somewhere.
The follow-up an interviewer asks next
Expect: "The charges table has two billion rows. Your self-join is too slow. What do you do." Begin with the observation that the join is already highly selective in intent but not necessarily in execution: partition or sort by customer and product so candidate pairs are physically near each other, restrict the scan to a recent window for the daily run since pairs older than the window cannot form, and consider a window-function formulation — comparing each charge only to the previous charge for the same customer, product and amount using LAG — which is sufficient when duplicates are immediate retries and reduces the comparison to neighbours rather than all pairs within five minutes. Be honest about the trade: the neighbour formulation can miss a pattern where a legitimate charge intervenes between the two duplicates, and choosing between the formulations is a judgement about what the bug actually does, informed by the data, not a performance reflex. That honesty, again, is the answer inside the answer.
Appendix A — Tiny test datasets that break the wrong answers
Interviewers are fond of asking, "how would you test that query," and candidates are fond of answering, "I would check it against some sample data." The difference between a vague promise and a demonstration is having the sample data already in your head. This appendix works through the smallest dataset that exposes each major trap in the set. Build the habit of constructing these tables before you trust any answer, including your own under time pressure, and especially including answers printed in preparation material, this set included. Reasoning over five rows you fully understand teaches more than scanning five thousand rows you do not.
Start with ranking. Imagine a single category, games, with four products whose total revenues after aggregation are as follows in words: product Alpha at nine hundred, product Beta at seven hundred, product Gamma also at seven hundred, and product Delta at four hundred. Ask for the top two by dense rank. The correct result contains three products, because Alpha holds rank one and both Beta and Gamma share rank two. A row-number answer contains exactly two products and discards one of the tied pair for a reason that has nothing to do with revenue — usually the accident of internal ordering. Now ask for exactly two products with ties broken by units sold, and suppose Gamma sold more units than Beta. The correct result is Alpha and Gamma, and notice that the identity of the second product changed even though the revenue figures did not move at all. That tiny table teaches the deepest lesson in Question 1: a top-N question is incomplete until the tie rule is part of the question, and two interviewers can set the same words and mark different answers correct because they hold different tie rules in mind. Stating yours converts a lottery into a demonstration.
For deduplication, imagine order number 42 arriving in three versions. The first version, timestamped Monday morning, shows a status of placed and an amount of fifty. The second, timestamped Monday afternoon, shows a status of paid and the same amount. The third, timestamped Tuesday, shows a status of shipped and an amount of fifty-five because a shipping charge was added. The correct clean table contains exactly one row for this order, the Tuesday shipped version, because the survivorship rule is latest wins. A distinct-based answer contains all three rows, because no two are identical, and any revenue sum over it counts the order three times at three different amounts, producing a figure that corresponds to nothing that ever happened. A group-by answer taking the maximum of each column independently produces a row that happens to be correct in this particular example — shipped and fifty-five are both maxima — which is precisely why this trap survives casual testing. Change one figure to expose it: suppose the Tuesday version corrected the amount downward to forty-five after a discount was applied. Now the column-wise maximum assembles shipped with fifty, a pairing that never existed, and the discount vanishes from the record. The lesson generalises far beyond this set: a test dataset that cannot distinguish the right method from the wrong method is not a test, and candidates who construct adversarial examples — examples chosen specifically to separate methods — are doing real engineering, not examination decoration.
For consecutive months, the separating dataset needs a year boundary and a gap in the same customer history. Imagine customer Asha ordering in November of one year, December of the same year, and January of the next, then falling silent in February, then ordering in March, April and May. The correct answer returns two streaks of three: November through January, and March through May. A method comparing month numbers sees eleven, twelve and one and, depending on its arithmetic, either breaks the first streak at the boundary or welds unrelated Decembers and Januaries from different years into phantom streaks elsewhere in the data. A method that forgets to collapse multiple orders within a month sees nothing wrong here, so add a second customer, Bruno, who ordered five times in March and never in any other month. The correct answer excludes Bruno entirely. A method counting orders instead of active months promotes him to a five-period loyalist. Two customers, nine rows of invented data, and three distinct wrong methods are separated from the correct one. That is the economy of a good test.
For recency and gaps, the separating dataset is a customer with two orders placed on the same day. Suppose customer Chen places order 1001 at nine in the morning and order 1002 at nine in the evening, and no other orders ever. The most recent order is 1002 under any sane tiebreak, the second-most-recent is 1001, and the gap is either zero whole days by calendar-date difference or half a day by elapsed time. Both gap figures are defensible; what is not defensible is a query whose answer changes between runs because it ordered by timestamp at day precision with no tiebreak, or a query that excludes Chen because a self-join on strictly earlier dates found no predecessor at a different date. Add a second customer, Dara, with a single order, and the dataset now also tests the exclusion rule: Dara must be absent from the result, present in a variant that studies single-order customers, and the candidate should be able to flip between those populations by changing one stated condition rather than by rewriting the query in a panic.
For rolling averages, the separating dataset is a week with a hole in it. Suppose daily order counts from Monday onward run ten, twelve, then nothing at all on Wednesday because the storefront was down, then fourteen on Thursday and sixteen on Friday. The honest series contains Wednesday with a count of zero, and any rolling average covering that span is dragged downward by the outage, as it should be. The gappy series, built only from days present in the orders table, contains no Wednesday at all, and a frame reaching back over the previous rows averages days that are not the previous calendar days. If the business question is about demand, Wednesday's zero is arguable — perhaps demand existed and the systems failed to capture it, which is a measurement conversation worth having explicitly. If the question is about fulfilled order volume, the zero is simply true. Either way, the candidate who notices the missing row and forces the conversation is ahead of the candidate whose average glides over the incident. In operations, the most dangerous dashboard is the one that looks calm during an outage, and this five-day example is the smallest possible demonstration of why.
For anti-joins, one null does all the work. Imagine three customers with identifiers one, two and three, and an orders table containing orders for customer one and one order whose customer identifier failed to load and is null. The correct never-ordered list contains customers two and three. The NOT IN formulation contains nobody at all, because the null in the inner list poisons every comparison. No elaborate dataset is needed; the entire three-valued-logic lesson fits in five rows, and a candidate who can reproduce it from memory will never ship the bug. For the warehouse half of the pattern, imagine three warehouses: one that shipped yesterday, one whose last shipment was two hundred days ago, and one that has never shipped. The correct no-recent-shipments list contains the second and third. Formulations that begin from the shipments table cannot see the third warehouse at all, and formulations that test whether any shipment is old wrongly include the first warehouse as soon as it accumulates any history. Three warehouses separate at least three wrong methods.
For fan-out, two orders suffice. Order one totals one hundred and ships in three parcels. Order two totals one hundred and ships in one parcel. The true revenue is two hundred. After a naive join to the parcels table, summing the order total returns four hundred — order one counted thrice and order two once — while counting rows returns four orders that never existed. The SUM DISTINCT bandage returns one hundred, because the two equal totals collapse into one, which is wrong in the opposite direction and by a different amount. A dataset in which the two orders had different totals would have hidden the bandage's flaw entirely, which is why the equal totals are the point of the example. When you design test data, make the values collide wherever a wrong method depends on values being unique, make timestamps tie wherever a method depends on ordering being total, and make populations include the empty case wherever a method depends on presence. Those three design rules generate nearly every useful test in this set.
For idempotency, the test is a sequence rather than a dataset: load a batch, record the row count and the sum of amounts, load the identical batch again and demand both figures stand still, then load a corrected batch in which one order's amount has changed and demand that exactly one row differs, then attempt to load the original stale batch a final time and demand that the correction survives. Four steps, each with a predicted outcome stated before it runs. Engineers who state predictions before running tests learn from failures; engineers who merely observe outcomes can rationalise any result. For duplicate charges, the separating dataset is a cluster: three successful charges for the same customer, product and amount at minutes zero, two and four, plus a fourth charge at minute forty that is plainly a separate purchase, plus a fifth at minute three for a different amount that should not join the cluster under the strict definition. Pair logic returns three pairs from the cluster and correctly ignores the other two charges; a candidate who expects exactly one duplicate row has misunderstood the output shape, and a candidate whose query returns the forty-minute charge has a window bug. Walking that example aloud, pair by pair, is one of the most convincing performances available in this entire set, because it shows forensic care and arithmetic care at the same time.
Appendix B — How to narrate your way through a live round
The content of a live SQL round is only half of what is being assessed. The other half is the experience of working with you, compressed into forty-five minutes, and narration is where that experience is created. This is not an argument for theatrical commentary. It is an argument for making your decision points audible at the moments you reach them, so the interviewer can follow, correct and trust the process even when a detail goes wrong.
Open every question with clarification, and make the clarification specific rather than ceremonial. Asking "can you tell me more about the data" wastes the interviewer's patience. Asking "is the orders table at order grain or order-line grain, can updated_at tie between versions, and should cancelled orders count in this population" tells the interviewer exactly how you think and frequently surfaces a hidden condition that changes the answer. Candidates worry that questions signal weakness. Interviewers, in their public accounts of the process, consistently describe targeted clarification as the behaviour of engineers they want on incidents, because production requirements arrive ambiguous in exactly this way and the expensive failures come from unasked questions.
State the definition you are adopting before you write code, in one or two sentences, and invite correction. "I will treat top five as five dense rank levels with ties included; say if you want exactly five rows." That sentence takes ten seconds and does three kinds of work: it proves you noticed the ambiguity, it gives the interviewer a cheap opportunity to steer you onto their intended definition, and it creates the reference point against which your follow-up handling will be judged when the definition changes later. Similarly, announce your population exclusions and your null assumptions as you reach them, briefly, the way a pilot announces configuration changes rather than narrating every switch.
While coding, narrate layers rather than keystrokes. The useful commentary sounds like this: first I collapse to one row per customer per month, because the streak logic operates at that grain; then I number the months within each customer; then the island key falls out of subtracting the counter from the calendar position. An interviewer hearing that structure can interrupt productively — "assume the monthly table already exists" — and can award credit for a correct architecture even if a function name escapes you. An interviewer hearing silence, followed by a finished query, has only the output to judge, and a single wrong row then carries the whole verdict. There is also a selfish reason to narrate in layers: your own ear catches errors your eye skims over. Saying "this join can multiply rows" out loud has stopped more fan-out bugs than any checklist.
When you finish a draft, test it aloud against a tiny case before declaring it done, choosing the case from the trap list for that pattern: the tie at the boundary, the single-order customer, the null in the list, the missing Wednesday. Interviewers notice the choice of test case as much as the test itself, because it reveals which failure modes live in your head rent-free. If the test exposes a flaw, name it plainly, locate it to a layer, and repair that layer rather than patching the output. The sentence "my ranking step is fine; my population is wrong because I numbered before filtering out cancellations" is one of the strongest sentences a candidate can produce in a loop, since it demonstrates diagnosis, vocabulary and composure in a single breath.
Close each question by offering the engineering horizon in two or three sentences: how this behaves at scale, what you would monitor, what definition a stakeholder might revisit. Do not ramble into a lecture; the offer is a door, and if the interviewer wants the follow-up discussion they will walk through it. This closing habit is what transforms the impression left by the round from "candidate who can write queries" to "engineer I would hand a table to," and in a loop calibrated on hire signals, that transformation is the whole game.
Finally, manage the clock visibly. If a question is consuming more than its share, say what remains and propose an order: "I have the core logic; the tiebreak and the null guard remain, and I will add them in that order." Candidates fear that acknowledging incompleteness loses marks. In practice, an interviewer who can see a correct plan with two named steps remaining can credit the plan, while an interviewer watching silent struggle cannot distinguish a nearly finished answer from a lost one. Visibility is not confession. It is project management in miniature, performed under the exact conditions the job will impose.
Appendix C — Scale, performance and the billion-row conversation
Sooner or later in a loop, usually as a follow-up rather than a headline question, the interviewer enlarges the room: the orders table no longer fits on a laptop, it holds billions of rows across years, the dashboard refreshes hourly, and other teams query the same tables while your pipeline is running. Candidates who have only ever optimised for correctness sometimes treat this turn as a change of subject. It is not. It is the same questions viewed at the scale where physical design decides whether the logically correct query finishes before the business meeting it feeds. This appendix collects the scale reasoning that applies across the set, so you can deploy the relevant piece inside any question rather than memorising a separate performance script.
The first principle is reduction order. Every operation in a query either shrinks the data, preserves it, or grows it, and the cheapest query is arranged so shrinking happens early and growing happens late, if at all. Filtering to ninety days before aggregating means the aggregation chews on ninety days. Filtering after aggregating over five years means the engine faithfully computed five years of product revenue in order to discard most of it in the final step — logically identical, operationally absurd. Joins obey the same arithmetic: aggregate the many side down before joining, as Question 7 requires for correctness, and you simultaneously discover that pre-aggregation is also the performance fix, because the join then moves one row per key instead of one row per parcel. Correctness and cost are often the same decision described twice, and pointing that out in an interview signals that your instincts were formed on real systems.
The second principle is that layout beats cleverness. A table partitioned by date and clustered or sorted by the keys it is usually filtered and joined on will outperform a beautifully written query against a table laid out at random. When an interviewer asks how you would speed up the rolling-average question over five years, the strongest answers mention the physical facts before the syntactic ones: a daily summary table maintained incrementally, so the window function reads roughly eighteen hundred rows instead of billions; partition pruning, so a ninety-day question touches ninety days of storage; and avoiding functions wrapped around filter columns in ways that prevent the engine from using its layout, such as truncating every timestamp in a predicate when a range comparison on the raw column would prune identically. None of this requires vendor-specific folklore. It requires the habit of asking where the bytes live and how many of them your query proposes to move.
The third principle concerns window functions specifically, because this set leans on them heavily. A window function sorts its partitions, and sorting is the expensive guest at the feast. Partitioning by customer over a billion-row orders table means the engine must gather each customer's rows together wherever they currently lie. That cost is sometimes unavoidable — recency and streak questions are inherently per-customer — but it can be paid once, in a maintained intermediate table, rather than repeatedly by every analyst who asks a per-customer question. It also means that adding an unnecessary second window pass, or ordering by columns the question does not need, is not a stylistic wart but a measurable expense. In the room, the sentence that lands is a comparative one: this formulation sorts once, that formulation sorts twice, and at this table size the difference is the difference between a refresh that lands and one that queues.
The fourth principle is incremental thinking as a default, and Question 9 is its fullest expression in the set. Full recomputation is a legitimate strategy at small scale and a slow-motion outage at large scale. The mature pattern is consistent: process the slice that changed, merge it into the curated state by key under a freshness rule, keep the raw history immutable so any state can be rebuilt, and reconcile periodically against an independent total so that silent drift has somewhere to be caught. Late and corrected data are not exceptions to this pattern; they are its reason for existing. A pipeline designed for a world in which yesterday's file is final will be redesigned, urgently and in public, the first time finance restates a quarter. Interviewers who probe scale are often really probing whether you have internalised that restatements are normal life.
The fifth principle is restraint with expensive conveniences. Counting distinct values across billions of rows, joining on expressions rather than stored keys, and scanning a wide table to return three columns all carry costs that a shared warehouse bills to everyone in the form of queues. Naming a cheaper approximation where the business can tolerate one — an approximate distinct count for a traffic dashboard, an exact count reserved for billing — shows commercial judgement, provided you label the approximation as loudly in the output as you do in the interview. The unforgivable version is the silent approximation, which is a data quality incident wearing a performance costume.
Weave these principles into answers sparingly. One scale remark, well aimed at the question in front of you, is worth a five-minute lecture on warehouse architecture, and the interviewer will extend the topic themselves if your remark convinces them the territory is yours. The aim, as with everything in this set, is to sound like someone whose queries have survived contact with production — because they ask production's questions before production gets the chance.
Appendix D — From this set to the wider loop
SQL is the centre of this set, but a data engineering loop surrounds it with neighbouring territories, and a candidate who sees the connections prepares more efficiently than one who treats each round as a separate examination. This closing appendix maps the bridges, briefly, so your next study sessions build outward from strength rather than starting cold in four directions at once.
The bridge to data modelling is grain, and you have been crossing it all along. Every question here began by stating the grain of its tables and the grain of its answer; a modelling round simply asks you to design the tables themselves — what a fact is, what a dimension is, how a customer's address history is preserved when it changes, how an order's status evolution is represented without destroying the record of what was believed at the time. If Questions 2 and 9 felt natural, modelling will too, because survivorship and slowly changing history are the same problem at different altitudes. Prepare by taking the orders world of this set and designing it from nothing: name your entities, choose your keys, decide which changes overwrite and which accumulate, and be ready to defend each decision against a stakeholder who wants reports that reconcile across time.
The bridge to pipeline design is Question 9 generalised: sources, staging, curated layers, orchestration, retries, backfills, and the monitoring that tells you a load landed correctly before a stakeholder's dashboard does. The bridge to data quality is the population thinking of Questions 5 and 6: completeness checks that notice a missing day, validity checks that notice a null where a key belongs, reconciliation that notices when two systems disagree about revenue — and the professional temperament to treat every failed check as information rather than embarrassment. The bridge to the behavioural rounds, which candidates chronically underrate, is the narration discipline of Appendix B applied to your own history: structure an incident story the way you structure a query answer, with the situation defined precisely, your decisions named in order, the wrong turns owned without flinching, and the measurable outcome stated at the end.
None of these territories requires a new personality. They require the same habits this set drilled in SQL form — define, decide, state your rules, test against the adversarial small case, think about the second run — carried across a border into a neighbouring notation. That is the real syllabus hidden inside ten SQL questions, and it is why this publication builds its sets the way it does: patterns first, decisions always, and the confidence that a candidate who learns to think this way in one language will think this way in every language the job eventually demands.
Appendix E — The morning-of checklist
Preparation ends, eventually, in a single morning, and the candidates who handle that morning well are rarely the ones who crammed hardest the night before. They are the ones who converted six days of work into a short, calm routine that protects the skills already built. This checklist is deliberately unambitious, because ambition on the morning itself is a tax on working memory you will want for tie rules and island keys.
Begin with the error log, not with new questions. Read each entry once, slowly, and for each one state the corrected rule aloud in a single sentence: ties survive under dense rank when the requirement counts levels; distinct cannot choose a survivor; a null inside a NOT IN list empties the result. Ten entries should take ten minutes. If an entry cannot be stated as a rule in one sentence, it was recorded too vaguely to help you now, and the honest response is to let it go rather than to reconstruct the whole question around it. The morning is for activating what you know, not for discovering what you do not.
Next, warm up the machinery with one easy execution, not one hard problem. Write the deduplication pattern from memory — partition, order, number, keep the first — and the anti-join in its NOT EXISTS form, and the rolling frame with its six preceding rows. These are the scales a musician plays before a concert: not performance, but proof to your own hands that the patterns are loaded and fluent. If a pattern stumbles, you have found the one thing worth five more minutes. If none stumble, stop. Additional drilling past fluency buys fatigue, not safety.
Then rehearse the openings, because the first ninety seconds of each live question are where your habits either appear or do not. Practise the clarification questions for a generic prompt until they feel conversational: what is the grain of this table, which columns can be null, what should happen at a tie, which population is excluded, and over what time window, in which timezone, are we measuring. Practise the definition sentence. Practise the closing sentence about scale and monitoring. None of this is scripted performance; it is the removal of friction, so that in the room your attention can live entirely on the specific problem rather than on remembering to clarify at all.
Handle the logistics early and plainly: the editor or assessment link tested, a quiet room, water, a notebook for the tiny test datasets you will sketch while thinking, and a hard stop on preparation at least an hour before the first interview, spent doing anything absorbing and unrelated. Arrive at the screen slightly under-stimulated rather than buzzing. Every interviewer account of strong loops describes the same surface calm in the candidate, and while calm cannot be manufactured in an hour, exhaustion can certainly be avoided in one, which amounts to much the same advantage.
During the loop itself, keep one private discipline: after each round, write two lines in your notebook — the pattern that appeared, and the decision you handled worst — and then close the notebook and let the round go. Between-round rumination is the most common unforced error in a full day of interviews, because round three inherits the emotional residue of round one unless something deliberate discharges it. Your two lines convert residue into record. The record improves your preparation for the next loop, whatever this loop decides; the rumination only taxes the next interviewer, who has not yet had the chance to form their own impression of you. Protect that chance. It is, in the end, what all the SQL was for.
What candidates are discussing
Every preparation community develops its own weather: recurring arguments that tell you less about any single interview than about where collective anxiety is sitting this season. The themes below are reported discussion, paraphrased from public candidate conversation and preparation guides, framed as rumour and debate rather than as fact about any company's internal process. Treat them the way a good engineer treats an unverified signal — interesting, worth preparing for, never a substitute for the verified record, which in this case is the structured material above: the questions, the definitions, the edge cases.
The liveliest debate concerns depth allocation. One camp of candidates argues that window functions have become the centre of gravity of the data engineering SQL round — ranking, recency, rolling frames and streak logic in the pattern of Questions 1, 3, 4, 5 and 8 — and that Python or algorithmic rounds carry comparatively less weight for this particular role than for generalist software roles. Another camp pushes back, reporting loops where a data manipulation round in Python, or a data modelling conversation, consumed the oxygen, and warning that over-investing in exotic SQL while neglecting modelling and pipeline design is its own failure mode. Cross-checked against the structure of this set, the balanced reading is that both camps are describing real variation between teams and levels. The prudent preparation is asymmetric: SQL fluency is the entry ticket and should be drilled to reliability, while modelling and idempotent pipeline thinking — Question 9's territory — is what converts a passing loop into a strong hire signal. Nobody credible reports a loop passed on Python brilliance with failing SQL, for a data engineering role whose daily work is SQL.
A second recurring argument asks whether the round is a take-home or live exercise, and which format favours which candidate. Reported experience includes both: timed online assessments taken alone, live shared-editor sessions with an interviewer watching and probing, and occasionally a take-home dataset with a follow-up discussion. The discussion consensus, such as it is, is that the formats test overlapping skills with different stress profiles — take-homes reward polish, documentation and testing, live sessions reward narration, clarification and recovery from a wrong turn — and that preparing only for one is a gamble. The practical translation: practise writing SQL silently to a clock for the assessment format, and practise talking while writing for the live format, including the underrated skill of saying "my first approach has a flaw at the tie boundary, here is the correction" without losing composure. Interviewers in live settings consistently describe valuing visible reasoning over silent perfection, according to public interviewer commentary, because the session is a simulation of working alongside you.
The third theme is dialect anxiety: whether Redshift-specific syntax is expected, given the company's well-known warehouse technology, or whether generic standard SQL suffices. The consensus across public guides is reassuring and consistent — generic SQL is the expected medium, interviewers work in whatever shared editor the process provides, and nobody sensible fails a candidate for writing a standard date function instead of a vendor's variant, provided the intent is clear. That consensus matches the approach taken throughout this set, where solutions are written in a readable generic style, dialect divergences are named in words — calendar generation, date difference functions, interval arithmetic — and the underlying pattern is kept portable. If you know one dialect deeply, say which one you are writing in and proceed confidently; translating syntax is a five-minute documentation task in a real job, and interviewers know it.
A quieter thread deserves mention because it recurs whenever candidates compare notes after loops: surprise that the questions felt "easy" yet the feedback was negative. Cross-checking those accounts against the failure modes catalogued in this set suggests a consistent explanation. The surface query is often short. The marks live in the tie rule stated before coding, the warehouse with no second value reported rather than dropped, the second run of the merge walked through unprompted, the pair reported once rather than twice. Candidates who prepare by collecting questions are ready for the surface. Candidates who prepare by collecting decisions are ready for the marking scheme. That distinction is, in the end, the editorial position of this entire publication, and it is why each answer here spends as many words on definitions and wrong answers as on the SQL itself.
Building a notebook from this set, so preparation compounds
There is a failure mode in interview preparation that has nothing to do with SQL knowledge: the evaporating practice session. A candidate works through ten questions on a Monday, understands every correction in the moment, and by the following Monday retains a warm feeling of familiarity and almost none of the specific decisions — which ranking function matched which tie rule, why the anti-join collapsed, what made the load safe to re-run. Familiarity feels like mastery right up until a live interview asks for the detail. The remedy is unglamorous and completely reliable: a personal question-and-answer notebook, one entry per question, written in your own words within a day of practising, containing the question in your phrasing, the definition you settled, the one trap that caught you, and the corrected SQL. Reviewing ten such entries the night before a loop is worth more than skimming fifty fresh questions, because the entries encode your errors, which are the ones you were statistically likely to repeat. This is exactly the kind of compounding, private, searchable preparation record that a tool like VaultBook (https://vaultbook.net) is built to hold — a personal vault where each set you work through becomes a permanent, organised asset rather than a scattered trail of browser tabs and half-remembered editor sessions. Start the notebook with this set: ten entries, ten traps, ten follow-ups you have already rehearsed, and let Set 2, when it arrives, add to a structure that already exists instead of starting your memory from zero again.
A study plan for this set
Work the set over six days, in the order that builds skill rather than the order that flatters it.
Day one — foundations under no time pressure. Questions 1 and 2, untimed. Write each answer, then write the wrong answer deliberately and construct the tiny dataset that exposes it. If you cannot break the wrong answer with three invented rows, you do not yet understand why it is wrong. Record both traps in your notebook the same evening.
Day two — sequences and streaks. Questions 3 and 4. These share a skeleton — collapse to the right grain, order within partitions, reason about neighbours — so study them as a pair and write one paragraph comparing them: what the island key does that LAG does not, and when each is the natural tool. Draw the January-to-June customer example by hand.
Day three — time and absence. Questions 5 and 6. Build the calendar spine logic until it bores you, because boredom here is fluency. For the anti-join, reproduce the NOT IN collapse yourself: insert one null into a practice orders table, watch the query return nothing, and never again need to be warned about it. Experienced traps are remembered; read traps are forgotten.
Day four — grain and value patterns. Questions 7 and 8. For fan-out, take any two tables you have used before and write their grains at the top of the page before joining them, as a drill. For the second-highest pattern, solve it three ways — dense rank, rank over distinct values, max-below-max — and articulate where each breaks or omits. Redundancy of method is not waste; it is how you survive the moment your first method is challenged.
Day five — engineering judgement. Questions 9 and 10, slowly. These reward prose as much as code, so practise the explanations out loud: walk an imaginary interviewer through the second run of the merge, and through what happens after the duplicate-charge query returns, before a single refund. Time yourself speaking; three clear minutes per design is the target.
Day six — simulation. Ninety minutes, four questions drawn unseen from the ten — one from each tier plus one repeat of whichever question has your worst notebook entry — under loop conditions: clarify aloud, state definitions, code, test with tiny data, invite the follow-up. Score yourself not on correctness alone but on the decision checklist: tie rules, null behaviour, grain, exclusion rules, second-run thinking. Then rest the following day. Consolidation is part of training, and a tired candidate narrates badly.
Across all six days, keep the error log brutally specific. "Careless mistake" is a useless entry. "Used ROW_NUMBER where ties had to survive, Question 1 pattern" is an entry that prevents a recurrence. Review the log, not the full set, in the final twenty-four hours before any real interview.
Pitfalls: the short list that costs the most marks
Ranking without a tie rule. Any top-N or latest-per-group answer that never states what happens at a tie is unfinished, whatever its output. Decide, state, choose the function that implements the decision.
DISTINCT as a deduplication strategy. Distinct removes identical copies. Versioned duplicates require a survivorship rule and a ranking. If your answer to a dedup question contains only the word distinct, it contains no answer.
Averaging or ranking a gappy time series. If days, weeks or months can be absent from the data but present in reality, build the spine. A rolling metric over present rows only is a different, usually meaningless, metric.
NOT IN against a nullable column. One null empties your result silently. Default to NOT EXISTS for anti-joins and make NOT IN justify itself each time it appears.
Summing across a fan-out. The moment a join multiplies rows, every additive measure from the repeated side is wrong by a per-row factor. Pre-aggregate the many side to the grain of the one side, then join.
Forgetting the excluded population. Single-order customers excluded, warehouses with no answer labelled, never-ordered customers included — every question in this set hides a population decision, and the marks follow the candidate who makes it deliberately.
Dividing without a guard. Ratios, percentages and deviation flags all divide. Check the denominator for zero and null before the division, using NULLIF or an explicit condition, and decide what the output should be when the denominator is absent.
Answering the question you wished for. Mid-interview definition changes — delivered instead of placed, business days instead of calendar days, net instead of gross — are tests, not interruptions. Recompute the population from the new definition, filter before you rank, and do not defend the previous answer's honour.
Frequently asked questions
How many questions should I expect in one SQL round?
Candidates report and public guides describe anything from two substantial questions with follow-ups to a timed set of several shorter ones in an assessment. Prepare for depth over volume: two questions answered with definitions, edge cases and follow-ups handled will serve you in either format, whereas shallow coverage of many patterns serves you in neither.
Is memorising these ten questions enough?
No, and the set is designed against that use. Each question is a pattern — ranking with ties, survivorship, islands, recency gaps, rolling frames, anti-joins, fan-out, distinct-value ranking, idempotent merge, temporal self-join — and patterns transfer when table names and stories change. Memorised SQL does not survive its first variant; understood patterns generate the variant's answer on the spot.
Which dialect should I write in?
Write clean generic SQL in the style of whichever dialect you know best, name that dialect at the start, and flag the one or two places where syntax varies, such as date truncation and interval arithmetic. Public preparation consensus holds that interviewers assess logic, not vendor trivia, for this role.
Do I really need to talk while coding?
In a live round, yes. Your narration is the interviewer's evidence of how you clarify, how you catch your own errors, and how you would behave in a design review. Silent brilliance is indistinguishable, from across the table, from silent guessing. Practise narrating definitions and decisions, not a running commentary on keystrokes.
What if I blank on a function name?
Describe the operation precisely — "I need the previous row's date within each customer's ordering" — and write the closest form you can. Interviewers routinely supply a forgotten name to a candidate who clearly knows the concept, because the concept is the job skill and the name is documentation. Blanking on the concept is the real problem, and that is what the study plan is for.
How important are the wrong answers, really?
They are the highest-yield section of each question for a prepared candidate, because interviewers plant the traps those sections catalogue. Knowing that ROW_NUMBER discards a tie, that NOT IN collapses on a null, and that DISTINCT cannot choose a survivor converts three potential silent failures into three visible demonstrations of care.
Should I optimise my answers for performance during the interview?
Optimise for correctness first, then make one or two informed performance remarks: filter early, pre-aggregate before joining, read a summary table instead of raw events for dashboards. A correct query with a sensible remark about scale beats a prematurely clever query that returns the wrong rows very quickly.
How do I handle a question I genuinely cannot solve?
Narrow it honestly. Solve the simpler stated case — no ties, no nulls — say exactly which complication you are setting aside, and talk through how you would approach it. Partial credit in loop interviews is real, and it flows to candidates whose reasoning stays visible and structured under difficulty. It does not flow to silence or to bluffing, both of which interviewers are trained to recognise.
What comes after SQL in preparation priority?
Data modelling — entities, grains, slowly changing dimensions — and pipeline design in the shape of Question 9: keys, idempotency, reconciliation, late data. Those topics convert SQL competence into data engineering credibility, and they are where the next sets in this publication will go after the SQL sequence is established.
Closing
Ten questions, ten patterns, and one standard running through all of them: define before you code, choose the population deliberately, state your tie and null rules, test against a dataset small enough to hold in your head, and think about the second run, the second day and the second reader of everything you write. A candidate who works that way is not merely prepared for an interview loop. They are rehearsing, accurately, the daily practice of the job the loop is hiring for — which is the deepest reason the preparation works. Work the set slowly, keep the notebook honestly, and let the patterns compound.
Last checked: 18 November 2026
Pinnacle Report — Layoffs verified. Ladders decoded. Interview questions answered. This set is an original preparation resource. Accounts of interview format are framed as candidate reports and public-guide descriptions, not as statements by Amazon. Worked scenarios marked as illustrative are composites created for teaching.

