SQL fluency

Why it matters

SQL is the interface to most production data, and it fails in a specific way: a wrong query usually runs and returns a plausible number. Exercises target exactly the places where intuition breaks, such as a LEFT JOIN that silently becomes an inner join, a NOT IN that returns nothing, a ranking that mishandles ties, or a retention rate with the wrong denominator. Fluency means getting these right on the first attempt and being able to explain why, which is also what you need when an incident forces you to query production data under pressure.

Core ideas

Logical order. A query behaves as if FROM and joins run first, then WHERE, GROUP BY, HAVING, window functions, the SELECT list, DISTINCT, ORDER BY and LIMIT. This explains why WHERE cannot use a SELECT alias or a window result, and why HAVING can filter on aggregates.

Joins. An inner join keeps matching pairs. A LEFT JOIN keeps every left row and fills NULLs where nothing matches; a WHERE filter that tests a right-table column for a value then discards those NULL-extended rows and turns it back into an inner join, so such conditions belong in ON. Joins multiply rows: joining orders to both items and payments double-counts unless you aggregate first. Semi-joins (EXISTS) ask whether a match exists without multiplying; anti-joins (NOT EXISTS, or a LEFT JOIN keeping rows where the right key IS NULL) find rows without one. A CROSS JOIN builds every combination, which is how you produce a row for each user and day before counting.

Aggregation. COUNT(*) counts rows, COUNT(col) counts non-NULL values, and COUNT(DISTINCT col) counts distinct non-NULL values. Aggregates ignore NULLs, and SUM over no rows is NULL rather than 0, so wrap it in COALESCE when zero is the right answer. Conditional aggregation (SUM(CASE WHEN ... THEN 1 ELSE 0 END), or COUNT(*) FILTER (WHERE ...) in PostgreSQL) computes several metrics in one pass.

NULL and three-valued logic. Comparisons with NULL yield UNKNOWN, and WHERE keeps only rows where the condition is TRUE. So status <> 'cancelled' silently drops rows whose status is NULL; write status IS DISTINCT FROM 'cancelled' or add OR status IS NULL. A WHERE x NOT IN (subquery) filter passes no rows at all once the subquery returns a NULL, which is why NOT EXISTS is the safer anti-join.

CTEs and subqueries. CTEs name intermediate results and make multi-step logic readable. PostgreSQL normally folds a side-effect-free, non-recursive CTE that is referenced once into the main query, and MATERIALIZED or NOT MATERIALIZED overrides that choice. Correlated subqueries read clearly but may run once per row; check the plan.

Window functions. A window function computes over related rows without collapsing them. ROW_NUMBER breaks ties arbitrarily, RANK leaves gaps after ties and DENSE_RANK does not; top-N per group is a ranking in a CTE filtered in the outer query. LAG and LEAD compare neighboring rows. With ORDER BY, the default frame runs from the partition start to the current row's last peer, so rows tied on the ordering key share a running total; use a ROWS frame for strict row-by-row totals.

Ordering. Without ORDER BY, row order is unspecified, and ORDER BY on a non-unique key leaves ties in arbitrary order, so add a unique tie-breaker before LIMIT. PostgreSQL sorts NULLs as larger than any value by default (last ascending, first descending); NULLS FIRST and NULLS LAST override it.

Retention. Define the cohort (for example each user's first active day), what counts as retained (any activity in days 1 to 7 after it), and the denominator (every user in the cohort, including those who never returned). Build it in steps:

WITH firsts AS (
  SELECT user_id, MIN(activity_date) AS d0 FROM events GROUP BY user_id
), flags AS (
  SELECT f.user_id, f.d0,
         EXISTS (SELECT 1 FROM events e
                 WHERE e.user_id = f.user_id
                   AND e.activity_date BETWEEN f.d0 + 1 AND f.d0 + 7) AS retained
  FROM firsts f
)
SELECT d0 AS cohort_day, COUNT(*) AS cohort_size,
       AVG(CASE WHEN retained THEN 1.0 ELSE 0.0 END) AS week1_retention
FROM flags GROUP BY d0 ORDER BY d0;

The EXISTS keeps duplicate events from inflating counts, and the per-user CTE keeps the denominator honest.

Common misconceptions

• "A LEFT JOIN always keeps every left row." Only until a WHERE filter on a right-table column removes the NULL-extended rows; put such conditions in ON.
• "NOT IN and NOT EXISTS are interchangeable." They differ as soon as the subquery can return NULL: NOT IN then returns no rows.
• "COUNT(col) counts rows." It skips NULLs; use COUNT(*) for rows and be explicit about what you count.
• "ROW_NUMBER, RANK and DENSE_RANK differ only cosmetically." They return different rows for top-N with ties; read the requirement for ties first.
• "Tables have a natural order." Without ORDER BY there is no guaranteed order, and ordering inside a subquery does not order the outer result.
• "A running SUM over ORDER BY is row by row." The default frame includes peers, so tied rows share a total; use ROWS for row-wise totals.

How to approach it

Refresh with the PostgreSQL tutorial sections on joins, aggregates and window functions, then read the reference sections on joined tables, subquery expressions, comparison operators and window function calls. Practice against a real database so you can inspect intermediate results, and for every exercise write down the grain of each intermediate table (one row per user, per user and day, per order item). In interviews, state the grain and the handling of NULLs and ties before writing, build the query in CTE steps, and check it against a tiny hand-made example with an unmatched row, a NULL and a tie. All levels are expected to write correct queries; senior candidates are also asked about performance and indexes.