SQL Deep Dive

Grain — declare it before you write a line

Before any query, answer two questions out loud:

1. What is the grain of my result? One row per what? Per customer? Per customer-per-day? Per usage event?
2. What is the grain of each input table? Mismatched grain is the #1 cause of wrong answers — it's why a join silently fans out and inflates a SUM.

Saying "this result is one row per customer per day, and usage_events is one row per metered interval, so I'll aggregate before joining" is the single clearest seniority signal in a SQL screen. It also prevents the bug before it happens.

Logical query order — know this cold

SQL is written one way and executed another. The logical order:

1. FROM        (and JOINs)
2. WHERE       (filters rows BEFORE grouping)
3. GROUP BY
4. HAVING      (filters groups AFTER grouping)
5. SELECT      (expressions/aliases computed here)
6. DISTINCT
7. ORDER BY    (SELECT aliases ARE available here)
8. LIMIT / OFFSET

This explains the errors interviewers love to catch:

• "Why can't I use a SELECT alias in WHERE?" — WHERE runs before SELECT.
• "Why is COUNT(*) higher than COUNT(col)?" — COUNT(*) counts rows; COUNT(col) skips NULLs.
• "Why does HAVING work where WHERE didn't?" — HAVING filters after grouping; WHERE filters before.

Joins & fan-out — where people quietly go wrong

The LEFT JOIN → WHERE trap. Filtering the right table in WHERE silently turns a LEFT JOIN back into an INNER JOIN, because WHERE right.col = 'x' drops the NULL rows. Put the condition in ON:

-- WRONG: this is now effectively an inner join
SELECT c.customer_id, i.invoice_id
FROM customers c
LEFT JOIN invoices i ON i.customer_id = c.customer_id
WHERE i.status = 'paid';        -- kills customers with no paid invoice

-- RIGHT: keep all customers, only join paid invoices
SELECT c.customer_id, i.invoice_id
FROM customers c
LEFT JOIN invoices i
  ON i.customer_id = c.customer_id
 AND i.status = 'paid';

Fan-out. If the right side has multiple rows per key, left rows multiply. SUM a left-side column afterward and it's inflated. Defense: aggregate the right side to the join grain first.

-- Customer's plan fee should NOT be multiplied by their many usage rows.
WITH usage AS (
  SELECT customer_id, SUM(gpu_seconds) AS gpu_seconds
  FROM usage_events
  GROUP BY customer_id            -- collapse to one row per customer FIRST
)
SELECT c.customer_id, c.monthly_fee, u.gpu_seconds
FROM customers c
LEFT JOIN usage u ON u.customer_id = c.customer_id;

Anti-join (rows in A with no match in B) — three idioms, and a trap:

-- Preferred: explicit, index-friendly
SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

-- Also clear, NULL-safe
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

-- TRAP: if b.id has even one NULL, NOT IN returns ZERO rows
SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);

The NOT IN + NULL trap is a favorite: x <> NULL is unknown, so a single NULL in the subquery makes the whole predicate fail for every row. Use NOT EXISTS or the LEFT JOIN idiom.

NULL traps

• NULL = NULL is unknown, not true. Compare with IS NULL / IS DISTINCT FROM.
• Aggregates ignore NULLs: AVG(col) divides by the count of non-NULLs, which may not be what you want.
• COUNT(col) skips NULLs; COUNT(*) doesn't.
• Divide-by-zero: guard with NULLIF(denominator, 0) so the result is NULL instead of an error.
• COALESCE(col, 0) to default; but decide whether a missing value should really be 0 or stay NULL (a customer with no usage vs. zero usage may be different).

GROUP BY & HAVING

Every non-aggregated SELECT column must be in GROUP BY (outside MySQL's loose mode). Use HAVING to filter on aggregates, WHERE to filter rows before aggregating — pushing filters into WHERE is both correct and faster.

SELECT customer_id, SUM(amount) AS revenue
FROM invoices
WHERE status = 'paid'            -- filter rows first (cheap, correct)
GROUP BY customer_id
HAVING SUM(amount) > 1000;       -- filter groups after aggregation

CTEs & structure

Build queries as a pipeline of named CTEs, each one transformation. It reads top-to-bottom, is debuggable (run one CTE at a time), and shows the interviewer your thinking. Prefer this to deeply nested subqueries.

WITH daily AS (        -- 1. roll usage up to customer-day
  SELECT customer_id, DATE(started_at) AS day, SUM(gpu_seconds) AS secs
  FROM usage_events
  GROUP BY 1, 2
),
billed AS (            -- 2. price it
  SELECT customer_id, day, secs, secs / 3600.0 * 1.20 AS cost_usd
  FROM daily
)
SELECT * FROM billed WHERE day >= CURRENT_DATE - 7;

Note: recursive CTEs (WITH RECURSIVE) handle hierarchies (org charts, referral chains) and generating date spines — worth being able to recognize, less commonly required to write live.

Window functions — the highest-leverage skill

If you master one thing, make it windows. They separate junior from mid/senior SQL. Anatomy:

FUNCTION() OVER (
  PARTITION BY       -- like GROUP BY but keeps every row
  ORDER BY               -- required for ranking / running calcs
  ROWS BETWEEN              -- which rows feed the function
)

Ranking — know the three: ROW_NUMBER (always unique: 1,2,3,4), RANK (gaps after ties: 1,2,2,4), DENSE_RANK (no gaps: 1,2,2,3). For "latest row per group" you want ROW_NUMBER; for "Nth distinct value" you want DENSE_RANK.

Running totals & moving averages:

SELECT
  day,
  revenue,
  SUM(revenue) OVER (ORDER BY day
       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(revenue) OVER (ORDER BY day
       ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS rolling_7d_avg
FROM daily_revenue;

Period-over-period with LAG/LEAD:

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct
FROM monthly_revenue;

Note the NULLIF(..., 0) guard — small detail, signals care.

QUALIFY & the dedup pattern

The most-asked window pattern: keep the latest record per key. Two ways — and QUALIFY (Snowflake/BigQuery/Databricks) lets you filter on a window function without a wrapping CTE:

-- Portable: CTE + ROW_NUMBER
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id
                       ORDER BY updated_at DESC, id DESC) AS rn  -- tiebreaker!
  FROM customers
)
SELECT * FROM ranked WHERE rn = 1;

-- Snowflake / BigQuery / Databricks: QUALIFY
SELECT *
FROM customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id
                           ORDER BY updated_at DESC, id DESC) = 1;

Always include a deterministic tiebreaker in the ORDER BY (e.g. id DESC) — otherwise "latest" is nondeterministic when timestamps tie, and your result changes between runs.

Gaps & islands, sessionization

Gaps and islands — find consecutive streaks (e.g. consecutive days a customer had active instances). The trick: subtract a row number from the date; consecutive rows share a constant.

WITH g AS (
  SELECT customer_id, active_date,
    active_date
      - (ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY active_date))::int AS grp
  FROM daily_active
)
SELECT customer_id, MIN(active_date) AS streak_start,
       MAX(active_date) AS streak_end, COUNT(*) AS streak_len
FROM g
GROUP BY customer_id, grp;

Be ready to explain why it works: the offset stays constant only when dates increment by exactly one, so each unbroken run collapses to a single grp.

Sessionization — group events into sessions with a 30-minute inactivity gap. Pattern: flag boundaries, then a running SUM of the flag generates IDs.

WITH lagged AS (
  SELECT customer_id, event_ts,
    LAG(event_ts) OVER (PARTITION BY customer_id ORDER BY event_ts) AS prev_ts
  FROM events
),
flagged AS (
  SELECT *,
    CASE WHEN prev_ts IS NULL
           OR event_ts - prev_ts > INTERVAL '30 minutes'
         THEN 1 ELSE 0 END AS is_new
  FROM lagged
)
SELECT *,
  SUM(is_new) OVER (PARTITION BY customer_id ORDER BY event_ts) AS session_id
FROM flagged;

The "flag a boundary, running-sum the flag" pattern generalizes to many problems — recognize it.

Conditional aggregation (pivot)

-- FILTER is the clean Postgres/standard way
SELECT customer_id,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'failed')    AS failed
FROM jobs GROUP BY customer_id;

-- SUM(CASE ...) is the portable equivalent every engine supports
SELECT customer_id,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'failed'    THEN 1 ELSE 0 END) AS failed
FROM jobs GROUP BY customer_id;

Performance — what to say

You don't need to be a query-planner expert, but have informed opinions:

• EXPLAIN / EXPLAIN ANALYZE is how you diagnose. Look for sequential scans on big tables and bad join order.
• Indexes help selective lookups and joins but cost write throughput; a query reading most of a table will ignore the index and scan anyway.
• Don't wrap indexed columns in functions in WHERE. WHERE DATE(ts) = '2026-01-01' can't use an index on ts; rewrite as a range: ts >= '2026-01-01' AND ts < '2026-01-02'.
• Columnar warehouses (BigQuery, Snowflake, Redshift) change the game. Partitioning and clustering / sort keys matter more than traditional indexes, and you pay for bytes scanned — so SELECT * and unpartitioned full scans are expensive. Select only needed columns and filter on the partition column.
• Pre-aggregate when many queries hit the same rollup; materialize it rather than recomputing.

As the first hire, add the cost lens: "On a usage-based warehouse I'd partition usage_events by day and cluster by customer, because nearly every query filters on a date range and a customer — that keeps scan cost and bill predictable."

Anti-patterns to call out

• SELECT * in production models — breaks on schema change and scans extra bytes.
• Correlated subqueries in SELECT that re-run per row — usually replaceable with a join or window.
• DISTINCT used to paper over a fan-out bug instead of fixing the join grain.
• Implicit cross joins from comma-joins with a forgotten condition.
• ORDER BY inside subqueries/CTEs (ignored, wasteful) — order only at the end.

Approaching live SQL — the performance

1. Restate & clarify. "So you want one row per customer per day, and a customer with no usage that day should appear with zero — yes?" Confirm grain and edge cases.
2. Name the shape. "This is a latest-per-group / a gaps-and-islands / a sessionization problem." Pattern-naming reassures the interviewer.
3. Build with CTEs, narrating each step. Don't go silent.
4. Sanity-check. "Let me confirm row counts didn't explode after that join." Mention how you'd validate against a known total.
5. Mention NULL / tie / empty-input edge cases even if you don't fully handle them — it shows you see them.

✓ Check yourself

Answer from memory before opening each one.

1. A report joins customers to usage_events, which holds many rows per customer, then sums customers.monthly_fee. The total is far too high. A teammate proposes SUM(DISTINCT monthly_fee). What is wrong with that fix, and what do you do instead?

   Show answer

   SUM(DISTINCT) collapses equal fees, so two customers on the same plan count once and the total is now too low. It also leaves every other joined column inflated. Aggregate usage to one row per customer in a CTE first, then join. See joins and fan-out.

2. Table a has ids 1 to 5 and you know id 5 has no match in b. The query SELECT * FROM a WHERE id NOT IN (SELECT id FROM b) returns no rows. What is the likely cause, and how do you rewrite it?

   Show answer

   b.id holds at least one NULL. Comparing a value with NULL gives unknown, so the predicate is never true and every row is dropped. Use NOT EXISTS, or a LEFT JOIN with b.id IS NULL, which are both NULL-safe. See the anti-join idioms.

3. A table has three rows for each day. SUM(x) OVER (ORDER BY day) shows the same running value on all three rows of a day. Why, and what do you change if you want row-by-row accumulation?

   Show answer

   With ORDER BY and no frame, the default frame is RANGE, which treats rows with equal ORDER BY values as peers and includes all of them at once. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and add a tiebreaker column to the ORDER BY so the order is defined. See window functions.

4. Your latest-row-per-customer query returns a different row for some customers on each run. Nothing in the data changes between runs. What is happening, and what is the fix?

   Show answer

   Two rows for the same customer share the same updated_at, so ROW_NUMBER picks between them arbitrarily. Add a deterministic tiebreaker such as id DESC to the ORDER BY. See QUALIFY and the dedup pattern.

5. A customer was active on day-of-month 3, 4, 5, 8 and 9. Number the rows 1 to 5 in date order. What does date minus row number give for each row, and what streaks come out?

   Show answer

   The differences are 2, 2, 2, 4, 4. Rows with the same difference form one streak: days 3 to 5 (length 3) and days 8 to 9 (length 2). The offset stays constant only while the dates rise by exactly one. See gaps and islands.

6. A query filters WHERE DATE(ts) = '2026-01-01' on a very large table with an index on ts. It is slow, and on the warehouse it also bills far more than expected. What do you change, and how do you confirm the change worked?

   Show answer

   Rewrite the filter as a range, ts >= '2026-01-01' AND ts < '2026-01-02', so the engine can use the index or prune partitions instead of wrapping the column in a function. Confirm with EXPLAIN: an index scan replaces the sequential scan, or the warehouse reports fewer bytes scanned. See performance.

Takeaway

• Declare the grain of the result and of each input before you write; mismatched grain is the usual cause of a wrong total.
• Aggregate the many side to the join grain before you join. DISTINCT hides fan-out and can delete real rows.
• A right-table filter in WHERE turns a LEFT JOIN into an inner join, and one NULL inside a NOT IN subquery empties the result.
• A window with ORDER BY defaults to a RANGE frame that lumps ties together; say ROWS when you mean physical rows.
• Latest-per-group needs a deterministic tiebreaker, and "flag a boundary, then running-sum it" covers sessions and streaks.
• Do not wrap an indexed or partitioned column in a function in WHERE; on a warehouse, bytes scanned is the bill.

The window frame, lateral join and as-of join ideas here have sharper edges that a senior screen probes. → SQL Advanced

Further reading

• Tutorial: Window Functions — PostgreSQL docs, 2026. A short worked introduction to PARTITION BY, ORDER BY and frames.
• Window Functions — PostgreSQL docs, 2026. The reference for ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and the value functions.
• Table Expressions — PostgreSQL docs, 2026. Join types, the FROM clause and the rules behind outer joins.
• Subquery Expressions — PostgreSQL docs, 2026. EXISTS, IN and NOT IN, including how NULL changes the result.
• Comparison Functions and Operators — PostgreSQL docs, 2026. NULL comparison, IS DISTINCT FROM and three-valued logic.
• QUALIFY — Snowflake docs, 2026. Filtering on window function results without a wrapping CTE.