SQL Interview Patterns

After you’ve solved enough SQL interview questions, you start noticing that the prompt wording changes, but the query shape stays the same. This class is a quick “pattern reminder” you can scan before interviews to refresh the handful of solution templates that cover most questions.

Below are 10 common patterns, each with a short “when you see it” description, a reliable boilerplate, and what to do next.

Give me all X, but only if Y

You’ll see: “Show users who…”, “Orders in the last 30 days…”, “Events matching…”

What to do when you see it: clarify the scope first (time window, status, definition of “active”), then filter early.

SQL
SELECT
    ...
FROM x
WHERE
    <row_filters>
    AND <time_window_filter>;

Existence checks

You’ll see: “users who have at least one…”, “customers who never…”, “entities without…”

What to do when you see it: default to EXISTS / NOT EXISTS when you only care that a match exists (it’s naturally duplicate-safe).

SQL
-- Has (at least one matching row)
SELECT
    a.*
FROM a
WHERE EXISTS (
    SELECT 1
    FROM b
    WHERE b.a_id = a.id
      AND <b_filters>
);

-- Has-not (no matching rows)
SELECT
    a.*
FROM a
WHERE NOT EXISTS (
    SELECT 1
    FROM b
    WHERE b.a_id = a.id
      AND <b_filters>
);

Filter on an aggregate

You’ll see: “at least 3 weeks…”, “>= 2 purchases…”, “avg spend above…”

What to do when you see it: decide whether the filter is on rows (WHERE) or groups (HAVING). If it’s a complex group metric, compute it first, then filter.

SQL
SELECT
    group_key,
    COUNT(*) AS cnt
FROM t
GROUP BY group_key
HAVING COUNT(*) >= 3;

Two-step (cleaner for complex filters)

SQL
WITH per_group AS (
    SELECT
        group_key,
        <agg_expr> AS metric
    FROM t
    GROUP BY group_key
)
SELECT *
FROM per_group
WHERE metric >= <threshold>;

Pre-aggregating

You’ll see: “total paid per user”, “revenue by country”, “counts after joins”

What to do when you see it: identify the “many” side and pre-aggregate it down to the join key before joining.

SQL
WITH b_agg AS (
    SELECT
        key,
        SUM(metric) AS metric
    FROM b
    WHERE <filters>
    GROUP BY key
)
SELECT
    a.key,
    COALESCE(SUM(b_agg.metric), 0) AS metric
FROM a
LEFT JOIN b_agg
    ON b_agg.key = a.key
GROUP BY a.key;

Conditional aggregation

You’ll see: “count X and Y in one table”, “funnel by step”, “segment by device”

What to do when you see it: keep the grain stable, and use SUM(CASE WHEN ...) (or COUNT with NULLs) to compute multiple metrics in one pass.

SQL
SELECT
    group_key,
    SUM(CASE WHEN condition_1 THEN 1 ELSE 0 END) AS metric_1,
    SUM(CASE WHEN condition_2 THEN 1 ELSE 0 END) AS metric_2
FROM t
GROUP BY group_key;

Rates, shares, and NULL-safe division

You’ll see: “conversion rate”, “CTR”, “share of total”, “percent of users who…”

What to do when you see it: define numerator/denominator explicitly, force float math, and guard divide-by-zero with NULLIF.

SQL
SELECT group_key,
       numerator,
       denominator,
       100.0* numerator/NULLIF(denominator,0) AS pct
FROM (...);

Ranking

You’ll see: “top 3”, “2nd highest”, “latest order”, “nth transaction”

What to do when you see it: use ROW_NUMBER() for exactly N rows per group; use RANK()/DENSE_RANK() if ties should be kept. Always add a deterministic tie-breaker.

SQL
SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY group_key
            ORDER BY sort_key DESC, tie_breaker DESC
        ) AS rn
    FROM t
) AS x
WHERE rn <= N;

Rolling metrics

You’ll see: “7-day rolling average”, “trailing 28 days”, “cumulative revenue”

What to do when you see it: first compute the metric at the correct time grain (day/week/month), then apply a window frame.

SQL
SELECT
    dt,
    metric,
    AVG(metric) OVER (
        ORDER BY dt
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7
FROM daily_metrics;

Previous vs current

You’ll see: “week-over-week change”, “difference from previous”, “growth rate”

What to do when you see it: decide if “previous” means previous row or previous period (calendar-aligned), then use LAG().

SQL
SELECT
    key,
    dt,
    metric,
    metric - LAG(metric) OVER (
        PARTITION BY key
        ORDER BY dt
    ) AS delta
FROM t;

Self-joins

You’ll see: “pairs of items”, “before/after events”, “employee-manager”, “referrer relationships”

What to do when you see it: alias the same table twice, and write a rule to avoid duplicates or self-pairs (a.id < b.id is a common trick).

SQL
SELECT
    a.id AS left_id,
    b.id AS right_id
FROM t AS a
JOIN t AS b
    ON <pair_condition>
WHERE a.id < b.id;

Syntax cheat sheet

The following section is intentionally syntax-only: short, copyable snippets you can reuse while practicing. It’s not meant to teach concepts, just to reduce “blank page” time when you recognize a pattern.

Use it as a quick reference for common SQL building blocks (filters, windows, bucketing, NULL-safety, ranking, existence checks).