DEV Community

SQL Interview Patterns
SQL Interview Patterns

Posted on

5 SQL traps that sink data engineering interviews (with runnable examples)

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)