Section B · SQL

SQL Creation Patterns

The composable patterns you reach for to answer business questions in SQL. Window functions, cohorts, funnels, sessionization, gaps-and-islands, pivots, hierarchical queries — and how to chain them cleanly.

The interview approach

When the interviewer gives you a schema and a question, the senior move is to narrate before you type:

Restate the question in one sentence.
Ask one or two clarifying questions: definition of "active," time-zone handling, dedupe rules, what "first" means when there are ties.
Name the SQL pattern you're going to use: "I'll write a CTE per step, with a window function for ranking."
Write it. Talk through each clause.
Trace 2–3 example rows through the output by hand. Catches off-by-ones immediately.

The patterns below are the building blocks. Most prompts are a composition of two or three of them.

Window functions

Four families. Memorize them.

Ranking

row_number vs rank vs dense_rank

SELECT
  customer_id, gpu_id, started_at,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY started_at) AS rn,
  RANK()       OVER (PARTITION BY customer_id ORDER BY started_at) AS rk,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY started_at) AS drk
FROM gpu_sessions;

-- ROW_NUMBER: unique 1..N per partition, ties broken arbitrarily
-- RANK:       ties share rank, next skips (1,2,2,4)
-- DENSE_RANK: ties share rank, no skip (1,2,2,3)
--
-- "Most recent session per customer" → ROW_NUMBER + WHERE rn=1
-- "Top 3 distinct revenue tiers per region" → DENSE_RANK + WHERE drk <= 3

Offset (lag / lead)

Compare a row to the previous or next in its partition.

time between sessions per customer

SELECT
  customer_id,
  started_at,
  LAG(started_at) OVER (PARTITION BY customer_id ORDER BY started_at) AS prev_start,
  EXTRACT(EPOCH FROM started_at -
    LAG(started_at) OVER (PARTITION BY customer_id ORDER BY started_at)) / 3600 AS hours_since_prev
FROM gpu_sessions;

Aggregate windows

Running totals, moving averages, share-of-total.

7-day rolling GPU-hours

WITH daily AS (
  SELECT
    DATE_TRUNC('day', started_at) AS day,
    SUM(EXTRACT(EPOCH FROM ended_at - started_at) / 3600) AS gpu_hours
  FROM gpu_sessions
  GROUP BY 1
)
SELECT
  day,
  gpu_hours,
  AVG(gpu_hours) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS gpu_hours_7d_avg
FROM daily
ORDER BY day;

Frame trap

Use ROWS BETWEEN for time-based moving averages, not RANGE. RANGE with date gaps does the wrong thing. Classic interviewer gotcha.

First / last value

Carry a value from a partition's first or last row into every row.

first plan per customer, carried forward

SELECT
  customer_id, changed_at, plan,
  FIRST_VALUE(plan) OVER (
    PARTITION BY customer_id
    ORDER BY changed_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_plan
FROM plan_changes;

LAST_VALUE trap

The default frame ends at CURRENT ROW, so LAST_VALUE without an explicit frame returns the current row, not the partition's last. Always write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Cohort retention

"Of customers who signed up in week N, how many came back in week N+k?" — the analytical staple.

weekly retention triangle

WITH cohorts AS (
  SELECT customer_id, DATE_TRUNC('week', signed_up_at) AS cohort_week
  FROM customers
),
activity AS (
  SELECT DISTINCT
    customer_id,
    DATE_TRUNC('week', event_at) AS activity_week
  FROM gpu_sessions
)
SELECT
  c.cohort_week,
  DATE_PART('week', a.activity_week - c.cohort_week)::INT AS weeks_since_signup,
  COUNT(DISTINCT c.customer_id) AS retained,
  COUNT(DISTINCT c.customer_id) * 1.0 /
    NULLIF(
      MAX(CASE WHEN DATE_PART('week', a.activity_week - c.cohort_week) = 0
               THEN COUNT(DISTINCT c.customer_id) END)
      OVER (PARTITION BY c.cohort_week), 0
    ) AS retention_rate
FROM cohorts c
LEFT JOIN activity a
  ON c.customer_id = a.customer_id
 AND a.activity_week >= c.cohort_week
GROUP BY 1, 2
ORDER BY 1, 2;

For an interview, the simpler version (compute cohort size separately, join in the denominator) often reads cleaner. The above shows the window-function flex if asked.

Funnels with time constraints

"Of customers who signed up, how many launched a GPU within 7 days? Of those, how many launched a second within 30 days?" The wrong answer is multiple self-joins. The right answer is one CTE per step:

3-step funnel with time windows

WITH signup AS (
  SELECT customer_id, MIN(signed_up_at) AS signup_at
  FROM customers
  GROUP BY customer_id
),
first_launch AS (
  SELECT s.customer_id, s.signup_at, MIN(g.started_at) AS first_launch_at
  FROM signup s
  JOIN gpu_sessions g
    ON g.customer_id = s.customer_id
   AND g.started_at BETWEEN s.signup_at AND s.signup_at + INTERVAL '7 days'
  GROUP BY 1, 2
),
second_launch AS (
  SELECT f.customer_id, f.signup_at, f.first_launch_at, MIN(g.started_at) AS second_launch_at
  FROM first_launch f
  JOIN gpu_sessions g
    ON g.customer_id = f.customer_id
   AND g.started_at > f.first_launch_at
   AND g.started_at <= f.first_launch_at + INTERVAL '30 days'
  GROUP BY 1, 2, 3
)
SELECT
  (SELECT COUNT(*) FROM signup)          AS signups,
  (SELECT COUNT(*) FROM first_launch)    AS launched_within_7d,
  (SELECT COUNT(*) FROM second_launch)   AS second_launched_within_30d;

The trick is the time bounds inside the JOIN, not as a WHERE filter — that prevents the LEFT-JOIN-NULL trap if you ever switch from INNER to LEFT.

Sessionization

Group consecutive events into sessions where a gap of > N minutes starts a new session.

30-minute gap sessionization

WITH flagged AS (
  SELECT
    customer_id,
    event_at,
    CASE
      WHEN LAG(event_at) OVER (PARTITION BY customer_id ORDER BY event_at) IS NULL
        OR EXTRACT(EPOCH FROM event_at -
             LAG(event_at) OVER (PARTITION BY customer_id ORDER BY event_at)) / 60 > 30
      THEN 1 ELSE 0
    END AS is_new_session
  FROM events
),
sessioned AS (
  SELECT
    customer_id,
    event_at,
    SUM(is_new_session) OVER (PARTITION BY customer_id ORDER BY event_at) AS session_id
  FROM flagged
)
SELECT
  customer_id, session_id,
  MIN(event_at) AS session_start,
  MAX(event_at) AS session_end,
  COUNT(*)      AS event_count
FROM sessioned
GROUP BY 1, 2;

Gaps & islands

The "tabibitosan" trick — subtract a row's rank from its date; consecutive runs share the same key.

longest consecutive-day GPU-active streak

WITH daily AS (
  SELECT DISTINCT customer_id, DATE_TRUNC('day', event_at)::DATE AS d
  FROM gpu_sessions
),
keyed AS (
  SELECT
    customer_id, d,
    d - (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY d))::INT AS run_key
  FROM daily
)
SELECT
  customer_id,
  MIN(d) AS streak_start,
  MAX(d) AS streak_end,
  COUNT(*) AS streak_days
FROM keyed
GROUP BY customer_id, run_key
ORDER BY customer_id, streak_start;

Read carefully: date − row_number is constant within a run of consecutive dates because both increase by 1 per row.

Pivots

Long-to-wide. Most dialects don't have a clean PIVOT; use SUM(CASE WHEN…).

per-customer GPU-hours by tier

SELECT
  customer_id,
  SUM(CASE WHEN tier = 'a100' THEN gpu_hours END) AS a100_hours,
  SUM(CASE WHEN tier = 'h100' THEN gpu_hours END) AS h100_hours,
  SUM(CASE WHEN tier = 'rtx-4090' THEN gpu_hours END) AS rtx4090_hours,
  SUM(gpu_hours) AS total_hours
FROM session_summary
GROUP BY customer_id;

Hierarchical queries

Recursive CTEs for org-tree / cost-rollup / referral-chain questions. Less common in screens but worth knowing the shape:

referral chain depth

WITH RECURSIVE chain AS (
  SELECT customer_id, referred_by, 1 AS depth
  FROM customers
  WHERE referred_by IS NULL
  UNION ALL
  SELECT c.customer_id, c.referred_by, p.depth + 1
  FROM customers c
  JOIN chain p ON p.customer_id = c.referred_by
)
SELECT depth, COUNT(*) AS customers
FROM chain
GROUP BY depth
ORDER BY depth;

CTE composition discipline

For anything beyond two steps, write one CTE per logical operation and name them like prose. The reviewer reads top-to-bottom:

active_customers — filter customers who've been active in the last 30 days
gpu_hours_per_customer — aggregate
tier_ranked — rank within tier
final_output — select fields to return

The interviewer reads the CTE names as an outline of your thinking. Bad names ("t1", "subquery", "stuff") read junior even when the SQL is correct.

The composition signal

If your query is over ~30 lines and lives in a single deeply-nested subquery, refactor to CTEs even mid-interview. Say "let me restructure this for clarity" — interviewers love that move.