Product BI / Round 4 / Shafi

01 / Start 2 min read · 5 min practice

Your route through Round 4

Use the supplied SQL preparation sheet as the coverage map: grain, joins, grouping, windows, sets, JSON, hierarchy, pivoting, transformations and performance reasoning. This guide adds boundary-case practice and a live local SQL workspace. It teaches you to write an answer, prove it and explain the trade-offs under interruption.

Route	Time and allocation	Finish line
Selected core	4 hours: 15m method/fixture; 40m joins and ranking; 40m windows/time; 30m sequence/JSON; 25m behavioral evidence; 60m Mock A; 30m repair.	240m selected practice. This does not include every lab.
Full guide	~11h 50m: 129 min reading + 9h 41m practice.	All 30 SQL labs and one full mock. Lab writing budgets are already counted in their chapters, not added again in the workspace.
All three mocks	~13h 50m including the full guide.	Add Mock B and C, one hour each. Breaks and extra repair attempts are additional.

Pass 1: predict the result, write the query, inspect the explanation. Pass 2: reconstruct cold, change one assumption and defend the result. The guide stores your SQL drafts, result checks, notes, clocks and progress in this browser. Export before moving files or switching browsers.

Round 1 · Round 2 · Round 3. Use Round 3 for deeper model and engine-tuning discussion.

02 / Execution 2 min read · 10 min practice

The live SQL interview method

A clear contract turns a query into a testable answer.

Step	What to say or do	Why it earns confidence
Clarify	Output columns, one-row meaning, time window/time zone, statuses, ties, nulls, duplicates, units and expected size.	Ask the ambiguities that change the answer; do not spend minutes on irrelevant metadata.
State the plan	Explain the intermediate grain and join cardinality before typing.	The interviewer can follow the algorithm and correct a misunderstood requirement early.
Write a correct baseline	Use named CTEs for distinct logical stages; qualify columns; write explicit join predicates.	Readable stages expose population loss and accidental fanout. A CTE does not itself guarantee materialization or speed.
Walk a fixture	Predict normal rows, zero activity, duplicate identity, a boundary, a tie and a null as relevant.	Correct syntax is necessary but insufficient.
Discuss scale	Estimate scans, sorts, shuffle, cardinality and output size. Offer one evidence-based improvement.	Avoid claiming EXISTS, an index or a CTE is universally fastest.
Close	Restate the result grain and any unresolved assumption.	The final answer remains tied to the customer question.

SAY IT / Opening

How will you approach a SQL coding problem?

Reveal worked answer

I would confirm the result grain and the few ambiguities that change the answer: population, time boundary, ties, duplicates and null handling. Then I would state the logical stages and write a readable baseline. I would test it on a small normal example and one counterexample before discussing the physical plan or scaling changes. If the interviewer changes a requirement, I would name the affected stage and revise it explicitly.

Pressure test: You have ten minutes. Prioritize the grain, one key ambiguity, a correct query and one edge case.

Avoid: Writing a clever window expression before defining the population.

Your 60-second coding approach

03 / Reference 3 min read · 5 min practice

Know the toy warehouse before coding

All exercises use fictional, deterministic data. Monetary amounts are integer USD cents. Date/time strings are sortable ISO UTC values; visits and calendar use dates. Nullable account_id in orders is deliberate. SQLite is the runnable lab dialect, not a claim about the interview platform. You can inspect any table in the workspace.

Tables	Grain / fields to know	Deliberate edge
accounts / orders	Account identity and region; one order with account, timestamp, status, cents, coupon.	d has no orders; o8 has unknown account; o4 cancelled; o7 paid with zero cents.
lines / captures / refunds	Separate line, capture and refund observations.	Two equal captures and two lines expose fanout and SUM(DISTINCT) mistakes.
sales / name_events	Product totals by category; raw name occurrences by group.	Tied amounts and names, plus a NULL name.
changes	One delivered event-version payload; exact deliveries may repeat.	Replacement and tombstone versions. Equal-version conflicts must be rejected outside the ranking assumption.
visits / calendar / events	Account-day visits, complete Jan 1-8 calendar, uniquely identified event stream.	Duplicate day, missing activity, 30/31-minute gaps, out-of-order funnel types.
account_history / old_values / new_values	Valid-time dimension versions and two keyed snapshots.	Half-open validity, nullable measures and missing keys.
documents / org	JSON payloads; node with parent.	Invalid JSON, wrong type, empty array and disconnected cycle.
required_services / service_use	Required set and observed account/service pairs.	Repeated usage; an empty requirement set has a defined all-accounts result.

Copyable complete SQLite setup

CREATE TABLE accounts(account_id TEXT PRIMARY KEY,region TEXT);
INSERT INTO accounts VALUES('a','US'),('b','EU'),('c','US'),('d','US');
CREATE TABLE orders(order_id TEXT PRIMARY KEY,account_id TEXT,ordered_at TEXT,status TEXT,cents INTEGER,coupon TEXT);
INSERT INTO orders VALUES
('o1','a','2026-01-01T09:00:00','paid',1000,NULL),
('o2','a','2026-01-01T10:00:00','paid',2000,'NEW'),
('o3','b','2026-01-02T09:00:00','paid',3000,NULL),
('o4','b','2026-01-03T09:00:00','cancelled',9000,NULL),
('o5','c','2026-01-03T10:00:00','paid',3000,'NEW'),
('o6','a','2026-01-04T00:00:00','paid',1000,NULL),
('o7','c','2026-01-04T10:00:00','paid',0,NULL),
('o8',NULL,'2026-01-04T11:00:00','paid',500,NULL),
('o9','b','2026-01-05T00:00:00','paid',1000,NULL),
('o10','a','2026-01-08T09:00:00','paid',4000,NULL);
CREATE TABLE lines(line_id TEXT PRIMARY KEY,order_id TEXT,cents INTEGER);
INSERT INTO lines VALUES('l1','o1',600),('l2','o1',400);
CREATE TABLE captures(capture_id TEXT PRIMARY KEY,order_id TEXT,cents INTEGER);
INSERT INTO captures VALUES('p1','o1',500),('p2','o1',500);
CREATE TABLE refunds(refund_id TEXT PRIMARY KEY,capture_id TEXT,cents INTEGER);
INSERT INTO refunds VALUES('r1','p2',100);
CREATE TABLE sales(category TEXT,product TEXT,cents INTEGER,PRIMARY KEY(category,product));
INSERT INTO sales VALUES('A','p1',100),('A','p2',100),('A','p3',50),('B','p4',20);
CREATE TABLE name_events(group_id TEXT,name TEXT);
INSERT INTO name_events VALUES('x','Ana'),('x','Ana'),('x','Bo'),('x','Bo'),('x',NULL),('y','Cy');
CREATE TABLE changes(event_id TEXT,version INTEGER,account_id TEXT,units INTEGER,deleted INTEGER);
INSERT INTO changes VALUES('e1',1,'a',10,0),('e1',1,'a',10,0),('e2',1,'b',20,0),('e2',2,'b',30,0),('e3',1,'c',5,0),('e3',2,'c',0,1);
CREATE TABLE visits(account_id TEXT,day TEXT);
INSERT INTO visits VALUES('a','2026-01-01'),('a','2026-01-01'),('a','2026-01-02'),('a','2026-01-03'),('a','2026-01-05'),('b','2026-01-02'),('b','2026-01-04'),('b','2026-01-05'),('c','2026-01-08');
CREATE TABLE calendar(day TEXT PRIMARY KEY);
INSERT INTO calendar VALUES('2026-01-01'),('2026-01-02'),('2026-01-03'),('2026-01-04'),('2026-01-05'),('2026-01-06'),('2026-01-07'),('2026-01-08');
CREATE TABLE events(event_id TEXT PRIMARY KEY,account_id TEXT,event_at TEXT,kind TEXT);
INSERT INTO events VALUES
('v1','a','2026-01-01T00:00:00','view'),('v2','a','2026-01-01T00:20:00','start'),('v3','a','2026-01-01T00:50:00','purchase'),('v4','a','2026-01-01T01:21:00','view'),
('v5','b','2026-01-01T01:00:00','purchase'),('v6','b','2026-01-01T01:10:00','view'),('v7','b','2026-01-01T01:20:00','start'),('v8','b','2026-01-02T01:11:00','purchase'),
('v9','c','2026-01-01T02:00:00','view'),('v10','c','2026-01-01T02:05:00','purchase');
CREATE TABLE account_history(account_sk INTEGER PRIMARY KEY,account_id TEXT,plan TEXT,valid_from TEXT,valid_to TEXT);
INSERT INTO account_history VALUES(1,'a','basic','2025-01-01T00:00:00','2026-01-04T00:00:00'),(2,'a','pro','2026-01-04T00:00:00',NULL),(3,'b','basic','2025-01-01T00:00:00',NULL),(4,'c','basic','2025-01-01T00:00:00',NULL);
CREATE TABLE old_values(id TEXT PRIMARY KEY,value INTEGER);
INSERT INTO old_values VALUES('a',100),('b',200),('c',NULL),('e',NULL),('f',NULL);
CREATE TABLE new_values(id TEXT PRIMARY KEY,value INTEGER);
INSERT INTO new_values VALUES('a',100),('b',250),('d',400),('e',0),('f',NULL);
CREATE TABLE documents(id TEXT PRIMARY KEY,payload TEXT);
INSERT INTO documents VALUES('j1','{"service":"s1","latency_ms":12,"tags":["prod","blue"]}'),('j2','{"service":"s1","latency_ms":"oops","tags":[]}'),('j3','not json'),('j4','{"service":"s2","latency_ms":null,"tags":"single"}'),('j5','{"service":"s2"}');
CREATE TABLE org(node TEXT PRIMARY KEY,parent TEXT);
INSERT INTO org VALUES('R',NULL),('a','R'),('b','a'),('c','a'),('x','y'),('y','x');
CREATE TABLE required_services(service_id TEXT PRIMARY KEY);
INSERT INTO required_services VALUES('s1'),('s2');
CREATE TABLE service_use(account_id TEXT,service_id TEXT);
INSERT INTO service_use VALUES('a','s1'),('a','s2'),('b','s1'),('c','s1'),('c','s2'),('c','s2');

04 / SQL foundations 5 min read · 46 min practice

Population, nulls, joins and aggregates

Keep three questions separate: which input observations qualify, what one output row represents, and which entities must remain even without a match. SQL normally uses bag semantics, so duplicate rows survive unless you deliberately remove them. NULL means unknown or absent under the schema, not automatically zero.

Expression or clause	Meaning	Common failure
COUNT(*) / COUNT(x) / COUNT(DISTINCT x)	Rows / nonnull x values / distinct nonnull x values.	COUNT(*) after a left join counts the placeholder for a parent with no child.
WHERE / HAVING	Filter input rows / filter grouped results.	Aggregate filters before GROUP BY or source filters after aggregation change the question.
LEFT JOIN plus WHERE on right	WHERE can reject null-extended unmatched rows.	Losing inactive accounts when a report must include them.
EXISTS / NOT EXISTS	Test whether at least one matching row exists / no row exists.	Joining solely to test existence can multiply the parent.
NOT IN and NULL	Unknown comparisons can eliminate rows unexpectedly.	Assuming a null-bearing subquery behaves like an anti join.
COALESCE	Apply a defined fallback at the appropriate stage.	Turning source incompleteness or an undefined rate into zero.

SQL LAB / 8 MIN PRACTICE

Count the right population

Using paid orders, return order_count, known_account_orders, distinct_accounts, coupon_orders, total_cents. Include the unmapped order in order and amount totals.

Hint

COUNT(*) counts rows; COUNT(column) ignores NULL.

Worked solution and checked rows

SELECT COUNT(*) order_count,COUNT(account_id) known_account_orders,COUNT(DISTINCT account_id) distinct_accounts,COUNT(coupon) coupon_orders,SUM(cents) total_cents FROM orders WHERE status='paid';

Why it works: The zero-cent paid order is still an order. A null account is unknown, not a fourth known account.

SQL LAB / 10 MIN PRACTICE

Preserve accounts with no orders

Return account_id, paid_orders and paid_cents for every account, sorted by account_id. Unmapped orders do not belong to a known account.

Hint

Keep the paid filter in the optional-side input or ON clause.

Worked solution and checked rows

SELECT a.account_id,COUNT(o.order_id) paid_orders,COALESCE(SUM(o.cents),0) paid_cents FROM accounts a LEFT JOIN orders o ON a.account_id=o.account_id AND o.status='paid' GROUP BY a.account_id ORDER BY a.account_id;

Why it works: Count a nonnull order key, not the left-join placeholder. COALESCE is justified here because no matched paid order means zero paid cents under this exercise.

SQL LAB / 8 MIN PRACTICE

Null-safe non-buyers

Find accounts with no paid order. Return account_id sorted ascending.

Hint

Use a correlated anti-existence check.

Worked solution and checked rows

SELECT a.account_id FROM accounts a WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.account_id=a.account_id AND o.status='paid') ORDER BY a.account_id;

Why it works: A NULL account_id exists in paid orders. NOT IN over that set can return no accounts; NOT EXISTS tests a match for each account.

SQL LAB / 8 MIN PRACTICE

Filter groups after rows

Return known account_id and paid_cents for accounts with at least two paid orders and at least 4000 paid cents. Sort by account_id.

Hint

WHERE filters observations; HAVING filters grouped results.

Worked solution and checked rows

SELECT account_id,SUM(cents) paid_cents FROM orders WHERE status='paid' AND account_id IS NOT NULL GROUP BY account_id HAVING COUNT(*)>=2 AND SUM(cents)>=4000 ORDER BY account_id;

Why it works: The cancelled 9000-cent order is removed before aggregation.

SQL LAB / 12 MIN PRACTICE

Join three processes without inflation

For order o1, return order_id, line_cents, captured_cents, refund_cents, net_collected. Aggregate each process before combining.

Hint

Two lines times two captures creates four rows.

Worked solution and checked rows

WITH l AS (SELECT order_id,SUM(cents) cents FROM lines GROUP BY order_id),
c AS (SELECT order_id,SUM(cents) cents FROM captures GROUP BY order_id),
r AS (SELECT c.order_id,SUM(r.cents) cents FROM refunds r JOIN captures c ON r.capture_id=c.capture_id GROUP BY c.order_id)
SELECT l.order_id,l.cents line_cents,c.cents captured_cents,COALESCE(r.cents,0) refund_cents,c.cents-COALESCE(r.cents,0) net_collected
FROM l JOIN c ON l.order_id=c.order_id LEFT JOIN r ON l.order_id=r.order_id ORDER BY l.order_id;

Why it works: Independent aggregates are one row per order. All fixture money is USD cents; a production multicurrency query must retain currency or a defined conversion.

SAY IT / Coding

Write paid-account totals, preserve non-buyers, then add payment and refund totals.

Reveal worked answer

I would start from accounts and keep the paid-order condition on the optional order side. I would count a nonnull order key and sum cents, using zero only for the defined no-matching-order case. The result is one row per account.

Before adding captures or refunds, I would aggregate each process to its compatible key and currency. I would not join raw lines to raw captures and hope a final GROUP BY repairs the multiplication. I would test an inactive account, a null account reference and two equal captures. The fanout lab shows why SUM(DISTINCT amount) also fails.

Pressure test: How would you preserve a payment whose order is missing? Define an unmatched bucket or reconciliation output; do not silently hide it.

Avoid: Mistaking row reduction for correctness.

Sources: SQLite SELECT, SQLite expressions

05 / Windows 5 min read · 38 min practice

Ties, frequency and current state

Contract	Function / stage	Tie policy
Exactly N rows at most	ROW_NUMBER with a total order	Supply a meaningful stable tie-breaker.
Competition rank	RANK	Equal values share a rank; following positions have gaps.
N distinct value levels	DENSE_RANK	Peers share a rank; no gaps between levels.
Latest current entity state	Order authoritative source versions, then filter deletions	Reject conflicting equal-version payloads; arrival order may not be authority.
Most frequent name	Aggregate occurrences then rank frequencies	Return all tied winners unless the question specifies one.

SQL LAB / 10 MIN PRACTICE

Top two distinct sales levels

Return category, product, cents, sales_rank for the top two distinct sales amounts within each category. Include ties, sorted category, sales_rank, product.

Hint

Distinct levels imply DENSE_RANK, not a fixed number of rows.

Worked solution and checked rows

WITH ranked AS (SELECT *,DENSE_RANK() OVER(PARTITION BY category ORDER BY cents DESC) sales_rank FROM sales)
SELECT category,product,cents,sales_rank FROM ranked WHERE sales_rank<=2 ORDER BY category,sales_rank,product;

Why it works: Do not add product to the ranking ORDER BY when equal amounts must remain peers. Use product only for final display order.

SQL LAB / 8 MIN PRACTICE

Exactly two products at most

Return category, product, cents, rn for at most two products per category. Break equal amounts by product ascending.

Hint

Use a total order for reproducible row selection.

Worked solution and checked rows

WITH ranked AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY category ORDER BY cents DESC,product) rn FROM sales)
SELECT category,product,cents,rn FROM ranked WHERE rn<=2 ORDER BY category,rn;

Why it works: ROW_NUMBER, RANK and DENSE_RANK encode different contracts.

SQL LAB / 10 MIN PRACTICE

Most frequent name in each group

Ignore NULL names. Return group_id, name, frequency for all tied most-frequent names per group, ordered group_id,name.

Hint

Aggregate name occurrences, then rank the aggregated rows.

Worked solution and checked rows

WITH counts AS (SELECT group_id,name,COUNT(*) n FROM name_events WHERE name IS NOT NULL GROUP BY group_id,name),
r AS (SELECT *,DENSE_RANK() OVER(PARTITION BY group_id ORDER BY n DESC) rk FROM counts)
SELECT group_id,name,n frequency FROM r WHERE rk=1 ORDER BY group_id,name;

Why it works: The fixture names are case-sensitive exact values. Clarify trimming, case and identity normalization before collapsing names.

SQL LAB / 10 MIN PRACTICE

Latest valid state including deletes

Return event_id, account_id, units for current nondeleted events. Versions replace prior states; identical deliveries can repeat. Same-key/version payloads are assumed consistent.

Hint

Rank all versions first, including tombstones.

Worked solution and checked rows

WITH exact AS (SELECT DISTINCT * FROM changes),
r AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY version DESC) rn FROM exact)
SELECT event_id,account_id,units FROM r WHERE rn=1 AND deleted=0 ORDER BY event_id;

Why it works: Filtering deleted rows before ranking resurrects e3. Preserve tombstone ordering knowledge even when the serving result omits it.

SAY IT / Windows

Why would ROW_NUMBER, RANK and DENSE_RANK produce different top-two answers?

Reveal worked answer

ROW_NUMBER selects at most two ordered rows. RANK represents competition positions and can leave gaps after ties. DENSE_RANK selects the top two distinct value levels. I would ask whether the customer wants two entities, two positions or two value levels. For tie-preserving rank I would order by the ranked measure only, then add a stable tie-breaker to the final display order. For exactly two entities I would include that tie-breaker in ROW_NUMBER.

Pressure test: The values are 100,100,50. Predict ranks 1,1,3 and dense ranks 1,1,2.

Avoid: Adding entity_id inside DENSE_RANK and accidentally destroying ties.

SAY IT / Current state

Why must deletion filtering follow latest-version selection?

Reveal worked answer

A delete is an authoritative state transition. If I remove its row before ranking, an earlier active version becomes the apparent latest record and the entity reappears. I would rank valid source versions including deletion state, then filter the selected tombstone from the serving result while retaining version knowledge for replay. Exact duplicate deliveries and contradictory same-version payloads need different handling.

Pressure test: What if the event ID is only unique within a tenant? Include tenant/source scope in the partition key.

Avoid: Partitioning by all payload columns so a corrected event becomes a separate entity.

Sources: SQLite window functions

06 / Windows 5 min read · 38 min practice

Window grain, frames and precise time

A grouping operation reduces rows to its grouping grain. A window computes across related rows while preserving the rows entering that stage. Therefore, aggregate to the intended day/account/product grain first when the analytical window is defined at that grain. Filter window results in an outer query unless the selected engine offers a suitable QUALIFY clause.

Window functions see the rows available at their stage, not rows filtered out earlier.

ROWS uses physical row positions; RANGE uses ordered-value semantics, with engine-specific capabilities. In SQLite an omitted ordered frame includes the current peer group by default. Specify the intended frame instead of depending on a default. LAST_VALUE needs a frame through UNBOUNDED FOLLOWING when you mean the partition’s final value. A window ORDER BY does not guarantee final result order.

SQL LAB / 10 MIN PRACTICE

Running sales by calendar day

Return every calendar day and cumulative paid cents through that day, including zero-sales days, sorted by day.

Hint

Aggregate to one row per day before the window.

Worked solution and checked rows

WITH d AS (SELECT substr(ordered_at,1,10) day,SUM(cents) cents FROM orders WHERE status='paid' GROUP BY substr(ordered_at,1,10)),
filled AS (SELECT c.day,COALESCE(d.cents,0) cents FROM calendar c LEFT JOIN d ON c.day=d.day)
SELECT day,SUM(cents) OVER(ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumulative_cents FROM filled ORDER BY day;

Why it works: A calendar spine makes absent days explicit; the fixture guarantees complete input for these dates. Missing source data is not automatically zero.

SQL LAB / 10 MIN PRACTICE

Day-over-day change with zero baseline

Return day, cents, previous_cents and percentage_change for every calendar day. Use NULL if the prior day is absent or zero; round valid percent changes to two decimals.

Hint

Separate daily totals, LAG and the final arithmetic.

Worked solution and checked rows

WITH d AS (SELECT substr(ordered_at,1,10) day,SUM(cents) cents FROM orders WHERE status='paid' GROUP BY substr(ordered_at,1,10)),
f AS (SELECT c.day,COALESCE(d.cents,0) cents FROM calendar c LEFT JOIN d ON c.day=d.day),
w AS (SELECT *,LAG(cents) OVER(ORDER BY day) previous_cents FROM f)
SELECT day,cents,previous_cents,ROUND(100.0*(cents-previous_cents)/NULLIF(previous_cents,0),2) percentage_change FROM w ORDER BY day;

Why it works: 100.0 forces fractional arithmetic. An undefined baseline is not zero growth.

SQL LAB / 10 MIN PRACTICE

True last order amount

Return each paid order_id and the cents of that account’s final paid order in the fixture. Exclude NULL accounts. Break timestamp ties by order_id; sort output by account_id,ordered_at,order_id.

Hint

LAST_VALUE needs a frame that reaches the partition end.

Worked solution and checked rows

SELECT order_id,LAST_VALUE(cents) OVER(PARTITION BY account_id ORDER BY ordered_at,order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_cents
FROM orders WHERE status='paid' AND account_id IS NOT NULL ORDER BY account_id,ordered_at,order_id;

Why it works: A default ordered frame often ends at the current row or peer group, not the last row of the partition.

SQL LAB / 8 MIN PRACTICE

Half-open time filter

Return order_id,cents for paid orders on January 4 UTC, ordered order_id. Use timestamps bounded by start inclusive and next day exclusive.

Hint

A half-open interval avoids guessing the last representable time of day.

Worked solution and checked rows

SELECT order_id,cents FROM orders WHERE status='paid' AND ordered_at>='2026-01-04T00:00:00' AND ordered_at<'2026-01-05T00:00:00' ORDER BY order_id;

Why it works: These are sortable ISO UTC text values in SQLite. Use typed timestamps and correctly converted local-calendar bounds in a warehouse.

SAY IT / Windows

How do you compute day-over-day growth and a running daily total correctly?

Reveal worked answer

I would define paid status, the reporting time zone and whether missing source data is distinguishable from zero activity. I would aggregate to one row per day and join a calendar spine over the required complete period. Then I would calculate the running SUM with an explicit ROWS frame and use LAG for the prior day. I would divide using fractional arithmetic and NULLIF for a zero baseline. Any display-date filter would follow windows that need earlier dates.

Pressure test: What does a NULL growth rate mean here? No valid prior baseline, not zero growth.

Avoid: Applying a three-row window directly to sparse raw orders.

Sources: SQLite window functions, SQLite date/time functions, Redshift SUM windows, Redshift QUALIFY

07 / Time series 3 min read · 24 min practice

Calendar windows and distinct users

“Last three days” is a time contract; “last three rows” is a physical frame. They coincide only after you establish one row per day with the required dates present. Distinct entities introduce another problem: overlapping daily populations cannot be added. Use an exact bounded method first, then discuss a scaling alternative without changing the metric silently.

SQL LAB / 12 MIN PRACTICE

Three-calendar-day total

Return day and paid_cents_3d for January 3 through January 8 inclusive. Include zero-sales dates in the three-day frame.

Hint

Compute the window before removing dates needed for history.

Worked solution and checked rows

WITH d AS (SELECT substr(ordered_at,1,10) day,SUM(cents) cents FROM orders WHERE status='paid' GROUP BY substr(ordered_at,1,10)),
filled AS (SELECT c.day,COALESCE(d.cents,0) cents FROM calendar c LEFT JOIN d ON c.day=d.day),
w AS (SELECT day,SUM(cents) OVER(ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) paid_cents_3d FROM filled)
SELECT day,paid_cents_3d FROM w WHERE day>='2026-01-03' ORDER BY day;

Why it works: Filtering to January 3 before the window would lose January 1 and 2.

SQL LAB / 12 MIN PRACTICE

Distinct users over three days

For January 3 through January 8, return day and distinct_accounts across that day and its two preceding days.

Hint

A bounded range join can establish correctness on small data.

Worked solution and checked rows

SELECT c.day,COUNT(DISTINCT v.account_id) distinct_accounts FROM calendar c LEFT JOIN visits v ON v.day>=date(c.day,'-2 days') AND v.day<=c.day WHERE c.day>='2026-01-03' GROUP BY c.day ORDER BY c.day;

Why it works: Summing daily distinct counts double-counts users across days. For scale, bound input and evaluate deduplicated account-days or an approved approximate sketch with stated error.

SAY IT / Time series

Why can a rolling distinct-user count not be built by summing daily distinct counts?

Reveal worked answer

The same user can appear on several days and would be counted repeatedly. I would define the exact date window and count unique account identities across that union. A bounded range join over deduplicated account-days is a clear baseline. At scale I would measure the expansion and consider a more suitable maintained representation or mergeable approximate sketch if the product accepts a stated error. I would not call the sum of daily distincts a unique-user metric.

Pressure test: Does adding DISTINCT to each day fix overlap across days? No.

Avoid: Optimizing into a different denominator.

Sources: SQLite window functions, SQLite date/time functions

08 / Sequence problems 3 min read · 30 min practice

Streaks and sessions from ordered events

This exercise starts a session only when inactivity is greater than thirty minutes.

For streaks, reduce to unique entity-days before numbering. For sessions, order events consistently, compare each timestamp with the prior timestamp, flag starts and cumulatively sum the flags. These are different grains. Equal timestamps need a declared interpretation and stable display order; a tie-breaker gives reproducibility but cannot invent business causality.

SQL LAB / 15 MIN PRACTICE

Consecutive-day activity islands

Deduplicate account-day visits. Return account_id,start_day,end_day,active_days for every consecutive-day island, ordered account_id,start_day.

Hint

For consecutive dates, day minus row number is constant.

Worked solution and checked rows

WITH d AS (SELECT DISTINCT account_id,day FROM visits),
r AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY day) rn FROM d),
g AS (SELECT *,date(day,'-'||rn||' days') island FROM r)
SELECT account_id,MIN(day) start_day,MAX(day) end_day,COUNT(*) active_days FROM g GROUP BY account_id,island ORDER BY account_id,start_day;

Why it works: Deduplicate first, or duplicate days advance row numbers and split or merge the wrong islands.

SQL LAB / 15 MIN PRACTICE

Sessionize with an exact boundary

For each event return account_id,event_id,session_no, sorted account_id,event_at,event_id. A new session begins only when the gap is greater than 30 minutes. All times are UTC.

Hint

LAG, boundary flag, cumulative SUM: three distinct stages.

Worked solution and checked rows

WITH p AS (SELECT *,LAG(event_at) OVER(PARTITION BY account_id ORDER BY event_at,event_id) prev_at FROM events),
f AS (SELECT *,CASE WHEN prev_at IS NULL OR unixepoch(event_at)-unixepoch(prev_at)>1800 THEN 1 ELSE 0 END new_session FROM p)
SELECT account_id,event_id,SUM(new_session) OVER(PARTITION BY account_id ORDER BY event_at,event_id ROWS UNBOUNDED PRECEDING) session_no FROM f ORDER BY account_id,event_at,event_id;

Why it works: The 30-minute gap stays in session 1; the 31-minute gap starts session 2.

SAY IT / Coding

Write session IDs and consecutive-day streaks, then explain the difference.

Reveal worked answer

For sessions I would partition by account, order by timestamp and a stable event key, compute LAG, flag the first row or a gap beyond the agreed threshold, and cumulatively sum those flags. I would test exact-threshold and just-over-threshold gaps.

For daily streaks I would first deduplicate account-day observations. I could then group by a date-minus-row-number island key or flag a break whenever the prior day is not adjacent. I would aggregate each island to its start, end and length. Raw event duplicates must not advance the daily row number.

Pressure test: Late events can join two previous sessions. Describe whether published session IDs are restated or versioned.

Avoid: Treating a session identifier as stable under all late-arrival policies.

Sources: SQLite window functions, SQLite date/time functions

09 / Product BI 4 min read · 33 min practice

Ordered funnels and observable retention

Write the denominator in words before SQL. A funnel can count first-touch cohorts, repeated attempts, sessions or any valid sequence. This fixture deliberately uses first view per account and no later re-anchoring. Retention likewise needs an entity, cohort rule, observation instant/day and completeness cutoff. The SQL follows those decisions.

SQL LAB / 18 MIN PRACTICE

Ordered first-view conversion funnel

Anchor each account on its first view. Count viewed_accounts, started_accounts, purchased_accounts. Start must occur strictly after view; purchase strictly after that first valid start; both within 24 hours inclusive of the view. No re-anchoring on later views.

Hint

Sequential MIN stages must constrain each next event by the prior valid stage.

Worked solution and checked rows

WITH v AS (SELECT account_id,MIN(event_at) viewed_at FROM events WHERE kind='view' GROUP BY account_id),
s AS (SELECT v.account_id,v.viewed_at,MIN(e.event_at) started_at FROM v LEFT JOIN events e ON e.account_id=v.account_id AND e.kind='start' AND e.event_at>v.viewed_at AND unixepoch(e.event_at)<=unixepoch(v.viewed_at)+86400 GROUP BY v.account_id,v.viewed_at),
p AS (SELECT s.account_id,s.started_at,MIN(e.event_at) purchased_at FROM s LEFT JOIN events e ON e.account_id=s.account_id AND e.kind='purchase' AND e.event_at>s.started_at AND unixepoch(e.event_at)<=unixepoch(s.viewed_at)+86400 GROUP BY s.account_id,s.started_at)
SELECT COUNT(*) viewed_accounts,COUNT(started_at) started_accounts,COUNT(purchased_at) purchased_accounts FROM p;

Why it works: b has a purchase before its view and another 24h1m after its view; neither converts this anchored funnel. c has no start.

SQL LAB / 15 MIN PRACTICE

D1 retention with censoring

First visit defines cohort. Data is complete through January 8. Return cohort_day, cohort_accounts, observable_accounts, retained_accounts, d1_pct. January 8’s next-day observation is unavailable, so its percentage is NULL.

Hint

Use an explicit observation cutoff and deduplicate with existence.

Worked solution and checked rows

WITH f AS (SELECT account_id,MIN(day) cohort_day FROM visits GROUP BY account_id),
flags AS (SELECT f.*,CASE WHEN date(cohort_day,'+1 day')<='2026-01-08' THEN 1 ELSE 0 END observable,
CASE WHEN date(cohort_day,'+1 day')<='2026-01-08' AND EXISTS(SELECT 1 FROM visits v WHERE v.account_id=f.account_id AND v.day=date(f.cohort_day,'+1 day')) THEN 1 ELSE 0 END retained FROM f)
SELECT cohort_day,COUNT(*) cohort_accounts,SUM(observable) observable_accounts,SUM(retained) retained_accounts,ROUND(100.0*SUM(retained)/NULLIF(SUM(observable),0),2) d1_pct FROM flags GROUP BY cohort_day ORDER BY cohort_day;

Why it works: First observed is true first only if history coverage supports that assumption. A future unobserved day is not evidence of churn.

SAY IT / Coding

Build a view-to-start-to-purchase funnel and next-day retention.

Reveal worked answer

I would first settle the funnel anchor, repeated-attempt policy, event ordering and conversion window. For a first-view account funnel, I would find the first view, then the first valid later start within the window, then a later purchase that also falls inside the view-based window. A purchase before the view cannot qualify.

For D1 retention I would assign the first-visit cohort under the available history contract, then test for existence of a visit on the next calendar day. I would distinguish observed nonreturn from a next day beyond the completeness cutoff. I would count accounts once and use only the observable cohort as the denominator for a reported retention percentage.

Pressure test: What changes if the product allows a later view to start a new attempt? You need an attempt/session definition; the first-view query no longer answers it.

Avoid: Using independent MIN timestamps without enforcing the required sequence.

Sources: SQLite date/time functions, SQLite SELECT

10 / Correctness 4 min read · 36 min practice

As-of joins and complete reconciliation

A history join needs a business key and a temporal predicate. A reconciliation needs both key domains and null-safe value comparison. These checks protect different failures: interval overlap can multiply matches; an inner join can hide missing keys; ordinary inequality can miss a null-versus-value change.

SQL LAB / 12 MIN PRACTICE

As-of plan on each order

Return order_id and event-time plan for paid orders with a known account. Use [valid_from,valid_to), sorted order_id.

Hint

Use the order timestamp, not load time or current_flag.

Worked solution and checked rows

SELECT o.order_id,h.plan FROM orders o JOIN account_history h ON o.account_id=h.account_id AND o.ordered_at>=h.valid_from AND (o.ordered_at<h.valid_to OR h.valid_to IS NULL) WHERE o.status='paid' AND o.account_id IS NOT NULL ORDER BY o.order_id;

Why it works: Validate interval nonoverlap and missing matches separately. An inner join is justified here only because the fixture covers every known account order.

SQL LAB / 12 MIN PRACTICE

Full reconciliation including NULL

Return id,old_value,new_value,issue for changed or missing keys. Label changed, missing_new or missing_old. Sort by id.

Hint

Two anti/outer branches cover both key domains.

Worked solution and checked rows

SELECT o.id AS id,o.value old_value,n.value new_value,CASE WHEN n.id IS NULL THEN 'missing_new' ELSE 'changed' END issue FROM old_values o LEFT JOIN new_values n ON o.id=n.id WHERE n.id IS NULL OR o.value IS NOT n.value
UNION ALL
SELECT n.id,NULL,n.value,'missing_old' FROM new_values n LEFT JOIN old_values o ON n.id=o.id WHERE o.id IS NULL ORDER BY id;

Why it works: Nonnullable id detects presence even when a stored value is NULL. SQLite IS NOT is a null-safe comparison; use target-engine equivalents.

SQL LAB / 12 MIN PRACTICE

Detect invalid temporal intervals

From a CTE with A:[Jan1,Feb1), A:[Jan15,infinity), B:[Jan1,Jan15), B:[Jan15,infinity), return only overlapping id pairs, ordered first_id,second_id.

Hint

Two half-open intervals overlap when each starts before the other ends.

Worked solution and checked rows

WITH h(id,account_id,start_at,end_at) AS (VALUES(1,'a','2026-01-01','2026-02-01'),(2,'a','2026-01-15',NULL),(3,'b','2026-01-01','2026-01-15'),(4,'b','2026-01-15',NULL))
SELECT a.id first_id,b.id second_id FROM h a JOIN h b ON a.account_id=b.account_id AND a.id<b.id AND (a.start_at<b.end_at OR b.end_at IS NULL) AND (b.start_at<a.end_at OR a.end_at IS NULL) ORDER BY a.id,b.id;

Why it works: Touching intervals for B are valid adjacency, not overlap. Separately reject end<=start and define gap handling.

SAY IT / Coding

How would you prove two query outputs are equivalent?

Reveal worked answer

I would fix the same input cutoff, definition, output grain and numeric contract. I would compare keys present only on each side, value differences using null-safe semantics, and multiplicity if the result is a bag rather than a unique-key set. I would compare required ordering separately. Equal totals are insufficient because missing or misattributed values can cancel. A checksum can help locate discrepancies but is not an absolute proof without accounting for collision and representation.

Pressure test: If both outputs have duplicate keys, what changes? Compare row multiplicities or group by all relevant values and compare counts.

Avoid: Using EXCEPT alone when duplicate multiplicities matter.

Sources: SQLite expressions, SQLite SELECT

11 / Transformations 3 min read · 18 min practice

Sets and pivoted output

UNION compares complete output rows; it does not deduplicate by a business key unless those rows express that key alone. UNION ALL preserves multiplicity. EXCEPT and INTERSECT semantics and ALL support vary by engine. For a fixed pivot, conditional aggregates make the population and fallback rules visible. Ensure both sides of a set operation have compatible column positions and types.

SQL LAB / 8 MIN PRACTICE

Set versus bag

Return label and n for UNION and UNION ALL of left values a,a,b and right values a,c. Sort by label.

Hint

UNION removes duplicate whole result rows.

Worked solution and checked rows

WITH l(v) AS (VALUES('a'),('a'),('b')),r(v) AS (VALUES('a'),('c')),
u AS (SELECT v FROM l UNION SELECT v FROM r),ua AS (SELECT v FROM l UNION ALL SELECT v FROM r)
SELECT 'union' label,COUNT(*) n FROM u UNION ALL SELECT 'union_all',COUNT(*) FROM ua ORDER BY label;

Why it works: UNION ALL preserves input multiplicity; use it when duplicates are meaningful or already controlled.

SQL LAB / 10 MIN PRACTICE

Conditional aggregate pivot

Return known account_id,jan1_cents,jan2_cents,jan3_cents for every account including zero activity. Count paid orders only.

Hint

Conditional SUM is a portable fixed-column pivot.

Worked solution and checked rows

SELECT a.account_id,
SUM(CASE WHEN substr(o.ordered_at,1,10)='2026-01-01' THEN o.cents ELSE 0 END) jan1_cents,
SUM(CASE WHEN substr(o.ordered_at,1,10)='2026-01-02' THEN o.cents ELSE 0 END) jan2_cents,
SUM(CASE WHEN substr(o.ordered_at,1,10)='2026-01-03' THEN o.cents ELSE 0 END) jan3_cents
FROM accounts a LEFT JOIN orders o ON a.account_id=o.account_id AND o.status='paid' GROUP BY a.account_id ORDER BY a.account_id;

Why it works: The cancelling order contributes nothing. A dynamic set of output columns typically requires generated SQL or client-side shaping.

SAY IT / Transformations

How do you pivot without a dialect-specific PIVOT operator?

Reveal worked answer

For fixed output categories I would group by the intended row key and use conditional aggregates, such as SUM(CASE WHEN condition THEN amount ELSE 0 END). I would preserve zero-activity entities from the correct base table. If categories are dynamic, I would clarify whether dynamic SQL or client-side shaping is allowed; a static query cannot promise arbitrary new output columns without a mechanism.

Pressure test: Why is COUNT(CASE WHEN p THEN 1 ELSE 0 END) usually wrong for counting matches? Both one and zero are nonnull.

Avoid: Choosing output shape before deciding which entities must remain.

Sources: SQLite SELECT, SQLite expressions

12 / Semi-structured data 3 min read · 24 min practice

JSON extraction without losing type or parents

Treat parsing, type validation and relational expansion as separate steps. This lab uses SQLite JSON functions. A missing field, JSON null, invalid payload and wrong scalar type can require different handling. Preserve that information when the contract needs it. Array expansion changes grain, so repeating a parent amount for every tag can inflate a later sum.

SQL LAB / 12 MIN PRACTICE

Typed JSON scalar extraction

For every document return id,service,latency_ms. Invalid JSON behaves as an empty object. Accept latency only when its JSON type is integer or real; missing, null and text become SQL NULL.

Hint

Guard the value passed to the parser, then validate the scalar type.

Worked solution and checked rows

WITH clean AS (SELECT id,CASE WHEN json_valid(payload) THEN payload ELSE '{}' END p FROM documents)
SELECT id,json_extract(p,'$.service') service,CASE WHEN json_type(p,'$.latency_ms') IN ('integer','real') THEN CAST(json_extract(p,'$.latency_ms') AS REAL) END latency_ms FROM clean ORDER BY id;

Why it works: SQLite can cast invalid text to zero. A cast alone is not validation. Missing and explicit JSON null can be distinguished with json_type if the business contract requires it.

SQL LAB / 12 MIN PRACTICE

Expand tags but preserve empty parents

Return document id and tag, retaining one NULL tag row for absent/empty arrays or invalid JSON. Order id, array position. Treat nonarray tags as empty.

Hint

An outer expansion preserves a parent with no child elements.

Worked solution and checked rows

WITH clean AS (SELECT id,CASE WHEN json_valid(payload) THEN payload ELSE '{}' END p FROM documents),
a AS (SELECT id,CASE WHEN json_type(p,'$.tags')='array' THEN json_extract(p,'$.tags') ELSE '[]' END tags FROM clean)
SELECT a.id,j.value tag FROM a LEFT JOIN json_each(a.tags) j ON 1=1 ORDER BY a.id,j.key;

Why it works: Each tag repeats the parent. Aggregate parent measures before or after an intentional allocation, not by blindly summing expanded rows.

SAY IT / Coding

Extract a numeric JSON field and expand an optional array safely.

Reveal worked answer

I would decide how invalid JSON, missing fields, explicit nulls and wrong types should appear. I would guard the value passed to parsing and check the scalar type before casting, because some engines can coerce invalid text in surprising ways. For an array I would validate that it is an array, then choose an outer expansion if parent records with no elements must remain. I would state the new parent-element grain and avoid summing repeated parent measures.

Pressure test: Does a successful parse prove the expected schema? No; valid JSON can still have the wrong fields or types.

Avoid: Treating every string cast as trustworthy numeric data.

Sources: SQLite JSON functions, Redshift JSON_PARSE

13 / Harder SQL 4 min read · 42 min practice

Hierarchy, all-of queries and median

Recognize the logic before reaching for syntax. Hierarchy traversal needs an anchor, a recursive step and a termination rule. “Used every required service” is universal quantification: there is no requirement left unsatisfied. Median is an order statistic, not an average. These problems reward a clear invariant and a small counterexample.

SQL LAB / 15 MIN PRACTICE

Recursive hierarchy with cycle guard

Starting at R, return node,depth,path for all reachable descendants including R, ordered depth,node. Paths are slash-separated. Cap depth at 20 and do not revisit a node on the current path; IDs contain no comma or slash.

Hint

Anchor plus recursive step, with path membership and a depth boundary.

Worked solution and checked rows

WITH RECURSIVE walk(node,depth,path,seen) AS (
SELECT node,0,node,','||node||',' FROM org WHERE node='R'
UNION ALL
SELECT o.node,w.depth+1,w.path||'/'||o.node,w.seen||o.node||',' FROM org o JOIN walk w ON o.parent=w.node WHERE w.depth<20 AND instr(w.seen,','||o.node||',')=0)
SELECT node,depth,path FROM walk ORDER BY depth,node;

Why it works: The disconnected x/y cycle is not reachable from R. Starting at x must return x,y and stop; the build verifies that too. A depth cap prevents runaway work but can truncate a valid deep tree, so expose that limit.

SQL LAB / 12 MIN PRACTICE

Accounts using every required service

Return all account_id values that used every service in required_services. Duplicated use does not matter. Sort account_id.

Hint

There must be no required service for which this account lacks use.

Worked solution and checked rows

SELECT a.account_id FROM accounts a WHERE NOT EXISTS (SELECT 1 FROM required_services r WHERE NOT EXISTS (SELECT 1 FROM service_use u WHERE u.account_id=a.account_id AND u.service_id=r.service_id)) ORDER BY a.account_id;

Why it works: If requirements are empty, every account satisfies this universal condition. Confirm whether the business wants that mathematical behavior.

SQL LAB / 15 MIN PRACTICE

Median paid order amount

Return one median_cents value across all paid orders, including zero and the unmapped order. Use an approach that handles both odd and even counts.

Hint

For integer n, the two middle-position formulas coincide for odd n.

Worked solution and checked rows

WITH ranked AS (SELECT cents,ROW_NUMBER() OVER(ORDER BY cents,order_id) rn,COUNT(*) OVER() n FROM orders WHERE status='paid')
SELECT AVG(1.0*cents) median_cents FROM ranked WHERE rn IN ((n+1)/2,(n+2)/2);

Why it works: SQLite integer division is intentionally used for the ranks; explicit floating arithmetic is used for the mean. Target dialects can use built-in percentile functions with their own semantics.

SAY IT / Hierarchy

How do you keep a recursive hierarchy query from running forever?

Reveal worked answer

I would define the starting roots and relationship direction, then carry enough path or visited-state information to reject a node already on the current path. I would add an operational depth or row bound and make truncation visible. UNION may remove identical rows, but it does not necessarily stop a cycle when depth or path changes every iteration. I would test a normal tree, an orphan, a cycle and a valid path at the chosen limit.

Pressure test: What if the graph permits multiple legitimate paths to the same node? Decide whether the output is paths or distinct reachable nodes before applying global deduplication.

Avoid: Calling a depth cap proof that all reachable nodes were returned.

Sources: SQLite WITH and recursion, SQLite window functions

14 / Performance 2 min read · 15 min practice

Make the correct query efficient

Symptom / choice	Reasoning	Boundary
Large join result	Verify grain, key uniqueness and filtering before tuning algorithms.	A correct many-to-many output can still be inherently large.
EXISTS versus IN versus JOIN	Choose the semantic contract first, inspect the actual optimized plan.	Syntax alone does not establish a universal runtime winner.
CTEs and subqueries	Use stages for clarity and testability.	Inlining/materialization depends on engine/version and optimizer decisions.
Selective filters	Filter and project early when semantically valid; use half-open typed predicates.	Moving a right-side filter into WHERE can alter a left join.
Windows	Reduce to the correct grain and reuse compatible partitions/orderings where the plan can.	Partition-wide sorts and skew may dominate. Preaggregating can remove required detail.
Indexes / layout	Row-store indexes may help selective access; columnar pruning/distribution solve different physical problems.	A Redshift primary key is not an Oracle B-tree access path.
Hash / nested loop / merge	Match cardinalities, available ordering, memory and indexes.	A small outer input can favor nested loops; spills and estimate error can change the trade-off.
Views / materialized views / external tables	Logical saved query / stored refreshable result / data accessed outside the primary table storage.	Freshness, pushdown, file pruning and refresh capabilities are engine-specific.

SAY IT / Performance

Your query is correct but slow on production-sized data. What would you change first?

Reveal worked answer

I would separate queue time from execution and inspect the actual plan evidence: scanned rows and bytes, estimate error, join multiplicity, redistribution, skew, sorting and spill. I would choose one change that targets the measured bottleneck while preserving the output contract. I would compare equivalent results at the same cutoff and benchmark representative parameters and concurrent conditions. A faster warm-cache run of one small sample is not enough.

Pressure test: What does EXPLAIN ANALYZE do in PostgreSQL? It executes the statement; use an authorized controlled query.

Avoid: Promising that fewer CTEs or more indexes always makes SQL faster.

Reuse the deeper Oracle, Redshift and Spark diagnosis cases in Round 3. This round’s priority is fluent query construction plus a defensible plan discussion.

Sources: PostgreSQL 18 EXPLAIN, Redshift table design

15 / Portability 3 min read · 10 min practice

State the dialect and translate deliberately

Need	Runnable lab: SQLite	Interview translation
Filter a window result	CTE/subquery then WHERE	Redshift supports QUALIFY. Oracle 19c baseline uses nesting; do not assume newer syntax exists there.
Ordered aggregate window	Explicit ROWS frame	Redshift requires an explicit frame for ordered aggregate windows such as SUM. Verify supported frame types.
Date arithmetic	date(day, modifier), unixepoch(ts)	Use the engine’s typed-date/timestamp arithmetic. DATEADD, interval expressions and subtraction are not interchangeable syntax.
Top N	Window plus outer filter; LIMIT for a global limit	FETCH FIRST, TOP and LIMIT differ. Tie semantics still require a contract.
JSON	json_valid/json_type/json_extract/json_each	Redshift JSON_PARSE produces SUPER. Oracle JSON operators and array expansion use different syntax and error options.
Null-safe equality	IS / IS NOT for these fixture values	Use supported IS DISTINCT FROM or explicit null cases; verify the target dialect.
Integer division / decimal	100.0 or explicit real arithmetic for percentages	Use appropriate DECIMAL/NUMERIC precision for money. The fixture uses small cents below JavaScript’s exact-integer limit.
String filters	LIKE percent/underscore wildcard; explicit ESCAPE for literals	Case, collation, Unicode, concatenation and regex behavior differ. Do not normalize names without a requirement.
Recursion	WITH RECURSIVE	Oracle hierarchical/recursive forms and Redshift recursive support have different restrictions. Explain the logic if exact syntax is uncertain.

-- SQLite / Oracle-19c-style outer-filter shape (date syntax omitted):
WITH ranked AS (
  SELECT category, product, cents,
         ROW_NUMBER() OVER(PARTITION BY category
                           ORDER BY cents DESC, product) rn
  FROM sales
)
SELECT category, product, cents FROM ranked WHERE rn <= 2;

-- Redshift-specific alternative, illustrative and not run in this guide:
SELECT category, product, cents
FROM sales s
QUALIFY ROW_NUMBER() OVER(PARTITION BY category
                         ORDER BY cents DESC, product) <= 2;

Literal wildcard / checked string-filter example

The pattern below requires a literal underscore after svc, followed by any suffix. It returns svc_% and svc_prod, and rejects svcXprod. Percent and underscore have different wildcard roles; escaping the underscore protects the intended prefix. Case and collation remain dialect choices.

WITH names(value) AS (VALUES('svc_prod'),('svcXprod'),('svc_%'))
SELECT value FROM names
WHERE value LIKE 'svc!_%' ESCAPE '!'
ORDER BY value;

SAY IT / Portability

You remember the logic but not one function’s exact dialect syntax. What do you do?

Reveal worked answer

I would state the assumed dialect, explain the operation precisely and use a supported equivalent when I know it. I would ask whether documentation is permitted before looking anything up. I would not silently mix functions from several engines or claim the query ran when it did not. A short syntax uncertainty should not prevent me from proving the grain, sequence and edge-case behavior.

Pressure test: If documentation is not allowed, offer pseudocode for the isolated function and complete the rest of the query clearly.

Avoid: Bluffing familiarity with a feature instead of making the uncertainty specific.

Sources: Redshift QUALIFY, Redshift SUM windows, Redshift JSON_PARSE, Oracle 19c analytic functions

16 / Leadership principle 2 min read · 10 min practice

Learn and Be Curious: show a changed decision

The official principle emphasizes continuing to learn and exploring new possibilities. In an interview, a strong example shows a specific knowledge gap, deliberate learning, evidence that changed your approach and a useful application. A list of courses or tools is weaker unless you can show what changed in your work.

Evidence	Strong version	Weak version
Gap	I did not understand whether identity was stable over time.	I needed to learn everything.
Method	A small experiment, primary documentation and expert challenge.	I watched many videos.
Changed belief	The counterexample invalidated my current-state assumption.	I became confident.
Application	I changed a query/contract and checked its result.	I added the skill to my resume.
Transfer	A peer reused the test or review mechanism.	I sent a document and assumed adoption.

SAY IT / Learn and Be Curious

What have you learned recently, and how did it change your work?

Reveal worked answer

I would name a specific gap and the decision it affected, describe the experiment or feedback I used to learn, and show the implementation or judgment I changed. I would separate a lab from production experience and explain what I still do not know. The result should be observable, such as a corrected model, a rejected assumption or a reusable test, rather than just completion of a course.

Pressure test: Which evidence contradicted your first view? Be ready to name it.

Avoid: Turning curiosity into a list of buzzwords.

Sources: Amazon Leadership Principles

17 / Constructed rehearsal 3 min read · 15 min practice

Learning identity semantics before choosing an algorithm

SAY IT / Learn and Be Curious

Tell me about something important you had to learn to solve a problem.

Reveal worked answer

While building a first-party analytics capability, I initially treated an identifier mapping as a straightforward lookup problem. The harder question was what an identifier meant over time. I owned the data transformation, but I did not yet understand all the conditions under which a device, user and account should be associated.

I wrote down the assumptions my first design depended on and asked the source owner and a downstream analyst to challenge them. I read the source contract and traced a small set of records across changes. In this rehearsal I constructed six counterexamples, including a shared device, a reused identifier and a mapping arriving after the event. Those examples showed that the lookup could be unique at load time and still classify historical events incorrectly.

I changed the design to preserve the relevant time and identity scope, make unresolved mappings visible and validate historical attribution instead of only checking whether every row had a key. I compared the revised query with the original on the counterexamples and documented which consumer questions each interpretation could support. A partner reviewed the cases rather than just approving the diagram.

The outcome in this constructed example was a narrower, explainable contract and a review set other engineers could reuse. I would not claim a revenue gain or universal matching accuracy from that exercise. The learning changed an implementation decision, not merely my familiarity with a technology.

My lesson was to learn the domain uncertainty that could invalidate the model before optimizing the mechanics. I now ask what evidence would make my initial assumption wrong and seek that evidence early.

Pressure test: What did you personally not know?

Avoid: Presenting the invented incident details as personal history.

Six follow-ups and answer direction

What did you personally not know?

Whether the identifier relationship was stable through time and whether consumers wanted event-time or current classification. Name a concrete gap, not the entire identity domain.

How was this more than reading documentation?

I converted the uncertainty into counterexamples, obtained domain feedback and changed the data contract and validation.

What did your first design get wrong?

It equated a unique current mapping with correct historical attribution. Own that assumption instead of blaming unclear requirements.

Why not ask an expert to solve it?

Expert feedback was essential, but I remained responsible for understanding the mechanism, testing it and implementing the agreed interpretation.

How did you know you had learned enough?

I could predict the reviewed edge cases, explain the limits to a consumer and identify what remained unresolved. That is bounded evidence, not complete mastery.

What would you do differently?

Seek the time-dependent counterexamples before building the first lookup, and record the distinction in the consumer contract.

Rehearse the structure, then replace assumptions with your own defensible incident notes. Product-scale figures from the resume are not personal causal impact.

18 / Constructed rehearsal 3 min read · 15 min practice

Replacing a familiar SQL mental model with distributed evidence

SAY IT / Learn and Be Curious

Tell me about a time learning changed an approach you were confident in.

Reveal worked answer

During a reporting migration, my familiar approach was to translate the existing SQL logic and then tune individual expressions. The first distributed version was correct on a small sample but behaved poorly on a larger, skewed input. I realized that translating syntax had not given me a reliable model of the execution work.

I separated what I understood from what I needed to learn: how the join moved rows, where the shuffle occurred and why one key produced a long task. I studied the executed plan and stage evidence, created a small skewed input, and asked a more experienced engineer to challenge my explanation. I tested a valid preaggregation against the unaggregated version and checked that it preserved the reporting grain.

In this rehearsal I used twelve correctness fixtures and then two shadow reporting days across six metrics. The useful outcome was identifying which transformation reduced unnecessary movement while preserving the output. I documented both the cases where it helped and the queries where it did not apply. I would only attach a performance percentage to a real measured comparison, not to this constructed story.

I shared the example with the team as a short review exercise: predict the output grain, identify the movement, then compare runtime evidence. That made the learning reusable without turning my one experiment into a universal rule.

The lesson was that experience in one execution model can create overconfidence in another. I learned to test the physical mechanism and the business result together, and to revise my preferred approach when the evidence disagreed.

Pressure test: Which belief changed?

Avoid: Presenting the invented incident details as personal history.

Six follow-ups and answer direction

Which belief changed?

That a logically equivalent syntax translation would have a comparable physical cost. The distributed plan made row movement and skew central.

What was the learning plan?

A specific question, primary documentation, a small reproducible input, expert feedback and a measured comparison with correctness checks.

How did you avoid endless research?

I time-boxed learning around the decision that blocked the migration and used the next experiment to resolve that question.

What if preaggregation changes the result?

Reject it or change the grain/components. Reduced runtime does not compensate for lost detail, incorrect distinct counts or changed join semantics.

How did others benefit?

In the rehearsal they received a reusable review exercise and documented boundaries. A real answer should identify actual use, not merely a document sent.

What are you still learning now?

Choose a specific current gap and a completed or underway experiment. Distinguish hands-on practice from production experience.

Rehearse the structure, then replace assumptions with your own defensible incident notes. Product-scale figures from the resume are not personal causal impact.

19 / Leadership principle 2 min read · 10 min practice

Earn Trust: candor plus follow-through

Amazon’s definition includes attentive listening, respectful candor and visible self-criticism. Show what another person could reasonably rely on you to do, the moment that confidence was tested and your concrete follow-through. Being agreeable or technically right is not enough.

Moment	Useful response	Evidence to retain
Your explanation was wrong	Correct the claim promptly and state what is known versus unresolved.	The affected decision, correction and validation.
A date is at risk	Own the original assumption, explain scope and offer a useful choice before the surprise.	Revised checkpoint, customer acceptance and actual follow-through.
A partner challenges your result	Listen to the use case and compare the same population/definition/cutoff.	The specific evidence that resolved or narrowed the disagreement.
You lack expertise	State the boundary and a concrete learning or escalation step.	The completed experiment or specialist review, not an unsupported assurance.

SAY IT / Earn Trust

A stakeholder no longer trusts your dashboard. What do you do?

Reveal worked answer

I would ask them to show the decision or discrepancy that broke confidence, then acknowledge any error or communication gap I own. I would reconcile the same population, definition and cutoff with them, identify verified scope and explain what remains unresolved. I would agree an acceptance example and a response owner, then follow through with visible evidence. I would not treat an apology or a corrected number alone as proof that trust was restored.

Pressure test: What if their preferred number is wrong? Explain the evidence respectfully and test both definitions against the actual decision.

Avoid: Trying to win the argument before understanding why the customer doubts the result.

Sources: Amazon Leadership Principles

20 / Constructed rehearsal 3 min read · 15 min practice

Correcting your own confident metric explanation

SAY IT / Earn Trust

Tell me about a time you had to admit you were wrong.

Reveal worked answer

I had explained a metric movement to a product partner with more confidence than the evidence justified. My aggregate check looked consistent, so I attributed the change to behavior. A later regional comparison showed that one region was absent from the input. I owned both the incomplete validation and the explanation I had given.

I contacted the partner as soon as the discrepancy was confirmed. I stated exactly which interpretation I was retracting, what I had verified and what remained unknown. I did not wait for the entire repair so I could present the issue as a completed success. I asked whether the earlier explanation had already affected a decision, because correcting a table would not necessarily correct the customer consequence.

I worked with the source owner to restore the missing population and compared the revised result at the same cutoff. I sent a concise correction showing the affected scope and the updated interpretation, then changed the acceptance check to include region coverage as well as the overall total. I asked a partner to review the correction and the new check.

In this rehearsal the corrected output was accepted, and the specified coverage check stayed clear across seven subsequent releases. That observation would support only a bounded follow-through claim. It would not establish that every quality problem was eliminated or that trust was instantly restored.

The uncomfortable part was correcting an explanation I had already defended. My lesson was to make confidence proportional to the evidence and to communicate a known error before I could wrap it in a reassuring final outcome.

Pressure test: What did you say first?

Avoid: Presenting the invented incident details as personal history.

Six follow-ups and answer direction

What did you say first?

I was wrong to attribute the movement to behavior before validating the regional population. One region was missing; I am correcting that interpretation.

Why should they trust you afterward?

Not because I apologized once. Clear correction, transparent scope, an owned repair and observable follow-through create evidence over time.

Did the customer act on the wrong information?

The constructed script does not establish that. In a real answer, state the actual consequence and any remediation; do not invent a harmless outcome.

Why not wait until the fix was complete?

The partner could still rely on the wrong explanation. Early correction protects their next decision while repair continues.

How did you treat the source team?

Describe the evidence and shared repair respectfully. My responsibility for validation and communication does not disappear because the input was incomplete.

What if the new check is noisy?

Investigate the false alarms and refine the meaningful population contract while preserving visibility of material coverage failures.

Rehearse the structure, then replace assumptions with your own defensible incident notes. Product-scale figures from the resume are not personal causal impact.

21 / Constructed rehearsal 3 min read · 15 min practice

Resetting a delivery commitment before the surprise

SAY IT / Earn Trust

Tell me about a time you had to reset a commitment with a stakeholder.

Reveal worked answer

I committed to a reporting delivery before I had fully validated the exception handling in two source feeds. As implementation progressed, I found that completing those feeds under the original date would require either hiding unresolved identities or weakening the acceptance checks. I had made the commitment, so I needed to own the gap instead of presenting it as somebody else’s delay.

I raised the issue before the promised review. I explained that six of the eight feeds met the agreed checks and two did not, why my original estimate had been incomplete, and what the missing feeds meant for the customer’s decision. I asked the partner which decision had to be made on the original date rather than assume a partial delivery would help.

I proposed two concrete choices: a clearly labeled six-feed view if that population supported the immediate decision, or a complete view after a short revised checkpoint. I kept the missing scope visible, named the repair owner and agreed when I would report progress even if nothing had improved. I did not call the partial output complete.

In this constructed example the partner accepted the bounded view for the immediate task, and the remaining two feeds passed review three days later. I would report those as assumed rehearsal outcomes until replaced with a real incident. The ongoing improvement was adding source-exception discovery to commitment planning and verifying the acceptance scope before promising a date.

The lesson was that trust depends on making the other person’s planning more reliable. Early candor, a useful alternative and follow-through mattered more than defending my original estimate.

Pressure test: Why was the original commitment wrong?

Avoid: Presenting the invented incident details as personal history.

Six follow-ups and answer direction

Why was the original commitment wrong?

I underestimated source exceptions before validating the inputs. Name the decision I owned, not just an external dependency.

What if six feeds cannot support the customer’s decision?

Do not push a partial delivery just to show progress. Explain the consequence, agree the best available alternative and keep the checkpoint explicit.

How much notice did you give?

Use the actual timeline in a real story. The rehearsal establishes notice before the review, not a verified number of days.

What if the customer was still upset?

Acknowledge the planning cost and listen. Do not equate an accepted workaround with restored trust or satisfaction.

How did you avoid another missed date?

Base the revised checkpoint on the remaining work and evidence, expose uncertainty and report progress as agreed. Do not create certainty by wording alone.

What became repeatable?

Source-exception discovery and acceptance scope entered planning before commitments. A real answer should show subsequent use of that mechanism.

Rehearse the structure, then replace assumptions with your own defensible incident notes. Product-scale figures from the resume are not personal causal impact.

22 / Under pressure 3 min read · 10 min practice

Interruptions, uncertainty and hard follow-ups

Challenge	Answer directly	Then support it
That query drops a customer.	Identify the filter/join that changes the population.	Walk the missing row through the stages and correct the result.
Why did you choose that approach?	Name the contract or measured cost it protects.	Compare one plausible alternative fairly.
You do not know this function?	State the exact syntax uncertainty.	Explain the operation and offer a known equivalent or isolated pseudocode.
What did you personally do?	Name your decision and implementation responsibility.	Separate your work from the partner’s contribution.
Did trust actually improve?	Describe observed follow-through or feedback.	Avoid equating an accepted fix with universal restored confidence.
What would you change now?	Name a specific assumption or mechanism.	Explain the evidence that makes the change worthwhile.

SAY IT / Earn Trust

You find a mistake in your query after confidently explaining the result. What do you say?

Reveal worked answer

I would name the mistake directly: for example, the right-side filter removed inactive accounts, so my result did not preserve the requested population. I would correct the query, walk the affected edge case and state whether the earlier conclusion changes. I would not hide the change or spend time defending the original answer. A clear correction demonstrates a reliable reasoning process even when the first version was wrong.

Pressure test: What if you cannot finish the correction in time? Explain the remaining defect and the next specific step without claiming completion.

Avoid: Quietly editing the SQL while leaving the prior verbal conclusion standing.

SAY IT / Closing

Ask two useful questions and close the interview.

Reveal worked answer

I would ask which SQL workloads or product decisions are hardest to keep both correct and efficient, and how this team reviews metric definitions and learns from data incidents. I would listen for the actual workflow and ask a relevant follow-up. I would connect my experience to building understandable queries and trustworthy data products, rather than ask for a score or recite every database I have used.

Pressure test: What did their answer change about your understanding of the team?

Avoid: A generic question list with no follow-up.

23 / Personalize 2 min read · 20 min practice

Your four-story evidence workspace

Prepare a primary and backup for each principle. Notes need enough detail to support a follow-up before you self-confirm. Editing notes clears the confirmation. A confirmation is your own attestation, not verification by this guide.

Learn and Be Curious

Learning identity semantics before choosing an algorithm

Your real incident I replaced constructed details with a real incident I can defend.

Where have you used it?

Learn and Be Curious

Replacing a familiar SQL mental model with distributed evidence

Your real incident I replaced constructed details with a real incident I can defend.

Where have you used it?

Earn Trust

Correcting your own confident metric explanation

Your real incident I replaced constructed details with a real incident I can defend.

Where have you used it?

Earn Trust

Resetting a delivery commitment before the surprise

Your real incident I replaced constructed details with a real incident I can defend.

Where have you used it?

24 / Knowledge 6 min read · 20 min practice

Thirty checks before the cold mock

Target 30/30 with explanations hidden. Explain why the tempting alternative fails. Reset clears only these quiz answers.

25 / Practice workspace 2 min read · 5 min practice

Write, run and check SQL offline

Select a lab and write its query from a blank editor. Run executes in an isolated in-memory SQLite worker with the fixture reset every time. The engine and data are embedded. Your query does not contact a server. A five-second limit and a row cap stop runaway practice queries. This is a correctness lab, not a cloud performance benchmark.

Engine starts when you run.

Your SQLite query / Ctrl+Enter to run

Expected result / reveal after your attempt

Inspect the fixture tables

Sources: sql.js project

26 / Rehearse 2 min read · 60 min practice

Three full SQL and behavioral mocks

Three complete interviews, with first-person scripts, explicit follow-ups and checked technical answers.

Read the scripts in the next three chapters, then rehearse cold. Use the 60-minute mode for a full interview or the short drill for concise recall. The continuous clock pauses and resumes; moving stages does not reset it.

Complete transcript	Stage budgets	Coverage
A / Financial joins, running totals and correcting a mistake	5 + 25 + 10 + 8 + 8 + 4 = 60 min	6 full answers + 12 answered follow-ups
B / Sessions, semi-structured data and reliable commitments	5 + 25 + 10 + 8 + 8 + 4 = 60 min	6 full answers + 12 answered follow-ups
C / Ordered funnels, reconciliation and learning under pressure	5 + 25 + 10 + 8 + 8 + 4 = 60 min	6 full answers + 12 answered follow-ups

01:30

Reveal full answer and answered follow-ups

What you said / query defect / repair evidence

Self-rating:

Nailed: correct mechanism and a defensible follow-up. Wobbled: right direction with a missing condition or weak evidence. Blank: no usable solution. Repair each gap, then repeat cold.

27 / Complete interview 14 min read · 0 min practice

Full mock A / Financial joins, running totals and correcting a mistake

Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.

Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.

Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.

STAGE 1 / 00:00 TO 05:00 / 5 MIN

Shafi, introduce yourself and connect your experience to this interview.

Resume-based introduction

Shafi’s full answer + 2 answered follow-ups

I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.

My approach to SQL is to define the output grain before touching the syntax. I check whether joins preserve that grain, whether missing values are meaningful and whether a time boundary changes the result. That discipline matters as much as writing the query quickly.

For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.

Interviewer follow-ups, fully answered

1. What did you personally own rather than the broader team?

I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.

2. Why are you interested in this Data Engineer II role?

The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.

STAGE 2 / 05:00 TO 30:00 / 25 MIN

For order o1, return order_id, line_cents, captured_cents, refund_cents, net_collected. Aggregate each process before combining.

Technical scenario

Complete input fixture

Run this fixture before the answer query

CREATE TABLE lines(line_id TEXT PRIMARY KEY,order_id TEXT,cents INTEGER);
INSERT INTO lines VALUES('l1','o1',600);
INSERT INTO lines VALUES('l2','o1',400);
CREATE TABLE captures(capture_id TEXT PRIMARY KEY,order_id TEXT,cents INTEGER);
INSERT INTO captures VALUES('p1','o1',500);
INSERT INTO captures VALUES('p2','o1',500);
CREATE TABLE refunds(refund_id TEXT PRIMARY KEY,capture_id TEXT,cents INTEGER);
INSERT INTO refunds VALUES('r1','p2',100);

Shafi’s full answer + 2 answered follow-ups

I need one row per order, but the three source tables represent separate processes. Joining line items, captures and refunds directly would multiply rows. For example, two lines and two captures already make four joined rows before refunds enter. DISTINCT would conceal that modeling problem and could remove legitimate equal-valued transactions.

I aggregate each process independently by order_id. I then join the three order-level results and convert a missing capture or refund aggregate to zero only because this contract treats absence as no transaction. Net collected is captured cents minus refunded cents. It is not line value minus refunds, and I would not assert equality between order value and captured value when partial payment is possible.

The query below produces the checked result on the supplied fixture. I would inspect uniqueness of each aggregate before the join and reconcile each measure against its own source. At scale, I would restrict the eligible order population and time range before expensive aggregation where that restriction is valid. The engine must still read the relevant facts; a CTE does not itself guarantee materialization or speed.

SQL / SQLite fixture

WITH l AS (SELECT order_id,SUM(cents) cents FROM lines GROUP BY order_id),
c AS (SELECT order_id,SUM(cents) cents FROM captures GROUP BY order_id),
r AS (SELECT c.order_id,SUM(r.cents) cents FROM refunds r JOIN captures c ON r.capture_id=c.capture_id GROUP BY c.order_id)
SELECT l.order_id,l.cents line_cents,c.cents captured_cents,COALESCE(r.cents,0) refund_cents,c.cents-COALESCE(r.cents,0) net_collected
FROM l JOIN c ON l.order_id=c.order_id LEFT JOIN r ON l.order_id=r.order_id ORDER BY l.order_id;

Checked result

order_id	line_cents	captured_cents	refund_cents	net_collected
o1	1000	1000	100	900

Interviewer follow-ups, fully answered

1. What if a refund has no matching order line?

This query intentionally starts from the line population. A refund outside that population would be absent from the output. For financial reconciliation I would instead build an order-key spine from the union of keys across all three sources, left join the aggregates to it and report an orphan-source flag. I would not silently drop the refund or invent a line record.

2. How would you report product-level net revenue?

I would first establish an allocation contract for captures and refunds because they are currently order-level facts. If a refund references a line, I can use that relationship. Otherwise I need an approved allocation rule, such as proportional allocation with integer-cent remainder handling. I would retain the original order-level amounts, test that allocations sum back exactly and avoid pretending the allocation is an observed product-level payment.

STAGE 3 / 30:00 TO 40:00 / 10 MIN

Return every calendar day and cumulative paid cents through that day, including zero-sales days, sorted by day.

Technical scenario

Complete input fixture

Run this fixture before the answer query

CREATE TABLE orders(order_id TEXT PRIMARY KEY,account_id TEXT,ordered_at TEXT,status TEXT,cents INTEGER,coupon TEXT);
INSERT INTO orders VALUES('o1','a','2026-01-01T09:00:00','paid',1000,NULL);
INSERT INTO orders VALUES('o2','a','2026-01-01T10:00:00','paid',2000,'NEW');
INSERT INTO orders VALUES('o3','b','2026-01-02T09:00:00','paid',3000,NULL);
INSERT INTO orders VALUES('o4','b','2026-01-03T09:00:00','cancelled',9000,NULL);
INSERT INTO orders VALUES('o5','c','2026-01-03T10:00:00','paid',3000,'NEW');
INSERT INTO orders VALUES('o6','a','2026-01-04T00:00:00','paid',1000,NULL);
INSERT INTO orders VALUES('o7','c','2026-01-04T10:00:00','paid',0,NULL);
INSERT INTO orders VALUES('o8',NULL,'2026-01-04T11:00:00','paid',500,NULL);
INSERT INTO orders VALUES('o9','b','2026-01-05T00:00:00','paid',1000,NULL);
INSERT INTO orders VALUES('o10','a','2026-01-08T09:00:00','paid',4000,NULL);
CREATE TABLE calendar(day TEXT PRIMARY KEY);
INSERT INTO calendar VALUES('2026-01-01');
INSERT INTO calendar VALUES('2026-01-02');
INSERT INTO calendar VALUES('2026-01-03');
INSERT INTO calendar VALUES('2026-01-04');
INSERT INTO calendar VALUES('2026-01-05');
INSERT INTO calendar VALUES('2026-01-06');
INSERT INTO calendar VALUES('2026-01-07');
INSERT INTO calendar VALUES('2026-01-08');

Shafi’s full answer + 2 answered follow-ups

I first aggregate paid orders to one row per date. Then I join that result to the calendar so dates with zero sales remain present. The cumulative sum runs over that dense daily relation with an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame.

The placement of the aggregation matters: the window should accumulate daily amounts, not arbitrary transaction rows or duplicated calendar matches. I use the calendar date to order the result and coalesce the absent daily amount to zero. The checked output below shows that a zero-sales date keeps the prior cumulative amount. A late order changes the relevant daily amount and every later cumulative value, so a stored cumulative table needs a restatement strategy.

SQL / SQLite fixture

WITH d AS (SELECT substr(ordered_at,1,10) day,SUM(cents) cents FROM orders WHERE status='paid' GROUP BY substr(ordered_at,1,10)),
filled AS (SELECT c.day,COALESCE(d.cents,0) cents FROM calendar c LEFT JOIN d ON c.day=d.day)
SELECT day,SUM(cents) OVER(ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cumulative_cents FROM filled ORDER BY day;

Checked result

day	cumulative_cents
2026-01-01	3000
2026-01-02	6000
2026-01-03	9000
2026-01-04	10500
2026-01-05	11500
2026-01-06	11500
2026-01-07	11500
2026-01-08	15500

Interviewer follow-ups, fully answered

1. How would you add a trailing seven-day total?

After building the dense calendar relation, I would use SUM(daily_cents) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Seven rows represent seven dates only because the calendar is dense and unique. If the displayed report starts later, I must compute with the prior six dates still available, then filter the presentation range outside the window calculation.

2. What changes when the business day is not UTC?

I would derive the business date in the agreed named time zone before grouping, then join the corresponding business calendar. I would keep the raw event instant in UTC for traceability. Daylight-saving transitions mean a local day is not always 24 hours, so I would not implement this by subtracting a fixed offset from every event.

STAGE 4 / 40:00 TO 48:00 / 8 MIN

Tell me about learning something that changed your data design.

Constructed personal incident; resume program anchor

Shafi’s full answer + 2 answered follow-ups

While building a first-party analytics capability, I initially treated an identifier mapping as a straightforward lookup problem. The harder question was what an identifier meant over time. I owned the data transformation, but I did not yet understand all the conditions under which a device, user and account should be associated.

I wrote down the assumptions my first design depended on and asked the source owner and a downstream analyst to challenge them. I read the source contract and traced a small set of records across changes. In this rehearsal I constructed six counterexamples, including a shared device, a reused identifier and a mapping arriving after the event. Those examples showed that the lookup could be unique at load time and still classify historical events incorrectly.

I changed the design to preserve the relevant time and identity scope, make unresolved mappings visible and validate historical attribution instead of only checking whether every row had a key. I compared the revised query with the original on the counterexamples and documented which consumer questions each interpretation could support. A partner reviewed the cases rather than just approving the diagram.

The outcome in this constructed example was a narrower, explainable contract and a review set other engineers could reuse. I would not claim a revenue gain or universal matching accuracy from that exercise. The learning changed an implementation decision, not merely my familiarity with a technology.

My lesson was to learn the domain uncertainty that could invalidate the model before optimizing the mechanics. I now ask what evidence would make my initial assumption wrong and seek that evidence early.

Interviewer follow-ups, fully answered

1. How did you avoid spending weeks learning without shipping?

I limited the investigation to assumptions that could change attribution. I brought six concrete cases to the source owner, timeboxed the review and separated settled behavior from unresolved mappings. That produced a testable contract and a bounded implementation instead of an open-ended reading project.

2. What would have happened if you had kept the original lookup?

A mapping that was unique at load time could have assigned an older event to the wrong account or treated a shared device as a unique person. The job might pass key-null and row-count checks while producing a misleading historical metric. The counterexamples showed why a temporal contract was necessary.

STAGE 5 / 48:00 TO 56:00 / 8 MIN

Tell me about earning back trust after you were wrong.

Constructed personal incident; resume program anchor

Shafi’s full answer + 2 answered follow-ups

I had explained a metric movement to a product partner with more confidence than the evidence justified. My aggregate check looked consistent, so I attributed the change to behavior. A later regional comparison showed that one region was absent from the input. I owned both the incomplete validation and the explanation I had given.

I contacted the partner as soon as the discrepancy was confirmed. I stated exactly which interpretation I was retracting, what I had verified and what remained unknown. I did not wait for the entire repair so I could present the issue as a completed success. I asked whether the earlier explanation had already affected a decision, because correcting a table would not necessarily correct the customer consequence.

I worked with the source owner to restore the missing population and compared the revised result at the same cutoff. I sent a concise correction showing the affected scope and the updated interpretation, then changed the acceptance check to include region coverage as well as the overall total. I asked a partner to review the correction and the new check.

In this rehearsal the corrected output was accepted, and the specified coverage check stayed clear across seven subsequent releases. That observation would support only a bounded follow-through claim. It would not establish that every quality problem was eliminated or that trust was instantly restored.

The uncomfortable part was correcting an explanation I had already defended. My lesson was to make confidence proportional to the evidence and to communicate a known error before I could wrap it in a reassuring final outcome.

Interviewer follow-ups, fully answered

1. What exactly did you say to the partner?

I said, "My earlier explanation was too confident. One region is missing from the input, so I cannot support the behavior-change conclusion I gave you. I am restoring that population and checking the same cutoff. Please tell me whether you used the earlier explanation in a decision so I can help correct its consequences." That separated the known error from the work still underway.

2. How did you know the repair addressed the original mistake?

I checked the expected region set and source coverage before comparing the revised metric at the same cutoff. I also asked the partner to review the corrected interpretation. Seven later releases passing the new coverage check were evidence of that specific control working, not proof that every data-quality risk had disappeared.

STAGE 6 / 56:00 TO 60:00 / 4 MIN

What questions do you have for me, and how would you close?

Role-focused closing

Shafi’s full answer + 2 answered follow-ups

I have two questions. Which recurring SQL or metric errors cause the most customer rework today? When the team changes a shared metric, how do engineers and consumers validate the new interpretation?

The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.

Interviewer follow-ups, fully answered

1. What would you focus on during your first month?

I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.

2. What would you do if the team’s approach differed from your previous company?

I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.

28 / Complete interview 14 min read · 0 min practice

Full mock B / Sessions, semi-structured data and reliable commitments

Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.

Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.

Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.

STAGE 1 / 00:00 TO 05:00 / 5 MIN

Shafi, introduce yourself and connect your experience to this interview.

Resume-based introduction

Shafi’s full answer + 2 answered follow-ups

I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.

I have worked through migrations where equivalent-looking SQL produced different behavior because the execution model or data semantics changed. I make ordering, nulls, ties and time boundaries explicit, then use a small counterexample to test the query before discussing performance.

For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.

Interviewer follow-ups, fully answered

1. What did you personally own rather than the broader team?

I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.

2. Why are you interested in this Data Engineer II role?

The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.

STAGE 2 / 05:00 TO 30:00 / 25 MIN

For each event return account_id,event_id,session_no, sorted account_id,event_at,event_id. A new session begins only when the gap is greater than 30 minutes. All times are UTC.

Technical scenario

Complete input fixture

Run this fixture before the answer query

CREATE TABLE events(event_id TEXT PRIMARY KEY,account_id TEXT,event_at TEXT,kind TEXT);
INSERT INTO events VALUES('v1','a','2026-01-01T00:00:00','view');
INSERT INTO events VALUES('v2','a','2026-01-01T00:20:00','start');
INSERT INTO events VALUES('v3','a','2026-01-01T00:50:00','purchase');
INSERT INTO events VALUES('v4','a','2026-01-01T01:21:00','view');
INSERT INTO events VALUES('v5','b','2026-01-01T01:00:00','purchase');
INSERT INTO events VALUES('v6','b','2026-01-01T01:10:00','view');
INSERT INTO events VALUES('v7','b','2026-01-01T01:20:00','start');
INSERT INTO events VALUES('v8','b','2026-01-02T01:11:00','purchase');
INSERT INTO events VALUES('v9','c','2026-01-01T02:00:00','view');
INSERT INTO events VALUES('v10','c','2026-01-01T02:05:00','purchase');

Shafi’s full answer + 2 answered follow-ups

I partition by account and order by event_at plus event_id so equal timestamps have a deterministic order. LAG gives the prior timestamp. A row starts a session if it is the first event for that account or if the gap is strictly greater than 30 minutes. Exactly 30 minutes remains in the same session.

I compute that flag in one CTE and a cumulative sum in the next. Separating the two window operations avoids nesting a window function inside another window function. The cumulative frame is explicit ROWS, which makes the per-row sequence clear even when timestamps tie. The final ordering matches the requested account, timestamp and event ID order.

This is event-time sessionization over a complete bounded input, not a permanent streaming session ID. An event arriving late between two existing sessions can bridge their gap and change the session assignment. A production incremental design needs a lateness policy and a correction range rather than blindly appending the next number.

SQL / SQLite fixture

WITH p AS (SELECT *,LAG(event_at) OVER(PARTITION BY account_id ORDER BY event_at,event_id) prev_at FROM events),
f AS (SELECT *,CASE WHEN prev_at IS NULL OR unixepoch(event_at)-unixepoch(prev_at)>1800 THEN 1 ELSE 0 END new_session FROM p)
SELECT account_id,event_id,SUM(new_session) OVER(PARTITION BY account_id ORDER BY event_at,event_id ROWS UNBOUNDED PRECEDING) session_no FROM f ORDER BY account_id,event_at,event_id;

Checked result

account_id	event_id	session_no
a	v1	1
a	v2	1
a	v3	1
a	v4	2
b	v5	1
b	v6	1
b	v7	1
b	v8	2
c	v9	1
c	v10	1

Interviewer follow-ups, fully answered

1. Why not group by a half-hour bucket?

Fixed buckets answer a different question. Events at 10:29 and 10:31 are two minutes apart but fall into different half-hour buckets. Conversely, a session can continue for hours if every adjacent gap stays within 30 minutes. LAG compares the actual adjacent-event gap and preserves that definition.

2. How do duplicates affect the query?

I would establish canonical event identity before sessionization. If the same event is replayed, counting it twice inflates session activity. If two different events share a timestamp, I preserve both and order by a stable event key. If identical IDs have conflicting payloads, I need a source version or explicit conflict rule rather than an arbitrary DISTINCT.

STAGE 3 / 30:00 TO 40:00 / 10 MIN

For every document return id,service,latency_ms. Invalid JSON behaves as an empty object. Accept latency only when its JSON type is integer or real; missing, null and text become SQL NULL.

Technical scenario

Complete input fixture

Run this fixture before the answer query

CREATE TABLE documents(id TEXT PRIMARY KEY,payload TEXT);
INSERT INTO documents VALUES('j1','{"service":"s1","latency_ms":12,"tags":["prod","blue"]}');
INSERT INTO documents VALUES('j2','{"service":"s1","latency_ms":"oops","tags":[]}');
INSERT INTO documents VALUES('j3','not json');
INSERT INTO documents VALUES('j4','{"service":"s2","latency_ms":null,"tags":"single"}');
INSERT INTO documents VALUES('j5','{"service":"s2"}');

Shafi’s full answer + 2 answered follow-ups

I normalize invalid JSON to an empty object before extracting fields. For latency, I check the JSON type and accept only integer or real. Missing keys, JSON null and text such as "42" become SQL NULL under this contract. Casting any text that looks numeric would silently broaden the accepted data definition.

I keep every document in the output because this is a left-preserving extraction task, not a filter for valid latency. The result below distinguishes extraction from quality reporting. In a production pipeline I would also count invalid JSON and rejected latency types separately so that a growing error rate is visible. JSON function names and malformed-document behavior vary by warehouse, so I would adapt the functions to the declared dialect rather than copy SQLite syntax into Redshift or Oracle.

SQL / SQLite fixture

WITH clean AS (SELECT id,CASE WHEN json_valid(payload) THEN payload ELSE '{}' END p FROM documents)
SELECT id,json_extract(p,'$.service') service,CASE WHEN json_type(p,'$.latency_ms') IN ('integer','real') THEN CAST(json_extract(p,'$.latency_ms') AS REAL) END latency_ms FROM clean ORDER BY id;

Checked result

id	service	latency_ms
j1	s1	12.0
j2	s1	NULL
j3	NULL	NULL
j4	s2	NULL
j5	s2	NULL

Interviewer follow-ups, fully answered

1. Would you accept negative latency or a huge number?

The stated task constrains JSON type, not a valid latency range, so the query follows that contract. For a production metric I would ask for the units, nonnegative rule and maximum supported value, then classify out-of-range values explicitly. I would not invent a threshold and silently discard rows.

2. How would you flatten an array inside each document?

I would first define one output row per parent ID and array element, preserve the element position if order matters and use the dialect’s supported array expansion. I would expand only the intended array. Expanding two independent arrays in the same row can create a Cartesian product, so I would aggregate or join them only through an explicit relationship.

STAGE 4 / 40:00 TO 48:00 / 8 MIN

Describe a time you had to replace a familiar technical approach.

Constructed personal incident; resume program anchor

Shafi’s full answer + 2 answered follow-ups

During a reporting migration, my familiar approach was to translate the existing SQL logic and then tune individual expressions. The first distributed version was correct on a small sample but behaved poorly on a larger, skewed input. I realized that translating syntax had not given me a reliable model of the execution work.

I separated what I understood from what I needed to learn: how the join moved rows, where the shuffle occurred and why one key produced a long task. I studied the executed plan and stage evidence, created a small skewed input, and asked a more experienced engineer to challenge my explanation. I tested a valid preaggregation against the unaggregated version and checked that it preserved the reporting grain.

In this rehearsal I used twelve correctness fixtures and then two shadow reporting days across six metrics. The useful outcome was identifying which transformation reduced unnecessary movement while preserving the output. I documented both the cases where it helped and the queries where it did not apply. I would only attach a performance percentage to a real measured comparison, not to this constructed story.

I shared the example with the team as a short review exercise: predict the output grain, identify the movement, then compare runtime evidence. That made the learning reusable without turning my one experiment into a universal rule.

The lesson was that experience in one execution model can create overconfidence in another. I learned to test the physical mechanism and the business result together, and to revise my preferred approach when the evidence disagreed.

Interviewer follow-ups, fully answered

1. What evidence changed your mind?

The executed stage showed a long task associated with skewed input and avoidable row movement. A small skewed fixture reproduced the mechanism, while the same calculation on an ordinary sample had hidden it. That combination was stronger evidence than a general claim that distributed SQL is faster or slower.

2. Why was preaggregation safe in this case?

The downstream question needed the same grouping keys and additive measures that the preaggregation preserved. I checked the output grain and compared all six metrics on the correctness fixtures and shadow reporting days. I would not apply the rewrite to a distinct-person measure or a filter that depended on the original row detail without a separate proof.

STAGE 5 / 48:00 TO 56:00 / 8 MIN

Tell me about a commitment you had to reset.

Constructed personal incident; resume program anchor

Shafi’s full answer + 2 answered follow-ups

I committed to a reporting delivery before I had fully validated the exception handling in two source feeds. As implementation progressed, I found that completing those feeds under the original date would require either hiding unresolved identities or weakening the acceptance checks. I had made the commitment, so I needed to own the gap instead of presenting it as somebody else’s delay.

I raised the issue before the promised review. I explained that six of the eight feeds met the agreed checks and two did not, why my original estimate had been incomplete, and what the missing feeds meant for the customer’s decision. I asked the partner which decision had to be made on the original date rather than assume a partial delivery would help.

I proposed two concrete choices: a clearly labeled six-feed view if that population supported the immediate decision, or a complete view after a short revised checkpoint. I kept the missing scope visible, named the repair owner and agreed when I would report progress even if nothing had improved. I did not call the partial output complete.

The partner accepted the bounded view for the immediate task, and the remaining two feeds passed review three days later. The ongoing improvement was adding source-exception discovery to commitment planning and verifying the acceptance scope before promising a date.

The lesson was that trust depends on making the other person’s planning more reliable. Early candor, a useful alternative and follow-through mattered more than defending my original estimate.

Interviewer follow-ups, fully answered

1. How did you avoid making the customer solve your engineering problem?

I explained the decision impact in terms of the population available on the promised date, not a list of implementation difficulties. I offered a bounded six-feed view and a complete-view checkpoint, with a recommendation tied to the immediate customer decision. I retained ownership of fixing the other feeds and reporting progress.

2. What changed in your future estimates?

I moved source-exception discovery into planning and required an acceptance check for each feed before treating the work as a routine translation. I also exposed the uncertain dependency separately from implementation effort. The goal was a commitment the partner could plan around, not padding every estimate.

STAGE 6 / 56:00 TO 60:00 / 4 MIN

What questions do you have for me, and how would you close?

Role-focused closing

Shafi’s full answer + 2 answered follow-ups

I have two questions. Which SQL engine and data-access patterns dominate the team’s work? How do engineers learn the business definitions behind a metric before they change its implementation?

The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.

Interviewer follow-ups, fully answered

1. What would you focus on during your first month?

I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.

2. What would you do if the team’s approach differed from your previous company?

I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.

29 / Complete interview 14 min read · 0 min practice

Full mock C / Ordered funnels, reconciliation and learning under pressure

Six stages, one 60-minute interview. Every question has a complete first-person answer and two answered follow-ups.

Evidence boundary: Technical cases are worked examples. Personal incidents and their specific outcomes are constructed rehearsals anchored to resume programs, not verified autobiographical claims. The introduction reflects the supplied background.

Read the complete transcript on pass 1. On pass 2, keep answers closed and use the runner to speak, code and defend your decisions. Reading is counted in this chapter; one live mock is counted in the runner section. The all-three-mocks total adds two more hours.

STAGE 1 / 00:00 TO 05:00 / 5 MIN

Shafi, introduce yourself and connect your experience to this interview.

Resume-based introduction

Shafi’s full answer + 2 answered follow-ups

I am a data engineer with 16-plus years of experience across enterprise data platforms and product analytics. My background includes telecom data migration at Sprint, the Citi analytics platform and Meta product and payments data. At Meta, my work included Automotive and Travel data foundations, first-party data pipelines, shared metrics and cross-functional delivery. I have led technical work without treating leadership as a reporting-line title.

I treat a SQL answer as a claim that must survive a counterexample. My experience in product analytics makes me especially careful about event sequences, eligible populations and metrics that can look correct in aggregate while hiding a missing customer segment.

For this role, I am interested in turning complex service and product data into reliable decisions. I would bring the same discipline I use in production: define what the data means, make the implementation understandable, test failure cases and own the operating outcome.

Interviewer follow-ups, fully answered

1. What did you personally own rather than the broader team?

I owned data-engineering decisions and implementation within the programs I worked on: defining transformations and models, resolving data-quality questions, coordinating dependencies and validating outputs. Product managers set product priorities and partner engineers owned other systems. I distinguish those contributions when I describe a result; the size of a product does not make all of its revenue my personal impact.

2. Why are you interested in this Data Engineer II role?

The work described in the posting is close to problems I enjoy: scalable data products, analytics foundations and engineering decisions that serve real consumers. I would evaluate the role through its ownership, customer problems and opportunity to contribute. My experience can help me work independently and mentor through technical judgment, while I remain hands-on with the implementation.

STAGE 2 / 05:00 TO 30:00 / 25 MIN

Anchor each account on its first view. Count viewed_accounts, started_accounts, purchased_accounts. Start must occur strictly after view; purchase strictly after that first valid start; both within 24 hours inclusive of the view. No re-anchoring on later views.

Technical scenario

Complete input fixture

Run this fixture before the answer query

CREATE TABLE events(event_id TEXT PRIMARY KEY,account_id TEXT,event_at TEXT,kind TEXT);
INSERT INTO events VALUES('v1','a','2026-01-01T00:00:00','view');
INSERT INTO events VALUES('v2','a','2026-01-01T00:20:00','start');
INSERT INTO events VALUES('v3','a','2026-01-01T00:50:00','purchase');
INSERT INTO events VALUES('v4','a','2026-01-01T01:21:00','view');
INSERT INTO events VALUES('v5','b','2026-01-01T01:00:00','purchase');
INSERT INTO events VALUES('v6','b','2026-01-01T01:10:00','view');
INSERT INTO events VALUES('v7','b','2026-01-01T01:20:00','start');
INSERT INTO events VALUES('v8','b','2026-01-02T01:11:00','purchase');
INSERT INTO events VALUES('v9','c','2026-01-01T02:00:00','view');
INSERT INTO events VALUES('v10','c','2026-01-01T02:05:00','purchase');

Shafi’s full answer + 2 answered follow-ups

I anchor each account on its first view. I then find the first start strictly after that view and no more than 24 hours later. Finally, I look for a purchase strictly after that qualifying start and still within 24 hours of the original view. The window is anchored on the view, not restarted at the start event.

I preserve the view population with left joins, then count accounts reaching each stage. I do not join every view to every start and purchase because that multiplies opportunities and may count an invalid later path. I also do not count purchases independently of valid starts. The fixture includes boundary and ordering cases so the query has to enforce the definition rather than rely on favorable timestamps.

At scale I would prune to the relevant event types and bounded source dates, retain enough look-ahead for the final anchor date and consider a canonical account-event table. I would compare any rewrite on the exact boundary cases, including equality at the 24-hour limit and equal-time events that fail the strict-after requirement.

SQL / SQLite fixture

WITH v AS (SELECT account_id,MIN(event_at) viewed_at FROM events WHERE kind='view' GROUP BY account_id),
s AS (SELECT v.account_id,v.viewed_at,MIN(e.event_at) started_at FROM v LEFT JOIN events e ON e.account_id=v.account_id AND e.kind='start' AND e.event_at>v.viewed_at AND unixepoch(e.event_at)<=unixepoch(v.viewed_at)+86400 GROUP BY v.account_id,v.viewed_at),
p AS (SELECT s.account_id,s.started_at,MIN(e.event_at) purchased_at FROM s LEFT JOIN events e ON e.account_id=s.account_id AND e.kind='purchase' AND e.event_at>s.started_at AND unixepoch(e.event_at)<=unixepoch(s.viewed_at)+86400 GROUP BY s.account_id,s.started_at)
SELECT COUNT(*) viewed_accounts,COUNT(started_at) started_accounts,COUNT(purchased_at) purchased_accounts FROM p;

Checked result

viewed_accounts	started_accounts	purchased_accounts
3	2	1

Interviewer follow-ups, fully answered

1. What if a later view would create a successful funnel?

Under the stated first-view contract I do not re-anchor. An account that fails its first-view window remains a failure for this metric even if a later attempt succeeds. If the customer wants attempt-level conversion, I would define attempts and their event assignment separately rather than alter the account-level metric invisibly.

2. What is the denominator of conversion?

For this query, viewed_accounts is the first-stage population. Started conversion is started_accounts divided by viewed_accounts, and purchase conversion is purchased_accounts divided by viewed_accounts. If I report purchase-after-start conversion, its denominator is started_accounts and I label it separately. Zero denominators produce undefined or NULL rates, not a fabricated zero-percent performance.

STAGE 3 / 30:00 TO 40:00 / 10 MIN

Return id,old_value,new_value,issue for changed or missing keys. Label changed, missing_new or missing_old. Sort by id.

Technical scenario

Complete input fixture

Run this fixture before the answer query

CREATE TABLE old_values(id TEXT PRIMARY KEY,value INTEGER);
INSERT INTO old_values VALUES('a',100);
INSERT INTO old_values VALUES('b',200);
INSERT INTO old_values VALUES('c',NULL);
INSERT INTO old_values VALUES('e',NULL);
INSERT INTO old_values VALUES('f',NULL);
CREATE TABLE new_values(id TEXT PRIMARY KEY,value INTEGER);
INSERT INTO new_values VALUES('a',100);
INSERT INTO new_values VALUES('b',250);
INSERT INTO new_values VALUES('d',400);
INSERT INTO new_values VALUES('e',0);
INSERT INTO new_values VALUES('f',NULL);

Shafi’s full answer + 2 answered follow-ups

I compare keys from both sides so that missing rows are visible in either direction. I separate absence from a present row whose value is NULL. A simple inner join would miss deletions and additions; a plain inequality would also miss differences involving NULL because SQL comparison can evaluate to unknown.

The query below returns changed values and keys missing from one side, with deterministic labels and ordering. This fixture assumes each side is unique by id. I would assert that assumption before trusting the comparison. Equal overall sums would not be sufficient evidence because a positive error in one key can cancel a negative error in another.

For a migration, I would run this at a common source cutoff and then compare null counts, key coverage and selected segment aggregates. If versions differ, I would first align the comparison snapshot so I do not mistake normal late updates for a migration defect.

SQL / SQLite fixture

SELECT o.id AS id,o.value old_value,n.value new_value,CASE WHEN n.id IS NULL THEN 'missing_new' ELSE 'changed' END issue FROM old_values o LEFT JOIN new_values n ON o.id=n.id WHERE n.id IS NULL OR o.value IS NOT n.value
UNION ALL
SELECT n.id,NULL,n.value,'missing_old' FROM new_values n LEFT JOIN old_values o ON n.id=o.id WHERE o.id IS NULL ORDER BY id;

Checked result

id	old_value	new_value	issue
b	200	250	changed
c	NULL	NULL	missing_new
d	NULL	400	missing_old
e	NULL	0	changed

Interviewer follow-ups, fully answered

1. What if a key occurs twice on one side?

Then this key-level comparison is underspecified and the join can multiply rows. I would either reject duplicate keys because the contract requires uniqueness or reconcile the multiset of full rows using grouped counts. I would not deduplicate arbitrarily, because the duplicate itself may be the defect we need to detect.

2. How would you compare floating-point measures?

I would use the documented precision contract. Exact integer cents or fixed-scale decimals should compare exactly. For genuinely approximate floating-point measures I would define both absolute and relative tolerances and handle NULL and non-finite values separately. The tolerance would be approved and reported, not tuned until discrepancies disappeared.

STAGE 4 / 40:00 TO 48:00 / 8 MIN

Tell me about learning a new domain quickly enough to make a decision.

Constructed personal incident; payments program anchor

Shafi’s full answer + 2 answered follow-ups

During a payments reporting change, I found that our proposed net metric combined order value, capture value and refunds as if they were the same event. I understood the SQL, but I did not yet understand the lifecycle well enough to defend that definition. My responsibility was to deliver the transformation and explain its meaning to the consumer.

I traced three cases with the payments owner: a fully captured order, a partial capture and a refund recorded on a later date. I wrote the expected output at each step and compared it with the proposed join. That exercise exposed both a fanout problem and a timing mismatch.

I changed the design to keep the process facts separate, aggregate them to a compatible order grain and label the reporting dates explicitly. I reviewed the three cases with the consumer before extending the implementation. I also added a fourth case with an orphan refund because the first cases assumed a relationship that might be missing in production.

The review accepted the contract, and all four examples became regression cases. My contribution was turning a domain gap into a small set of answerable questions and then changing the model. I learned that asking an expert to review concrete rows gives better feedback than asking whether a diagram looks reasonable.

Interviewer follow-ups, fully answered

1. Why did you not just ask the domain expert for the SQL?

The expert owned the payment meaning; I owned the data transformation. I needed to understand the semantics well enough to handle future cases and operate the pipeline. I asked the expert to validate expected outcomes, then implemented and tested the query myself.

2. What would you do if the experts disagreed?

I would show the exact case where their definitions produce different results, identify which business decision each definition supports and ask the accountable metric owner to choose. If both were legitimate, I would publish separate named measures rather than blend them into an ambiguous net number.

STAGE 5 / 48:00 TO 56:00 / 8 MIN

Tell me about maintaining trust when a stakeholder wanted an answer you could not yet support.

Constructed personal incident; product analytics anchor

Shafi’s full answer + 2 answered follow-ups

A product partner needed a regional comparison for a planning meeting, but one region had a different source cutoff. The combined total looked plausible, and the pressure was to send the full comparison immediately. I owned the dataset and the explanation that would accompany it.

I reproduced the cutoff difference with the source timestamps and showed the partner one regional example where mixing snapshots changed the direction of the comparison. I explained that the current result could not support the requested conclusion. I then asked whether the meeting required all regions or whether a common-cutoff subset could answer the immediate question.

We agreed to use the aligned regions for the meeting with the missing scope stated next to the chart. I sent the complete comparison after the delayed region reached the same cutoff and included a short note explaining the difference. I added a cutoff-consistency check to the release process so the next report would not rely on somebody noticing the mismatch manually.

The partner used the bounded comparison and accepted the later complete version. I did not call the first report complete or hide the limitation in a footnote. The lesson was that trust improves when I make the decision boundary clear and still provide useful work within it.

Interviewer follow-ups, fully answered

1. What if the meeting required every region?

I would state that the current data could not support the full comparison and offer the latest aligned historical snapshot if it answered the question. If no valid snapshot existed, I would give the recovery time and evidence needed to release it. A deadline does not make incompatible data comparable.

2. How did you check that the communication worked?

I asked the partner to restate which population and cutoff the meeting chart represented, and I reviewed the final chart label. After the complete release I confirmed whether the changed scope affected any decision. Sending a message alone would not prove that the limitation was understood.

STAGE 6 / 56:00 TO 60:00 / 4 MIN

What questions do you have for me, and how would you close?

Role-focused closing

Shafi’s full answer + 2 answered follow-ups

I have two questions. How does the team decide when a metric is ready for a customer-facing decision? What does a strong SQL engineer on this team do beyond producing a correct query?

The reason I ask is that I want to understand the decisions the data supports and where the team’s operating burden sits. My experience is most useful when I can connect a data contract to a customer outcome and then carry the implementation through validation and operation. Thank you for the discussion; the problems we covered reinforce my interest in doing that work here.

Interviewer follow-ups, fully answered

1. What would you focus on during your first month?

I would learn one important consumer workflow end to end, trace its data sources and definitions, and understand the team’s release and incident practices. I would pair with an engineer on a small, useful change rather than propose a redesign before understanding the constraints. By the end of the month I would aim to own a bounded improvement with an agreed acceptance test and a clear operational handoff.

2. What would you do if the team’s approach differed from your previous company?

I would first understand which constraints the approach solves: scale, staffing, correctness, cost or customer expectations. I would compare mechanisms rather than brand names. If I saw a material issue, I would bring a concrete example and an alternative, then work through the accountable owner. Once a legitimate decision was made, I would help implement it well.

30 / Recall 2 min read · 10 min practice

Final desk card and readiness gates

Before coding	Before declaring done	Behavior
Output grain; population; time; ties; nulls; duplicates.	Walk the result; test a counterexample; explain scale; name remaining assumptions.	Specific incident; your decision; evidence; outcome; changed behavior.

Target: all thirty SQL fixtures matched cold, 30/30 knowledge checks, every chapter recalled, four personally supported incidents, eighteen full-mock stages nailed, all readiness gates and zero unresolved repair ratings. These are self-recorded preparation measures, not Amazon’s internal scorecard. A correct fixture result still needs a general explanation.

I declare output grain, population and join cardinality before coding.I handle nulls, duplicates, ties, zero activity and time boundaries.I explain frame, partition, stage order and final result order.I can write and defend sessions, funnels, retention and rolling distincts.I can extract typed JSON, expand arrays, traverse a hierarchy and pivot output.I distinguish semantic correctness from physical performance and dialect syntax.I have a primary and backup learning incident with a changed decision.I have a primary and backup trust incident with candid ownership and follow-through.I repeated the weakest queries and behavioral prompts cold after a break.

31 / References 3 min read · 2 min practice

Coverage, sources and revision record

Prepared November 13, 2026. The local recruiter call establishes SQL, Learn and Be Curious and Earn Trust. SQL Problem Solving.pdf supplies the topic coverage. The scenarios, data, questions and self-rating rules are original preparation material, not Amazon’s internal question bank.

Local source	Used for	Boundary
Recruiter Call for next role.txt	Confirmed competency bundle.	Not scheduled order or coding dialect.
SQL Problem Solving.pdf	Joins, grouping, windows, set operators, JSON, hierarchy, pivoting, keys, dimensions and performance.	Its topic list is not a promise of exact questions.
MShafi_ResumeDE (1).pdf	Meta identity/shared metrics, Sprint migration and Citi delivery anchors.	Rehearsal incident details remain constructed; no invented achievements asserted as fact.
Round 1-3 guides	Companion foundations and model/tuning detail.	Separate artifacts and progress stores.

Primary reference	Use
Amazon Leadership Principles	Official Learn and Be Curious and Earn Trust definitions.
SQLite SELECT	Row filtering, grouping, compound queries and ordering; SQLite-specific deviations are not portable rules.
SQLite expressions	NULL, IN/EXISTS, CASE and cast behavior.
SQLite window functions	Ranking, frames and supported window syntax for the offline lab.
SQLite date/time functions	UTC fixture date modifiers and unixepoch arithmetic.
SQLite JSON functions	Validity, typed extraction and array expansion.
SQLite WITH and recursion	Recursive anchor/step mechanics and termination.
Redshift SUM windows	Explicit frames for ordered aggregate windows; DISTINCT not supported here.
Redshift QUALIFY	Filter window results without a nested query.
Redshift JSON_PARSE	Parsing JSON into SUPER rather than assuming SQLite JSON syntax.
Oracle 19c analytic functions	Analytic evaluation, ordering and frame semantics for the Oracle baseline.
PostgreSQL 18 EXPLAIN	Actual versus estimated execution evidence and ANALYZE execution behavior.
Redshift table design	Columnar physical layout and workload-specific design.
sql.js project	Pinned 1.13.0 runtime embedded with its MIT license; no remote runtime fetch.

Two content passes

First pass: map recruiter topics to worked examples and behavioral evidence. Second pass: independently specify expected rows, execute SQL, challenge common wrong alternatives, review boundaries and test the offline interface. The build checks 30 queries, shuffled replay invariance, recursive cycles, empty requirement sets and odd/even median cases. Browser verification runs the same solutions through the embedded engine.

Revision log

Revision	Date	Changes
1.0	2026-11-13	Thirty runnable SQL labs; four clearly labeled behavioral rehearsals with twenty-four answered probes; knowledge checks; offline worker execution with cancellation/limits; timing; cold/assisted attempt tracking; three mocks; saved progress; print layout.

sql.js 1.13.0 is pinned and its npm archive integrity was verified. Its MIT license is embedded below. SQLite behavior is labeled; Redshift and Oracle examples are illustrative translations and were not executed against those systems. Browser queries run only on fictional in-memory data. No Gmail actions or remote data connections are involved.

Embedded runtime license

Revision 1.2 / 2026-11-13

Added three complete 60-minute interview transcripts: 18 first-person primary answers and 36 fully answered follow-ups. Added complete technical fixtures and checked solutions where relevant, linked all stages to the existing timer and repair queue, and recalculated sidebar and full-guide study times. Preserved existing saved notes and migrated prior mock ratings into individual practice.

Write the query.Defend the answer.

How will you approach a SQL coding problem?

Count the right population

Preserve accounts with no orders

Null-safe non-buyers

Filter groups after rows

Join three processes without inflation

Write paid-account totals, preserve non-buyers, then add payment and refund totals.

Top two distinct sales levels

Exactly two products at most

Most frequent name in each group

Latest valid state including deletes

Why would ROW_NUMBER, RANK and DENSE_RANK produce different top-two answers?

Why must deletion filtering follow latest-version selection?

Running sales by calendar day

Day-over-day change with zero baseline

True last order amount

Half-open time filter

How do you compute day-over-day growth and a running daily total correctly?

Three-calendar-day total

Distinct users over three days

Why can a rolling distinct-user count not be built by summing daily distinct counts?

Consecutive-day activity islands

Sessionize with an exact boundary

Write session IDs and consecutive-day streaks, then explain the difference.

Ordered first-view conversion funnel

D1 retention with censoring

Build a view-to-start-to-purchase funnel and next-day retention.

As-of plan on each order

Full reconciliation including NULL

Detect invalid temporal intervals

How would you prove two query outputs are equivalent?

Set versus bag

Conditional aggregate pivot

How do you pivot without a dialect-specific PIVOT operator?

Typed JSON scalar extraction

Expand tags but preserve empty parents

Extract a numeric JSON field and expand an optional array safely.

Recursive hierarchy with cycle guard

Accounts using every required service

Median paid order amount

How do you keep a recursive hierarchy query from running forever?

Your query is correct but slow on production-sized data. What would you change first?

You remember the logic but not one function’s exact dialect syntax. What do you do?

What have you learned recently, and how did it change your work?

Tell me about something important you had to learn to solve a problem.

Six follow-ups and answer direction

Tell me about a time learning changed an approach you were confident in.

Six follow-ups and answer direction

A stakeholder no longer trusts your dashboard. What do you do?

Tell me about a time you had to admit you were wrong.

Six follow-ups and answer direction

Tell me about a time you had to reset a commitment with a stakeholder.

Six follow-ups and answer direction

You find a mistake in your query after confidently explaining the result. What do you say?

Ask two useful questions and close the interview.

Learning identity semantics before choosing an algorithm

Replacing a familiar SQL mental model with distributed evidence

Correcting your own confident metric explanation

Resetting a delivery commitment before the surprise

Shafi, introduce yourself and connect your experience to this interview.

Interviewer follow-ups, fully answered

1. What did you personally own rather than the broader team?

2. Why are you interested in this Data Engineer II role?

For order o1, return order_id, line_cents, captured_cents, refund_cents, net_collected. Aggregate each process before combining.

Interviewer follow-ups, fully answered

1. What if a refund has no matching order line?

2. How would you report product-level net revenue?

Return every calendar day and cumulative paid cents through that day, including zero-sales days, sorted by day.

Interviewer follow-ups, fully answered

1. How would you add a trailing seven-day total?

2. What changes when the business day is not UTC?

Tell me about learning something that changed your data design.

Interviewer follow-ups, fully answered

1. How did you avoid spending weeks learning without shipping?

2. What would have happened if you had kept the original lookup?

Tell me about earning back trust after you were wrong.

Interviewer follow-ups, fully answered

1. What exactly did you say to the partner?

2. How did you know the repair addressed the original mistake?

Write the query.
Defend the answer.