SQL Pattern Menu

The 12 patterns

Recognition cues — the senior tell

The fastest path to looking senior in SQL interviews is naming the pattern out loud before writing code. The interviewer relaxes; they know you'll get there.

Per-group calculations — when window vs GROUP BY

• GROUP BY: collapse to one row per group. You lose the original rows.
• Window function (OVER PARTITION BY): compute per group but keep all original rows.

Common pattern: compute in a window, filter, then aggregate. Example: "average revenue per customer's top 3 orders" — window-rank, filter to rank ≤ 3, then group + avg.

Time-based analysis

• Period bucketing: DATE_TRUNC('month', ts) for monthly aggregation.
• Filling gaps: LEFT JOIN to dim_date to ensure every day has a row.
• Period-over-period: LAG with the right ordering.
• Year-over-year: self-join on year = year - 1, or LAG with offset 12 if monthly.
• Window over time: cumulative + rolling + same-period-last-year are all just frame variations.

Deduplication

The pattern: ROW_NUMBER over the candidate key with a tiebreaker that picks the "right" duplicate.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY canonical_key
      ORDER BY
        prefer_this DESC,    -- primary preference (e.g. updated_at DESC)
        (col IS NOT NULL) DESC,  -- prefer non-null
        id ASC                -- final tiebreaker — deterministic
    ) AS rn
  FROM source
)
SELECT * FROM ranked WHERE rn = 1;

The final deterministic tiebreaker (a unique column) matters — without it, ties are non-deterministic and re-runs give different "winners."

Sequential / funnel analysis

The "what % of users went from step A to step B to step C" pattern.

Canonical approach: one row per user, MIN of timestamp for each event type, then comparisons:

WITH user_events AS (
  SELECT user_id,
    MIN(CASE WHEN event = 'A' THEN ts END) AS first_a,
    MIN(CASE WHEN event = 'B' THEN ts END) AS first_b,
    MIN(CASE WHEN event = 'C' THEN ts END) AS first_c
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) AS step_a,
  COUNT(*) FILTER (WHERE first_b >= first_a) AS step_b,  -- B after A
  COUNT(*) FILTER (WHERE first_b >= first_a
                     AND first_c >= first_b) AS step_c   -- C after B, B after A
FROM user_events
WHERE first_a IS NOT NULL;

Reshape — pivot & unpivot

CASE WHEN pivot works everywhere; PIVOT/UNPIVOT operators are shorter where supported.

For dynamic pivot (unknown column values at write time), either generate SQL in app code, use dbt_utils.pivot macro, or just return long format and pivot in the BI tool.

✓ Check yourself

Answer from memory before opening each one.

1. A question asks for users who logged in on at least three consecutive days. Which pattern is it, and why does the standard trick work?

   Show answer

   Gaps and islands. Within a run of consecutive dates, the date minus its row number stays constant, and it jumps at a gap, so grouping on that value isolates each streak. See recognition cues.

2. A dedup query keeps ROW_NUMBER() = 1 ordered by updated_at DESC. Two rows share the latest timestamp. What goes wrong across reruns, and what do you add?

   Show answer

   The winner among tied rows is arbitrary, so reruns can keep different rows and downstream numbers shift. Add a final unique tiebreaker column to the ORDER BY. See deduplication.

3. You need the average revenue of each customer's top three orders. Do you use GROUP BY or a window function?

   Show answer

   Both, in sequence. Rank orders per customer in a window, filter to rank three or lower, then GROUP BY customer and average. A window keeps the rows so you can filter on rank, and GROUP BY then collapses them. See per-group calculations.

4. In the funnel pattern, why take the MIN timestamp of each event type per user instead of counting events?

   Show answer

   Counting events counts repeated actions and gives several rows per user. The first timestamp per event gives one row per user and lets you compare order between stages. See sequential and funnel analysis.

5. A 7-day moving average uses ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, but the table has no rows for weekends. What is wrong, and how do you fix it?

   Show answer

   A row-based frame covers seven rows, not seven days, so it reaches further back than intended. Left join to a date dimension first so every day has a row. See time-based analysis.

6. A stakeholder wants a pivot where the columns are whatever product names exist this month. Which options do you have, and which would you suggest first?

   Show answer

   SQL pivots need known column values. Generate the SQL from a query of distinct values, use the dbt_utils pivot macro, or return long format and let the BI tool pivot. Suggest the BI tool first, because the column set changes. See reshaping.

Takeaway

• Most analytics-engineer SQL questions reduce to about a dozen patterns, and naming the pattern aloud before you write code is the strongest signal.
• Cue words map to tools: latest or top-N to ROW_NUMBER, running or rolling to a framed window aggregate, versus previous to LAG, streaks to gaps and islands.
• GROUP BY collapses rows and a window keeps them; combine them by ranking in a window, filtering, then aggregating.
• Deduplication needs a ranked choice with a final unique tiebreaker so reruns are stable.
• Row-based window frames assume one row per period, so fill gaps from a date dimension first.
• A funnel takes the first timestamp per event per user, then compares their order.
• A pivot needs known column values; for unknown ones, generate the SQL or pivot in the BI tool.

Next come eleven worked problems that drill these patterns end to end. → SQL Problems Worked Out

Further reading

• Window Functions — PostgreSQL documentation, accessed 2026. The tutorial behind running totals, ranking and per-group calculations.
• Window Functions (reference) — PostgreSQL documentation, accessed 2026. Exact semantics of ROW_NUMBER, RANK, LAG, LEAD and NTILE.
• Window Functions — DuckDB documentation, accessed 2026. A runnable engine to practise on, with frame syntax.
• QUALIFY — Snowflake documentation, accessed 2026. The shorter form of latest-per-group.