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.