Most data-engineering SQL rounds aren't hard because of syntax. They're hard because the query runs and returns a wrong answer that looks right. Here are five traps I see again and again, each with a tiny example you can run in Postgres or DuckDB.
1. The join fan-out
You want completed orders and revenue per customer, so you join orders to order_items and COUNT(o.order_id).
In my practice dataset, one customer has 6 completed orders. The naive query says 14, because every order is repeated once per line item.
-- wrong: counts line items
SELECT o.customer_id, COUNT(o.order_id)
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.customer_id;
Fix: aggregate the child table to the grain you need first (one row per order), then join. COUNT(DISTINCT ...) fixes the count but not the sums.
2. NOT IN and a single NULL
SELECT 1 WHERE 3 NOT IN (1, 2, NULL); -- returns 0 rows
x NOT IN (..., NULL) is never true. If the subquery behind your anti-join ever returns a NULL, you silently get an empty result. Use NOT EXISTS.
3. "Latest record" ordered by the wrong timestamp
CDC feeds deliver changes late. A customer moves to Hyderabad in April. A February change (to Chennai) arrives in June.
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY updated_at DESC, -- event time first
ingested_at DESC) -- arrival time only breaks ties
Order by arrival time and you'll confidently report Chennai. The query runs, the answer is wrong.
4. A "7-day" rolling average that covers 3 weeks
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
That's the last 7 rows. If days with no sales have no row, the window stretches. Build a date spine first (recursive CTE, generate_series, or a dim_date table), fill the gaps with 0, and then the window covers exactly 7 days. Start the spine 6 days before your reporting window so the first day has a full lookback.
5. An upsert that fails on its own batch
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
That's what Postgres says when your incoming batch contains the same key twice. MERGE in other engines fails in a similar way. Dedup the batch first (latest version per key), and only update when the incoming row is newer:
... ON CONFLICT (order_id) DO UPDATE
SET status = EXCLUDED.status, updated_at = EXCLUDED.updated_at
WHERE EXCLUDED.updated_at > orders.updated_at;
Now running the same batch twice changes nothing. That's idempotency, and interviewers love it when you say the word.
Want to practise these on real data? I put together 8 problems like these (gaps & islands, sessionization, SCD2, upserts…) with a practice database and real outputs. It's free: https://masteryoursyche.gumroad.com/l/free-sql-interview-sample
Disclosure: I wrote it. There's also a paid full version (48 problems in 16 patterns) linked inside the sample, but the free one stands on its own.
Top comments (0)