SQL: one problem shape at a time

Look for

• Only the rows that meet a condition
• Biggest, newest or lowest first
• Rows where a value is missing

Smallest shape

SELECT order_id, user_id, order_total
FROM orders
WHERE order_status = 'paid' AND order_total > 1300
ORDER BY order_total DESC, order_id;

The usual trap

A missing value is never equal to anything, not even to NULL. On telemetry, WHERE reading = NULL returns 0 rows, while WHERE reading IS NULL returns 25. Use IS NULL and IS NOT NULL.

Not this

If the prompt says per or for each, the answer has 1 row per group, so it is an aggregate, not a filter. WHERE removes rows before anything is counted.

Look for

• 1 row per department, customer or day
• Count, total or average of something
• Only groups that are bigger than some size

Smallest shape

SELECT order_status, COUNT(*) AS orders, ROUND(SUM(order_total), 2) AS revenue
FROM orders
GROUP BY order_status
HAVING COUNT(*) > 29  -- HAVING filters groups, WHERE filters rows
ORDER BY orders DESC;

The usual trap

COUNT(*) counts rows, COUNT(column) skips NULLs. On telemetry, COUNT(*) is 480 and COUNT(reading) is 455, and AVG(reading) divides by 455, not 480. Say which one the prompt means.

Not this

A running total or a rank keeps every input row and adds a column (a window function, covered in the window steps). GROUP BY collapses the rows into one per group.

Look for

• Months or categories as columns
• Wide table that should be long

Smallest shape

SELECT CAST(created_at AS DATE) AS day,
       SUM(CASE WHEN order_status = 'paid' THEN order_total END) AS paid,  -- CASE keeps only paid rows in this sum
       SUM(CASE WHEN order_status = 'refunded' THEN order_total END) AS refunded
FROM orders
GROUP BY 1
ORDER BY 1;

The usual trap

A pivot cell with no matching rows is NULL, not 0. In orders, 2 days have no refunded order, so their refunded cell is NULL, because SUM of nothing is NULL. Wrap it in COALESCE only when the business means zero.

Not this

CASE on its own only labels each row. A pivot wraps CASE in SUM and GROUP BY so that values become column names.

Look for

• Combine 2 tables on a shared key
• A number from one table next to a name from another
• Totals that look too big after a join

Smallest shape

SELECT CAST(o.created_at AS DATE) AS day,
       ROUND(SUM(i.quantity * i.unit_price), 2) AS item_revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_status = 'paid'
GROUP BY 1
ORDER BY 1;

The usual trap

The grain of a table is what 1 row means: one order, or one order line. A join copies each order's total onto every one of its item rows, so a sum of order_total after joining order_items is 194199.76 over 356 joined rows, while the orders table alone adds up to 83416.15. Sum a column that belongs to the item rows, or aggregate first and then join.

Not this

If you only need to know whether a match exists, and do not need columns from the other table, a join is the wrong tool. Use EXISTS or an anti-join (rows with no match, covered later) so rows are not repeated.

Look for

• Compare a row with another row of the same table
• Employee and their manager
• Pairs of orders by the same user

Smallest shape

SELECT a.user_id, a.order_id AS first_order, b.order_id AS later_order,
       date_diff('hour', a.created_at, b.created_at) AS hours_apart  -- DuckDB function
FROM orders a
JOIN orders b ON a.user_id = b.user_id AND a.order_id < b.order_id
ORDER BY hours_apart
LIMIT 3;

The usual trap

Without a rule to order the pair, every pair appears twice and every row pairs with itself. On orders, a.order_id < b.order_id gives 179 pairs, a.order_id <> b.order_id gives 358, and a plain join on user_id gives 538.

Not this

If you only need the previous or next row in order, LAG and LEAD are simpler than joining the table to itself.

Look for

• Customers who never ordered
• Rows in A that are missing from B
• Rows with no matching parent row (orphans)

Smallest shape

SELECT o.order_id, o.order_status
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL  -- NULL here means no payment row matched
ORDER BY o.order_id;

The usual trap

NOT IN returns nothing if the subquery contains a single NULL. Counting orders with no payment: NOT IN gives 30, but add one NULL to the payments list and it gives 0. NOT EXISTS still gives 30.

Not this

A LEFT JOIN on its own keeps the unmatched rows and the matched ones. It becomes an anti-join only when you then keep the rows where the right side is NULL.

Look for

• 1 row per key, the most recent
• The same record was sent more than once
• Keep the latest status of each order

Smallest shape

SELECT user_id, order_id, created_at
FROM orders
QUALIFY ROW_NUMBER() OVER (  -- QUALIFY filters on a window result (DuckDB, Snowflake, BigQuery)
  PARTITION BY user_id
  ORDER BY created_at DESC, order_id DESC
) = 1
ORDER BY user_id;

The usual trap

SELECT DISTINCT only removes rows that are identical in every column. On ecommerce_events, 812 rows stay 812 after DISTINCT *, but there are only 720 event_ids, because a replayed event is sent again with a later ingest_time, so the copies differ. Choose the key, then choose which copy wins.

Not this

Top N keeps the best N rows of each group by a value. Latest per key keeps exactly 1 row per key, chosen by time.

Look for

• Top 3 per category
• Second highest within each department
• Best, worst or nth inside a group

Smallest shape

SELECT user_id, order_id, order_total, rn
FROM (
  SELECT user_id, order_id, order_total,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_total DESC, order_id) AS rn
  FROM orders
) AS ranked
WHERE rn <= 2
ORDER BY user_id, rn;

The usual trap

ORDER BY ... LIMIT 2 returns 2 rows in total, not two per group. On orders it returns 2 rows, while the ROW_NUMBER version returns 130 (up to two per user). Rank inside PARTITION BY, then filter on the rank.

Not this

Latest per key looks the same, but it always keeps exactly 1 row per key. If the prompt says top N, think ranking functions. ROW_NUMBER gives every row its own number, even on ties. RANK and DENSE_RANK give tied rows the same number.

Look for

• Running total or cumulative count
• Moving or rolling average
• Percent of the grand total

Smallest shape

SELECT user_id, created_at, order_total,
       ROUND(SUM(order_total) OVER (
         PARTITION BY user_id ORDER BY created_at, order_id
       ), 2) AS running_spend
FROM orders
ORDER BY user_id, created_at;

The usual trap

A window SUM with no ORDER BY adds up the whole partition on every row. For user U003, running_spend grows 374.02, then 689.86, but without ORDER BY the column shows 1824.15 on every row. Order by time, and add a second column such as order_id so equal timestamps keep a fixed order.

Not this

GROUP BY with SUM gives one total per group and removes the detail rows. A running total keeps every row and shows the total so far next to it.

Look for

• Compared with last month or the previous row
• Change, growth or difference from before
• The next event for the same user

Smallest shape

SELECT device_id, event_time, reading,
       ROUND(reading - LAG(reading) OVER (
         PARTITION BY device_id ORDER BY event_time
       ), 3) AS change
FROM telemetry
WHERE metric = 'temp_c'
ORDER BY device_id, event_time;

The usual trap

Forget PARTITION BY and LAG looks at the previous row of a different group. On telemetry, ordering by event_time alone makes DEV-002's previous row DEV-001. Also, the first row of each group has no previous row, so its change is NULL.

Not this

A running total uses every earlier row. LAG reads exactly 1 row back, so use it when the prompt compares with the previous period, not with all periods so far.

Look for

• Consecutive days in a row
• Longest streak
• Where does an unbroken run (an island) start and end

Smallest shape

WITH days AS (
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day FROM ecommerce_events
), numbered AS (
  -- inside one unbroken run, day and row number rise together, so their difference stays the same
  SELECT user_id, day,
         day - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS INTEGER) AS grp
  FROM days
)
SELECT user_id, MIN(day) AS streak_start, COUNT(*) AS days_in_row
FROM numbered
GROUP BY user_id, grp
ORDER BY days_in_row DESC, user_id
LIMIT 3;

The usual trap

Several rows on the same day break the trick, because ROW_NUMBER grows faster than the dates. For user U001 there are 12 event rows but only 9 distinct days. Reduce to 1 row per user and day first.

Not this

Sessionisation also groups rows in a row, but the break is a time gap you choose, such as 30 minutes. Gaps and islands breaks at a missing day or number.

Look for

• Start a new session after a pause, for example 30 minutes
• Group a user's events into visits (a session is one visit)
• Bursts of activity separated by quiet gaps

Smallest shape

WITH gaps AS (
  SELECT device_id, event_time,
         LAG(event_time) OVER (PARTITION BY device_id ORDER BY event_time) AS prev
  FROM telemetry
), flagged AS (
  SELECT *, CASE WHEN prev IS NULL OR event_time - prev > INTERVAL 1 DAY THEN 1 ELSE 0 END AS new_run
  FROM gaps
)
SELECT device_id, event_time,
       SUM(new_run) OVER (PARTITION BY device_id ORDER BY event_time) AS run_no
FROM flagged
ORDER BY device_id, event_time;

The usual trap

The first event of each device (or user) has no previous event, so the gap test is NULL and counts as no new session. Without the prev IS NULL check, the first rows of DEV-001 get run_no 0 instead of 1.

Not this

Gaps and islands compare each day with a row number. Sessionisation compares each event with the one before it, marks the start of each session with a 1, and takes a running sum of those marks.

Look for

• Users who reached step 2 after step 1 (a funnel is an ordered list of steps)
• Conversion from view to cart to purchase
• Drop-off between steps

Smallest shape

WITH v AS (
  SELECT user_id, MIN(event_time) AS t FROM ecommerce_events WHERE event_type = 'page_view' GROUP BY user_id
), c AS (
  SELECT e.user_id, MIN(e.event_time) AS t
  FROM ecommerce_events e JOIN v ON v.user_id = e.user_id AND e.event_time > v.t
  WHERE e.event_type = 'add_to_cart' GROUP BY e.user_id
), p AS (
  SELECT e.user_id, MIN(e.event_time) AS t
  FROM ecommerce_events e JOIN c ON c.user_id = e.user_id AND e.event_time > c.t
  WHERE e.event_type = 'purchase' GROUP BY e.user_id
)
SELECT (SELECT COUNT(*) FROM v) AS viewed,
       (SELECT COUNT(*) FROM c) AS then_carted,
       (SELECT COUNT(*) FROM p) AS then_purchased;

The usual trap

Counting users who did each step, in any order, overstates the funnel. In ecommerce_events 69 users have a purchase, but only 32 purchased after viewing and carting in that order. Counting purchase events instead gives 153.

Not this

A plain count per event type answers how many, not how many in order. If order does not matter, GROUP BY is enough. If it does, each step must start after the previous one.

Look for

• Group users by when they first appeared (a cohort)
• Retention after a week or a month
• New versus returning users

Smallest shape

WITH first_order AS (
  SELECT user_id, MIN(CAST(created_at AS DATE)) AS first_day FROM orders GROUP BY user_id
)
SELECT CAST(date_trunc('week', f.first_day) AS DATE) AS cohort_week,
       COUNT(DISTINCT f.user_id) AS cohort_size,
       COUNT(DISTINCT o.user_id) FILTER (WHERE CAST(o.created_at AS DATE) > f.first_day) AS ordered_again
FROM first_order f
LEFT JOIN orders o ON o.user_id = f.user_id
GROUP BY 1
ORDER BY 1;

The usual trap

After joining the cohort back to its activity, COUNT(*) counts activity rows, not people. For the first cohort week it gives 52 against a real cohort of 17 users. Count DISTINCT users, and divide by the cohort size, not by the users who came back.

Not this

A funnel counts how many people completed ordered steps. A cohort follows one group of people over time, starting from their first date.

Look for

• What was true on a given date (a slowly changing dimension, type 2)
• A history table with valid_from and valid_to
• Close the old row and open a new one

Smallest shape

SELECT customer_id, city
FROM customers_hist
WHERE valid_from <= DATE '2026-03-31'
  AND valid_to >= DATE '2026-03-31'
ORDER BY customer_id;

The usual trap

Check whether valid_to is the last day or the day after. In customers_hist it is the last day (2026-03-31, then the next row starts 2026-04-01). Written as valid_to > date, the same date returns 0 of the 12 customers, instead of all 12.

Not this

A lookup table with one fixed row per range (for example salary bands) looks similar. A history table holds several versions of the same key over time.

Look for

• Only the rows that changed since the last load
• Insert new keys, update existing ones (an upsert)
• A watermark: the latest timestamp already loaded

Smallest shape

SELECT reading_id, device_id, ingest_time
FROM telemetry
WHERE ingest_time > TIMESTAMP '2026-08-01 08:23:21'  -- the watermark
QUALIFY ROW_NUMBER() OVER (PARTITION BY reading_id ORDER BY ingest_time DESC) = 1
ORDER BY ingest_time;

The usual trap

The boundary row. With the watermark 2026-08-01 08:23:21, > gives 478 rows and >= gives 479, because 1 row lands exactly on the watermark. Choose one on purpose, and make the load safe to run twice, so a repeated row does no harm.

Not this

A full reload copies everything every time. Incremental copies only what changed, so it needs a reliable change column. This card shows selecting the changed rows; applying them by key is the MERGE work in this step.

Look for

• Everyone who reports up to a manager
• Parent and child of any depth
• Rows that must be generated step by step

Smallest shape

WITH RECURSIVE nodes(id, parent) AS (
  VALUES (1, NULL), (2, 1), (3, 1), (4, 2)
), tree AS (
  SELECT id, parent, 0 AS depth FROM nodes WHERE parent IS NULL
  UNION ALL
  SELECT n.id, n.parent, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent = t.id
)
SELECT * FROM tree ORDER BY depth, id;

The usual trap

If the data contains a cycle, the recursion has nothing to stop it. A 2-node loop (1 to 2, 2 to 1) keeps producing rows 1, 2, 1, 2, 1 until the depth limit of 5 cuts it off. Always carry a depth column and stop at a safe limit.

Not this

If the depth is fixed and small, two or three self joins are simpler. Use recursion when the depth is unknown.

Look for

• The query is slow or expensive on a big table
• A function around a filtered column
• Reads the whole table for 1 day

Smallest shape

SELECT COUNT(*) AS n
FROM orders
WHERE created_at >= TIMESTAMP '2026-08-05'
  AND created_at <  TIMESTAMP '2026-08-06';

The usual trap

Both forms return the same 8 rows for 2026-08-05, so correctness does not tell you which is cheaper. WHERE CAST(created_at AS DATE) = DATE '2026-08-05' wraps the column in a function. In a warehouse that partitions or clusters on that column, the range form is the one that can skip data.

Not this

A fast query on a small table is not the same as a cheap query on a large one. Read what the plan scans, not how long it took on your sample.