SQL Creation Patterns

The interview approach

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

1. Restate the question in one sentence.
2. Ask one or two clarifying questions: definition of "active," time-zone handling, dedupe rules, what "first" means when there are ties.
3. Name the SQL pattern you're going to use: "I'll write a CTE per step, with a window function for ranking."
4. Write it. Talk through each clause.
5. 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

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.

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.

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;

First / last value

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

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;

Cohort retention

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

WITH cohorts AS (
  SELECT customer_id, DATE_TRUNC('week', signed_up_at)::DATE AS cohort_week
  FROM customers
),
cohort_size AS (
  SELECT cohort_week, COUNT(*) AS size
  FROM cohorts
  GROUP BY 1
),
activity AS (
  SELECT DISTINCT
    customer_id,
    DATE_TRUNC('week', started_at)::DATE AS activity_week
  FROM gpu_sessions
)
SELECT
  c.cohort_week,
  (a.activity_week - c.cohort_week) / 7 AS weeks_since_signup,
  COUNT(DISTINCT c.customer_id)         AS retained,
  COUNT(DISTINCT c.customer_id) * 1.0 / s.size AS retention_rate
FROM cohorts c
JOIN cohort_size s ON s.cohort_week = c.cohort_week
JOIN activity a
  ON a.customer_id = c.customer_id
 AND a.activity_week >= c.cohort_week
GROUP BY c.cohort_week, s.size, 2
ORDER BY 1, 2;

Cohort size comes from its own CTE, so the denominator never depends on which weeks have activity. Subtracting two DATE values gives a whole number of days in PostgreSQL, so dividing by 7 gives weeks since signup. Other dialects use DATEDIFF.

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:

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.

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.

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…).

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:

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.

✓ Check yourself

Answer from memory before opening each one.

1. A customer's sessions have revenue tiers 100, 90, 90 and 80. Give the ranks from ROW_NUMBER, RANK and DENSE_RANK, and say which you would use for "top 3 distinct revenue tiers per region".

   Show answer

   ROW_NUMBER gives 1, 2, 3, 4 with the tie broken arbitrarily. RANK gives 1, 2, 2, 4 and DENSE_RANK gives 1, 2, 2, 3. For distinct tiers use DENSE_RANK, because tied rows share a rank and no tier is skipped. See window functions.

2. A query uses LAST_VALUE(plan) OVER (PARTITION BY customer_id ORDER BY changed_at) and every row returns its own plan. Why, and what is the fix?

   Show answer

   With an ORDER BY the default frame ends at the current row, so the last value in the frame is the current row. State the frame as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or take the row with ROW_NUMBER() over a descending order. See window functions.

3. A daily table has rows for every day except the 3rd, 4th and 5th of the month. For the row on the 8th, what does ROWS BETWEEN 6 PRECEDING AND CURRENT ROW cover, and what would you use instead for a true 7-day average?

   Show answer

   It covers the 1st, 2nd, 6th, 7th and 8th, so it reaches back eight days and averages over only five rows. Use a date spine so every day has a row, or a RANGE frame with an interval of 6 days where the dialect supports one. See window functions.

4. You switch the first-launch step of the funnel from an inner join to a LEFT JOIN to count drop-offs. Where must the 7-day bound live, and what happens if it sits in WHERE?

   Show answer

   It stays in the ON clause. A WHERE predicate on the right table removes the NULL rows the LEFT JOIN just preserved, so the query silently becomes an inner join again and the drop-off count is zero. See funnels with time constraints.

5. One customer has events at 10:00, 10:20, 11:00 and 11:10, with a 30-minute idle gap. What are the is_new_session flags and the final session ids?

   Show answer

   The flags are 1, 0, 1, 0, because the first event starts a session, the 20-minute gap does not, and the 40-minute gap does. The running sum gives session ids 1, 1, 2, 2, so two sessions of two events each. See sessionization.

6. A customer is active on March 1, 2, 3, 5 and 6. What run keys does the gaps-and-islands query produce, and what streaks come out?

   Show answer

   The row numbers are 1 to 5, so the keys are Feb 28 for March 1 to 3 and March 1 for March 5 and 6. That gives a three-day and a two-day streak. The key is constant within a run because the date and the row number both rise by one. Duplicates must be removed first or the key drifts. See gaps and islands.

Takeaway

• Most screen prompts are a composition of two or three patterns, so name the pattern before you type.
• ROW_NUMBER, RANK and DENSE_RANK differ only in how they treat ties, and the business question decides which one is right.
• Window frames matter: LAST_VALUE needs an explicit frame, and ROWS counts rows rather than days, so gaps in the data change what a moving average means.
• Put time bounds on a funnel step in the join condition, not in WHERE, so the step survives a change to an outer join.
• Sessionization and streaks both reduce to flagging boundaries or keying runs, then using a running sum or a date-minus-rank key.
• One CTE per logical step, named like prose, makes the query read as an outline of your thinking.

Writing the query is half the screen; the next chapter covers reading a broken one. → SQL Debugging

Further reading

• Window Functions (tutorial) — PostgreSQL documentation, 2026. A short worked introduction to PARTITION BY, ORDER BY and frames.
• Value Expressions, Window Function Calls — PostgreSQL documentation, 2026. The exact definition of frame clauses, including ROWS, RANGE, GROUPS and the default frame.
• Window Functions — PostgreSQL documentation, 2026. Reference for ROW_NUMBER, RANK, LAG, LEAD, FIRST_VALUE and LAST_VALUE.
• WITH Queries (Common Table Expressions) — PostgreSQL documentation, 2026. CTE behaviour and the recursive form used for hierarchies.
• Window function calls — Google Cloud, BigQuery documentation, 2026. How frames and ordering work in a warehouse dialect.
• Window functions — Snowflake documentation, 2026. Another warehouse reference for frames and analytic functions.