Reference

Appendix H: SQL Recipes & Patterns

Overview

This appendix is a practitioner's cookbook of SQL patterns that recur constantly in data engineering, analytics, and machine learning pipelines. Each recipe states the problem concisely, shows a working SQL block, and highlights the key ideas and common pitfalls. Dialects are noted where behavior diverges; examples are written for PostgreSQL / BigQuery style unless stated otherwise.

H.1 Deduplication

Problem

A source table has duplicate rows (exact or near-exact). Keep one representative row per logical entity.

Exact duplicates — keep any one row:

SQL
-- CTE + ROW_NUMBER is the most portable approach
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id, event_type, event_ts
                            ORDER BY  load_ts DESC) AS rn
  FROM   raw_events
)
SELECT * EXCLUDE(rn)          -- BigQuery / DuckDB syntax
FROM   ranked
WHERE  rn = 1;

For databases that lack EXCLUDE, list columns explicitly or use a subquery:

SQL
SELECT user_id, event_type, event_ts
FROM   ranked
WHERE  rn = 1;

Near-duplicate — deduplicate on a subset of columns, preferring the most recent record:

SQL
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY email                   -- business key
           ORDER BY     updated_at DESC NULLS LAST
         ) AS rn
  FROM   customers
)
SELECT * EXCLUDE(rn) FROM ranked WHERE rn = 1;

Pitfall: SELECT DISTINCT deduplicates only fully identical rows and cannot express "prefer the newest." Use ROW<em>NUMBER when you need control over which row survives.

H.2 Top-N Per Group

Problem

Return the top 3 products by revenue within each category.

SQL
WITH ranked AS (
  SELECT
    category,
    product_id,
    SUM(revenue) AS total_revenue,
    RANK() OVER (PARTITION BY category
                 ORDER BY SUM(revenue) DESC) AS rnk
  FROM   orders
  GROUP BY category, product_id
)
SELECT category, product_id, total_revenue, rnk
FROM   ranked
WHERE  rnk <= 3
ORDER  BY category, rnk;

RANK vs DENSE</em>RANK vs ROW<em>NUMBER:

  • ROW</em>NUMBER — unique integers, no ties; use when you must get exactly N rows.
  • RANK — ties get the same rank, next rank skips (1, 1, 3).
  • DENSE<em>RANK — ties share a rank, next rank is consecutive (1, 1, 2); use this when "top 3 distinct scores" is the intent.

    • H.3 Running Totals and Cumulative Aggregates

    Problem

Compute a running (cumulative) sum of daily revenue ordered by date.

SQL
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                        AS running_total,
  AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                        AS running_avg
FROM   daily_revenue_summary
ORDER  BY order_date;

Why ROWS vs RANGE?

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW groups all rows with the same ORDER BY value into the same window frame, which can double-count on ties. ROWS is physical-row-based and usually correct for running totals.

Cumulative distribution / percentile rank:

SQL
SELECT
  score,
  CUME_DIST()   OVER (ORDER BY score) AS cdf,
  PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM   exam_results;

H.4 Moving Averages and Rolling Windows

Problem

7-day and 28-day rolling averages of daily active users (DAU).

SQL
SELECT
  dt,
  dau,
  AVG(dau) OVER (ORDER BY dt ROWS BETWEEN 6  PRECEDING AND CURRENT ROW) AS ma_7d,
  AVG(dau) OVER (ORDER BY dt ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) AS ma_28d
FROM   daily_active_users
ORDER  BY dt;

Pitfall — incomplete windows at the start of the series: The first 6 rows of ma</em>7d average fewer than 7 data points. Whether that is acceptable depends on the use case. To exclude partial windows, filter with ROW<em>NUMBER():

SQL
WITH windowed AS (
  SELECT
    dt,
    dau,
    AVG(dau) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d,
    ROW_NUMBER() OVER (ORDER BY dt) AS rn
  FROM daily_active_users
)
SELECT dt, dau, ma_7d
FROM   windowed
WHERE  rn >= 7;

Range-based (calendar) window — useful when the date column has gaps:

SQL
-- BigQuery: RANGE with INTERVAL
SELECT
  dt,
  dau,
  AVG(dau) OVER (
    ORDER BY UNIX_DATE(dt)
    RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7d
FROM daily_active_users;

H.5 Gaps and Islands

Problem

Given a sequence of login dates per user, identify contiguous "active streaks" (islands) and the gaps between them.

SQL
-- Step 1: assign a group ID to each island
WITH lagged AS (
  SELECT
    user_id,
    login_date,
    LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date
  FROM   user_logins
),
flagged AS (
  SELECT
    user_id,
    login_date,
    -- new island starts when gap > 1 day (or it's the first row)
    SUM(CASE WHEN login_date - prev_date > 1 OR prev_date IS NULL THEN 1 ELSE 0 END)
        OVER (PARTITION BY user_id ORDER BY login_date) AS island_id
  FROM lagged
)
SELECT
  user_id,
  island_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*)        AS streak_length_days
FROM   flagged
GROUP  BY user_id, island_id
ORDER  BY user_id, streak_start;

Key idea: The classic "row-number subtraction" trick also works when dates are dense integers (no calendar gaps):

SQL
WITH numbered AS (
  SELECT
    user_id,
    login_date,
    login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)::int
      AS grp     -- constant within each consecutive run
  FROM user_logins
)
SELECT user_id, MIN(login_date), MAX(login_date), COUNT(*) AS days
FROM   numbered
GROUP  BY user_id, grp;

H.6 Pivoting (Rows to Columns)

Problem

Turn a key-value table of product metrics into one column per metric.

Static pivot using conditional aggregation (all dialects):

SQL
SELECT
  product_id,
  MAX(CASE WHEN metric = 'views'     THEN value END) AS views,
  MAX(CASE WHEN metric = 'clicks'    THEN value END) AS clicks,
  MAX(CASE WHEN metric = 'purchases' THEN value END) AS purchases
FROM   product_metrics
GROUP  BY product_id;

PostgreSQL crosstab (tablefunc extension):

SQL
SELECT *
FROM   crosstab(
  'SELECT product_id, metric, value
   FROM   product_metrics
   ORDER  BY 1, 2',
  $$VALUES ('views'), ('clicks'), ('purchases')$$
) AS ct(product_id INT, views NUMERIC, clicks NUMERIC, purchases NUMERIC);

BigQuery dynamic pivot using EXECUTE IMMEDIATE (when the column list is not known at compile time):

SQL
-- Build and execute the pivot dynamically
DECLARE metrics ARRAY<STRING>;
SET metrics = (SELECT ARRAY_AGG(DISTINCT metric ORDER BY metric) FROM product_metrics);

EXECUTE IMMEDIATE FORMAT("""
  SELECT product_id, %s
  FROM   product_metrics
  PIVOT  (MAX(value) FOR metric IN (%s))
""",
  (SELECT STRING_AGG(m, ', ') FROM UNNEST(metrics) m),
  (SELECT STRING_AGG(FORMAT("'%s'", m), ', ') FROM UNNEST(metrics) m)
);

H.7 Cohort Retention Analysis

Problem

For each weekly signup cohort, what fraction of users return in weeks 1, 2, 3, … after signup?

SQL
WITH cohorts AS (
  -- assign each user to a cohort week
  SELECT
    user_id,
    DATE_TRUNC('week', signup_date) AS cohort_week
  FROM   users
),
activity AS (
  -- find all the weeks each user was active
  SELECT
    a.user_id,
    DATE_TRUNC('week', a.activity_date) AS activity_week
  FROM   user_activity a
),
joined AS (
  SELECT
    c.cohort_week,
    a.activity_week,
    DATE_DIFF('week', c.cohort_week, a.activity_week) AS period_number,
    COUNT(DISTINCT a.user_id)   AS active_users
  FROM       cohorts c
  INNER JOIN activity a USING (user_id)
  WHERE  a.activity_week >= c.cohort_week
  GROUP  BY 1, 2, 3
),
cohort_sizes AS (
  SELECT cohort_week, COUNT(*) AS cohort_size
  FROM   cohorts
  GROUP  BY cohort_week
)
SELECT
  j.cohort_week,
  j.period_number,
  j.active_users,
  cs.cohort_size,
  ROUND(100.0 * j.active_users / cs.cohort_size, 2) AS retention_pct
FROM       joined j
INNER JOIN cohort_sizes cs USING (cohort_week)
ORDER  BY  cohort_week, period_number;

The output is a "retention matrix" that you can pivot (see H.6) to display as a standard retention heat-map.

H.8 Funnel Analysis

Problem

Measure how many users progress through a multi-step conversion funnel (e.g., view → add-to-cart → checkout → purchase) and where drop-off occurs.

SQL
WITH funnel AS (
  SELECT
    user_id,
    MAX(CASE WHEN event = 'view'        THEN 1 ELSE 0 END) AS did_view,
    MAX(CASE WHEN event = 'add_to_cart' THEN 1 ELSE 0 END) AS did_add,
    MAX(CASE WHEN event = 'checkout'    THEN 1 ELSE 0 END) AS did_checkout,
    MAX(CASE WHEN event = 'purchase'    THEN 1 ELSE 0 END) AS did_purchase
  FROM   events
  WHERE  event_date BETWEEN '2025-01-01' AND '2025-01-31'
  GROUP  BY user_id
)
SELECT
  SUM(did_view)     AS step1_view,
  SUM(did_add)      AS step2_add_to_cart,
  SUM(did_checkout) AS step3_checkout,
  SUM(did_purchase) AS step4_purchase,
  ROUND(100.0 * SUM(did_add)      / NULLIF(SUM(did_view),     0), 2) AS view_to_add_pct,
  ROUND(100.0 * SUM(did_checkout) / NULLIF(SUM(did_add),      0), 2) AS add_to_checkout_pct,
  ROUND(100.0 * SUM(did_purchase) / NULLIF(SUM(did_checkout), 0), 2) AS checkout_to_purchase_pct
FROM   funnel;

Ordered funnel — require steps happen in sequence:

SQL
WITH ordered AS (
  SELECT
    user_id,
    MIN(CASE WHEN event = 'view'        THEN event_ts END) AS ts_view,
    MIN(CASE WHEN event = 'add_to_cart' THEN event_ts END) AS ts_add,
    MIN(CASE WHEN event = 'checkout'    THEN event_ts END) AS ts_checkout,
    MIN(CASE WHEN event = 'purchase'    THEN event_ts END) AS ts_purchase
  FROM   events
  GROUP  BY user_id
)
SELECT
  COUNT(*)                                            AS entered_funnel,
  COUNT(CASE WHEN ts_add      > ts_view        THEN 1 END) AS reached_add,
  COUNT(CASE WHEN ts_checkout > ts_add         THEN 1 END) AS reached_checkout,
  COUNT(CASE WHEN ts_purchase > ts_checkout    THEN 1 END) AS completed_purchase
FROM   ordered
WHERE  ts_view IS NOT NULL;

H.9 Date Spines

Problem

Many aggregations produce no row for dates with zero activity. A date spine fills those gaps so time-series plots and window functions work correctly.

Generate a date spine in pure SQL:

SQL
-- PostgreSQL / DuckDB
SELECT
  GENERATE_SERIES(
    '2025-01-01'::date,
    '2025-12-31'::date,
    INTERVAL '1 day'
  )::date AS dt;

SQL
-- BigQuery
SELECT dt
FROM   UNNEST(
  GENERATE_DATE_ARRAY('2025-01-01', '2025-12-31', INTERVAL 1 DAY)
) AS dt;

Join the spine to fill gaps:

SQL
WITH spine AS (
  SELECT GENERATE_SERIES('2025-01-01'::date,
                         '2025-12-31'::date,
                         INTERVAL '1 day')::date AS dt
),
daily AS (
  SELECT order_date, SUM(revenue) AS revenue
  FROM   orders
  GROUP  BY order_date
)
SELECT
  s.dt                          AS order_date,
  COALESCE(d.revenue, 0)        AS revenue
FROM       spine s
LEFT  JOIN daily d ON d.order_date = s.dt
ORDER  BY  s.dt;

Per-user spine — cross join the spine with a user list to get one row per (user, day):

SQL
WITH spine AS (
  SELECT dt FROM UNNEST(GENERATE_DATE_ARRAY('2025-01-01','2025-01-31', INTERVAL 1 DAY)) dt
),
users AS (SELECT DISTINCT user_id FROM events)
SELECT u.user_id, s.dt
FROM   users u CROSS JOIN spine s;

H.10 Slowly Changing Dimensions (SCD Type 2)

Problem

Track the full history of attribute changes for a dimension (e.g., a customer's address or plan tier), keeping one row per version with valid-from / valid-to dates.

Loading new SCD Type 2 records (merge pattern):

SQL
-- Assume a staging table `stg_customers` with the latest snapshot
-- and a dimension table `dim_customers` with effective_from / effective_to

-- Step 1: expire rows whose attributes have changed
UPDATE dim_customers AS d
SET    effective_to = CURRENT_DATE - INTERVAL '1 day',
       is_current   = FALSE
FROM   stg_customers s
WHERE  d.customer_key  = s.customer_id
  AND  d.is_current    = TRUE
  AND  (d.plan_tier   <> s.plan_tier
     OR d.country     <> s.country);

-- Step 2: insert new current versions
INSERT INTO dim_customers (customer_key, plan_tier, country,
                           effective_from, effective_to, is_current)
SELECT
  s.customer_id,
  s.plan_tier,
  s.country,
  CURRENT_DATE,
  '9999-12-31',
  TRUE
FROM   stg_customers s
JOIN   dim_customers d
  ON   d.customer_key = s.customer_id
 AND   d.is_current   = FALSE          -- just expired
 AND   d.effective_to = CURRENT_DATE - INTERVAL '1 day';

Point-in-time lookup — what was a customer's plan tier on 2024-06-15?

SQL
SELECT customer_key, plan_tier
FROM   dim_customers
WHERE  customer_key  = 42
  AND  effective_from <= '2024-06-15'
  AND  effective_to   >= '2024-06-15';

Tip: The sentinel value 9999-12-31 for effective</em>to of the current row allows range predicates without is<em>current filters and avoids NULL-handling complexity.

H.11 Sessionization

Problem

Group a stream of user events into sessions, where a new session starts if the gap between consecutive events exceeds 30 minutes.

SQL
WITH gaps AS (
  SELECT
    user_id,
    event_ts,
    LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) AS prev_ts
  FROM   events
),
session_starts AS (
  SELECT
    user_id,
    event_ts,
    SUM(CASE
          WHEN prev_ts IS NULL
            OR event_ts - prev_ts > INTERVAL '30 minutes'
          THEN 1 ELSE 0
        END)
      OVER (PARTITION BY user_id ORDER BY event_ts) AS session_id
  FROM gaps
)
SELECT
  user_id,
  session_id,
  MIN(event_ts) AS session_start,
  MAX(event_ts) AS session_end,
  COUNT(*)      AS event_count,
  MAX(event_ts) - MIN(event_ts) AS session_duration
FROM   session_starts
GROUP  BY user_id, session_id
ORDER  BY user_id, session_start;

H.12 Self-Join Patterns and Lag/Lead Comparisons

Problem

Compute the day-over-day percentage change in revenue.

SQL
SELECT
  dt,
  revenue,
  LAG(revenue) OVER (ORDER BY dt)       AS prev_revenue,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY dt))
           / NULLIF(LAG(revenue) OVER (ORDER BY dt), 0),
    2
  )                                       AS pct_change
FROM   daily_revenue
ORDER  BY dt;

Period-over-period with a self-join (useful when you want flexibility in the look-back period):

SQL
SELECT
  a.dt,
  a.revenue        AS this_week,
  b.revenue        AS last_week,
  ROUND(100.0 * (a.revenue - b.revenue) / NULLIF(b.revenue, 0), 2) AS wow_pct
FROM   daily_revenue a
LEFT   JOIN daily_revenue b
  ON   b.dt = a.dt - INTERVAL '7 days';

H.13 Percentiles and Distributions

Problem

Compute the median, p75, p90, p99 of query latency.

SQL
-- ANSI SQL (supported in PostgreSQL, BigQuery, Snowflake)
SELECT
  PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY latency_ms) AS p50,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY latency_ms) AS p75,
  PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY latency_ms) AS p90,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY latency_ms) AS p99
FROM   query_log;

PERCENTILE</em>CONT interpolates between adjacent values (continuous). PERCENTILE<em>DISC returns an actual value from the data (discrete). For latency SLAs, PERCENTILE</em>DISC is typically more honest.

Histogram buckets:

SQL
SELECT
  WIDTH_BUCKET(latency_ms, 0, 5000, 20) AS bucket,
  MIN(latency_ms)  AS bucket_min,
  MAX(latency_ms)  AS bucket_max,
  COUNT(*)         AS frequency
FROM   query_log
WHERE  latency_ms BETWEEN 0 AND 5000
GROUP  BY bucket
ORDER  BY bucket;

H.14 Set Operations and Anti-Joins

Problem

Find users who signed up but never made a purchase (anti-join).

SQL
-- Preferred: NOT EXISTS (planner can often use an index scan)
SELECT u.user_id, u.email
FROM   users u
WHERE  NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

-- Alternative: LEFT JOIN / IS NULL
SELECT u.user_id, u.email
FROM   users  u
LEFT   JOIN orders o USING (user_id)
WHERE  o.user_id IS NULL;

-- EXCEPT (returns distinct rows only — be careful with deduplication semantics)
SELECT user_id FROM users
EXCEPT
SELECT DISTINCT user_id FROM orders;

Pitfall: NOT IN with a subquery silently returns no rows if the subquery returns any NULL values. Prefer NOT EXISTS.

H.15 Working with JSON Columns

Modern warehouses store semi-structured data as JSON. Key extraction patterns:

SQL
-- PostgreSQL
SELECT
  id,
  payload->>'event_type'            AS event_type,
  (payload->'properties'->>'amount')::numeric AS amount
FROM   raw_events;

-- BigQuery
SELECT
  id,
  JSON_VALUE(payload, '$.event_type')            AS event_type,
  CAST(JSON_VALUE(payload, '$.properties.amount') AS NUMERIC) AS amount
FROM   raw_events;

Flatten a JSON array to rows (BigQuery JSON<em>TABLE / PostgreSQL json</em>array<em>elements):

SQL
-- PostgreSQL
SELECT id, elem->>'sku' AS sku, (elem->>'qty')::int AS qty
FROM   orders,
       json_array_elements(line_items) AS elem;

Quick-Reference: Window Function Frame Clauses

The frame clause controls which rows fall in the window:

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — all rows from the start up to the current row (standard running total).
  • ROWS BETWEEN N PRECEDING AND CURRENT ROW — rolling window of N+1 rows.
  • ROWS BETWEEN N PRECEDING AND N FOLLOWING — centered moving average.
  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — same as above but treats tied ORDER BY values as a single logical row (use cautiously).
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING — suffix aggregates (e.g., remaining balance).

Functions that ignore the frame clause entirely (they always operate over the whole partition): RANK, DENSE</em>RANK, ROW<em>NUMBER, NTILE, LAG, LEAD, FIRST</em>VALUE/LAST<em>VALUE (note: FIRST</em>VALUE/LAST_VALUE do respect the frame, which is often surprising — use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true partition-level first/last).

Common Pitfalls Summary

  • Division by zero — always wrap denominators in NULLIF(expr, 0).
  • NULL in aggregates — SUM, AVG, COUNT(col) silently ignore NULLs; COUNT(*) counts NULLs. Use COALESCE before aggregating when NULLs should count as zero.
  • RANGE vs ROWS — RANGE with ties can produce unexpected results; default to ROWS for running totals.
  • NOT IN with NULLs — if the subquery can return NULLs, the whole NOT IN expression evaluates to NULL (no rows pass). Use NOT EXISTS.
  • Implicit type coercion in JOINs — joining VARCHAR keys to INTEGER keys forces a full scan on some engines. Cast explicitly.
  • Window functions execute after WHERE/GROUP BY — you cannot filter on a window function result in the same query level; wrap in a CTE or subquery.