SQL for Data Engineers: The Queries You Write at Work, and How Interviews Test Each One

Revenue doubled after a join

WITH items AS (SELECT order_id, COUNT(*) AS item_count FROM order_items GROUP BY order_id) SELECT o.order_date, SUM(o.amount) AS revenue, SUM(i.item_count) AS items FROM orders o LEFT JOIN items i ON i.order_id = o.order_id GROUP BY o.order_date. Joining orders straight to order_items repeats each amount once per item; aggregating items first keeps the grain.

Keep the latest row per customer

WITH ranked AS (SELECT customer_id, city, updated_at, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, ingested_at DESC) AS rn FROM raw_customers) SELECT customer_id, city, updated_at FROM ranked WHERE rn = 1. RANK would keep both rows when two share an updated_at; the second ORDER BY column makes the winner the same on every run.

An upsert that is safe to rerun

MERGE INTO dim_product AS t USING staged_products AS s ON t.product_id = s.product_id WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET t.name = s.name, t.updated_at = s.updated_at WHEN NOT MATCHED THEN INSERT (product_id, name, updated_at) VALUES (s.product_id, s.name, s.updated_at). Run it twice with the same batch and the second run changes nothing.

SCD Type 2 in two statements

Close changed rows: MERGE INTO dim_customer AS t USING changes AS s ON t.customer_id = s.customer_id AND t.is_current = true WHEN MATCHED AND t.city IS DISTINCT FROM s.city THEN UPDATE SET t.is_current = false, t.valid_to = s.updated_at. Insert new versions: INSERT INTO dim_customer (customer_id, city, valid_from, valid_to, is_current) SELECT s.customer_id, s.city, s.updated_at, NULL, true FROM changes AS s LEFT JOIN dim_customer AS t ON t.customer_id = s.customer_id AND t.is_current = true WHERE t.customer_id IS NULL. A rerun after a failure between the two is safe.

Read the plan before you tune

EXPLAIN FORMATTED SELECT c.city, SUM(o.amount) AS revenue FROM orders o JOIN dim_customer c ON c.customer_id = o.customer_id WHERE o.order_date >= :start_date GROUP BY c.city. Check the orders scan for PartitionFilters on order_date. If dim_customer is small and the join is a SortMergeJoin, broadcast it so orders is not shuffled.

Duplicate current keys must return zero rows

SELECT customer_id, COUNT(*) AS n FROM dim_customer WHERE is_current = true GROUP BY customer_id HAVING COUNT(*) > 1. Any row returned means two current versions of one customer, so the job fails before the table is published.