SQL Window Functions skill for Codex

A window query is right when its partitions, ordering and frame are all stated explicitly, ties are broken deterministically, and the result was checked against a small hand-computed sample. Most window bugs are silent: the query runs and returns plausible numbers that are wrong.

Before writing

1. Identify the dialect (AGENTS.md, connection config, ORM) and version. QUALIFY, RANGE with interval offsets, GROUPS frames, FILTER and IGNORE NULLS vary by engine.
2. State the grain in a sentence: “one row per user per day”, “one row per order”. Window functions do not change row count; if the output grain differs from the input, you need GROUP BY or a filter as well.
3. Build a 10-20 row fixture (a VALUES CTE) with ties, NULLs, gaps and partition boundaries, and know the expected output before running.

Anatomy and evaluation order

fn(args) OVER (
  PARTITION BY k            -- independent groups
  ORDER BY t, id            -- order inside each group; add a unique tiebreaker
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW   -- frame, for aggregates and value functions
)

Windows are computed after WHERE, GROUP BY and HAVING, before ORDER BY and LIMIT. So you cannot filter on a window result in WHERE: wrap it in a CTE/subquery, or use QUALIFY (BigQuery, Snowflake, DuckDB, Databricks, Teradata; not Postgres, MySQL, SQL Server or SQLite).

The default frame trap

With ORDER BY and no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Two consequences:

• LAST_VALUE(x) OVER (PARTITION BY k ORDER BY t) returns the current row’s value, not the partition’s last. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or FIRST_VALUE with descending order.
• Running totals with RANGE treat rows with equal ORDER BY values as peers and give them all the same, already-summed total. Use ROWS for a row-by-row running sum.

Always write the frame explicitly for aggregates over windows.

Recipes

Latest row per key (dedupe)

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, id DESC) AS rn
  FROM customer_snapshots
)
SELECT * FROM ranked WHERE rn = 1;

Postgres alternative: SELECT DISTINCT ON (customer_id) * FROM customer_snapshots ORDER BY customer_id, updated_at DESC, id DESC; QUALIFY dialects: ... QUALIFY ROW_NUMBER() OVER (...) = 1.

Top N per group

ROW_NUMBER gives exactly N; RANK includes ties (may return more than N, with gaps); DENSE_RANK returns the top N distinct values. Pick deliberately and say which. For large tables in Postgres, a LATERAL (... ORDER BY ... LIMIT N) join backed by an index on (group_key, sort_key) can beat a window over the whole table.

Period over period

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly_revenue;

LAG looks at the previous row, not the previous month. If months can be missing, generate a complete calendar first (generate_series in Postgres, a date spine table elsewhere) and left join, or the “previous” row may be two months back.

Running and moving aggregates

SUM(amount) OVER (PARTITION BY account_id ORDER BY posted_at, id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance,
AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_rows

A 7-row average is a 7-day average only if every day has exactly one row. For true time windows use a date spine, or RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW where supported (Postgres 11+, DuckDB, Snowflake has limits; MySQL supports RANGE INTERVAL 6 DAY PRECEDING).

Gaps and islands (streaks, contiguous ranges)

WITH d AS (
  SELECT user_id, activity_date,
         activity_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS int) AS grp
  FROM (SELECT DISTINCT user_id, activity_date FROM activity) x
)
SELECT user_id, MIN(activity_date) AS streak_start, MAX(activity_date) AS streak_end,
       COUNT(*) AS days
FROM d GROUP BY user_id, grp;

Consecutive dates minus a consecutive row number give a constant per island. Dedupe first, or duplicates break the arithmetic. Date minus integer is Postgres syntax; use DATE_SUB/DATEADD/DATE_ADD in other dialects.

Sessionization (new session after 30 minutes idle)

WITH e AS (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL '30 minutes'
                OR LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL
              THEN 1 ELSE 0 END AS is_new
  FROM events
)
SELECT user_id, ts,
       SUM(is_new) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING) AS session_no
FROM e;

Share of total and percentiles

amount / SUM(amount) OVER (PARTITION BY region) AS share_of_region,
PERCENT_RANK() OVER (ORDER BY amount) AS pct_rank,
NTILE(4) OVER (ORDER BY amount) AS quartile

Watch integer division: cast one side to numeric/decimal in dialects that truncate (Postgres, SQL Server, SQLite).

Recursive CTEs (hierarchies, graphs)

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || c.id
  FROM categories c JOIN tree t ON c.parent_id = t.id
  WHERE NOT c.id = ANY(t.path) AND t.depth < 50          -- cycle and depth guard
)
SELECT * FROM tree;

Array paths are Postgres; elsewhere carry a delimited string or use the dialect’s CYCLE clause (Postgres 14+ also has CYCLE id SET is_cycle USING path). SQL Server omits the RECURSIVE keyword and caps recursion with OPTION (MAXRECURSION n).

Performance

• Each distinct PARTITION BY/ORDER BY combination needs its own sort. Reuse one window definition (WINDOW w AS (...), then OVER w) and keep the number of distinct windows small.
• An index on (partition_cols, order_cols) lets Postgres and MySQL skip the sort.
• Filter rows in WHERE before windowing whenever the filter does not change the window’s meaning. Filtering after (in an outer query) is required when it does, for example “rank among all, then show top 10 in region X”.
• In warehouses (BigQuery, Snowflake), a window over a huge unpartitioned set funnels through one worker; partition by something with many values.

Gotchas

• Non-deterministic order: ROW_NUMBER() OVER (ORDER BY created_at) with duplicate timestamps picks arbitrarily and can change between runs. Add a unique tiebreaker.
• COUNT(*) OVER () gives the total row count after WHERE, useful for pagination totals, but costs a full pass.
• NULLs sort first or last depending on dialect (Postgres: last for ASC). State NULLS LAST when it matters and the dialect supports it.
• LAG(x, 1, 0) default only applies when there is no previous row, not when the previous value is NULL.
• Mixing window functions and GROUP BY: the window runs on grouped rows, so SUM(SUM(amount)) OVER (...) is valid and often what you want.

Verification

• Grain stated; output row count matches it
• Every ORDER BY in OVER has a unique tiebreaker
• Frames explicit for aggregates and FIRST/LAST_VALUE
• Fixture with ties, NULLs, gaps and boundaries produces the hand-computed answer
• On real data, totals reconcile with a plain GROUP BY query
• Plan checked for repeated sorts on large inputs

Output template

## Query: <question answered>
**Dialect:** <engine + version>
**Grain:** one row per <...>
**SQL:** <query>
**Window choices:** <function, partition, order + tiebreaker, frame, and why>
**Checked with:** <fixture cases, reconciliation query>