Twelve shapes that solve
most of the actual work.

You want this when: an extract has the same record more than once and you need exactly one — usually the most recent. This is the most-used pattern in this entire page.

Number the rows within each group, ordered so the one you want is number 1. Then keep number 1. That's it — and once you see it, you'll use it constantly.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id        -- one per customer
      ORDER BY updated_at DESC       -- newest wins
    ) AS rn
  FROM raw_customer
)
SELECT * FROM ranked WHERE rn = 1;

You want this when: anyone says "versus last month", "change on prior", "movement", or "delta". Which in a finance org is roughly every second request.

Reach backwards a fixed number of rows and pull that value onto the current row, so you can subtract them side by side. LAG looks back; LEAD looks forward.

SELECT
  period,
  cost_centre,
  amount,
  LAG(amount) OVER (PARTITION BY cost_centre ORDER BY period) AS prior_amount,
  amount - LAG(amount) OVER (PARTITION BY cost_centre ORDER BY period) AS movement
FROM monthly_actuals;

You want this when: "cumulative year to date", "3-month rolling average", "balance over time". Anything that accumulates.

Same window, but instead of one specific row you define a range of rows to aggregate over — "everything up to here", or "these three".

SELECT
  period, amount,
  SUM(amount) OVER (
    ORDER BY period
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW      -- running total
  ) AS ytd,
  AVG(amount) OVER (
    ORDER BY period
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW            -- 3-month rolling
  ) AS rolling_3m
FROM monthly_actuals;

You want this when: "top 5 customers per region", "biggest three variances per department". Note this is pattern 01 with a different filter — same machinery.

WITH r AS (
  SELECT region, customer, revenue,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rk
  FROM sales
)
SELECT * FROM r WHERE rk <= 5;

You want this when: "how many days in a row was this breached", "find the missing invoice numbers", "how long was each outage". Sounds exotic, comes up constantly once you can name it.

The trick that makes it click: if you number the rows sequentially and subtract that from the date, every row in a consecutive run gets the same answer. Group by that answer and you've found your runs. It's a genuinely clever trick and it's worth sitting with for a minute.

WITH g AS (
  SELECT breach_date,
    DATEADD(day,
      -ROW_NUMBER() OVER (ORDER BY breach_date),
      breach_date) AS grp                    -- constant within a run
  FROM daily_breaches
)
SELECT MIN(breach_date) AS run_start,
       MAX(breach_date) AS run_end,
       COUNT(*)        AS days_in_run
FROM g GROUP BY grp
ORDER BY run_start;

You want this when: a finance extract has a column per month (Jan, Feb, Mar…). Humans love this layout; every analytical tool hates it. Almost every spreadsheet-sourced dataset needs this first.

Turn twelve columns into two: one saying which month, one saying the value. Long and boring beats wide and clever — every time, for anything downstream.

-- portable version: works everywhere
SELECT account, 'Jan' AS period, jan AS amount FROM budget
UNION ALL SELECT account, 'Feb', feb FROM budget
UNION ALL SELECT account, 'Mar', mar FROM budget;

-- T-SQL has a built-in for it
SELECT account, period, amount
FROM budget
UNPIVOT (amount FOR period IN (jan, feb, mar)) AS u;

You want this when: a month with no activity vanishes from your report instead of showing zero, so charts have gaps and prior-period comparisons quietly lie (see pattern 02).

Build a table of every date you care about, then LEFT JOIN your data onto it. Now absence is visible as a zero rather than invisible as a missing row — which is nearly always what people actually meant.

WITH spine AS (
  SELECT CAST('2024-01-01' AS date) AS d
  UNION ALL
  SELECT DATEADD(month, 1, d) FROM spine WHERE d < '2026-12-01'
)
SELECT s.d AS period, COALESCE(a.amount, 0) AS amount
FROM spine s
LEFT JOIN monthly_actuals a ON a.period = s.d
OPTION (MAXRECURSION 0);

You want this when: "group these into small / medium / large", ageing buckets, materiality tiers. Any time a continuous number needs to become something you can group by.

SELECT invoice_id, days_overdue,
  CASE
    WHEN days_overdue <= 0  THEN 'Current'
    WHEN days_overdue <= 30 THEN '1-30'
    WHEN days_overdue <= 60 THEN '31-60'
    ELSE '60+'
  END AS ageing_bucket
FROM receivables;

You want this when: a customer moves region, a cost centre is renamed, an account is remapped — and last year's report must still show what was true then. This is the pattern that makes you sound senior, because most people never learn it.

Instead of overwriting the row, close the old one off with an end date and insert a new one. Each row becomes "this was true between these dates", and you join on the date to get the version that applied at the time.

-- one row per version, not per entity
customer_key | customer_id | region | valid_from | valid_to   | is_current
1            | C100        | North  | 2024-01-01 | 2025-06-30 | 0
2            | C100        | South  | 2025-07-01 | 9999-12-31 | 1

-- "what was true when the transaction happened"
SELECT t.*, c.region
FROM transactions t
JOIN dim_customer c
  ON c.customer_id = t.customer_id
 AND t.txn_date BETWEEN c.valid_from AND c.valid_to;

You want this when: "which invoices didn't make it across", "which employees have no cost centre", "what's missing". The single fastest way to find a data problem.

-- readable and fast: no rows on the right
SELECT a.*
FROM source_system a
LEFT JOIN target_system b ON b.doc_id = a.doc_id
WHERE b.doc_id IS NULL;

You want this when: two systems disagree and you need to explain the gap, not just measure it. This is the deliverable that ends arguments — and the one people remember you for.

FULL OUTER JOIN keeps rows that exist on either side, so you catch all three failure modes at once: missing on the left, missing on the right, and present on both but different. Then categorise every difference — that categorisation is the actual product.

SELECT
  COALESCE(a.doc_id, b.doc_id)          AS doc_id,
  a.amount                              AS source_amount,
  b.amount                              AS target_amount,
  COALESCE(a.amount,0) - COALESCE(b.amount,0) AS diff,
  CASE
    WHEN b.doc_id IS NULL            THEN 'Missing in target'
    WHEN a.doc_id IS NULL            THEN 'Missing in source'
    WHEN ABS(a.amount-b.amount) < 0.01 THEN 'Match'
    WHEN a.posted_date <> b.posted_date THEN 'Timing difference'
    ELSE 'Unexplained — investigate'
  END AS reason
FROM source_ledger a
FULL OUTER JOIN target_ledger b ON b.doc_id = a.doc_id;

You want this when: a join returns far fewer rows than expected and you can't see why. The answer is almost always invisible characters — and you'll lose an hour to this if you don't check it first.

-- the usual suspects, in order of how often they're the culprit
SELECT
  UPPER(TRIM(doc_id))                      AS clean_id,   -- case + spaces
  LEN(doc_id) - LEN(TRIM(doc_id))          AS pad_chars,  -- hidden padding
  TRY_CAST(amount_text AS decimal(18,2))     AS amount      -- NULL = bad rows
FROM raw_extract;

-- diagnose a bad join fast: how many keys actually overlap?
SELECT
  (SELECT COUNT(DISTINCT doc_id) FROM a)                        AS keys_a,
  (SELECT COUNT(DISTINCT doc_id) FROM b)                        AS keys_b,
  (SELECT COUNT(DISTINCT a.doc_id) FROM a JOIN b ON b.doc_id=a.doc_id) AS overlap;