05_window_functions.sql

-- Window functions: like aggregates, but they don't collapse rows — each
-- row keeps its own identity while also seeing a computed value "over" a
-- window of related rows (via PARTITION BY/ORDER BY).
--   sqlite3 cookbook.db < sql/05_window_functions.sql
-- Needs SQLite >= 3.25 (2018) or any modern Postgres/MySQL/etc.

.headers on
.mode column

-- ROW_NUMBER(): a unique, sequential rank within each partition — useful
-- for "give me the single best row per group" without a self-join.
SELECT
    sample_id, chrom, pos, qual,
    ROW_NUMBER() OVER (PARTITION BY sample_id ORDER BY qual DESC) AS rank_in_sample
FROM variants
ORDER BY sample_id, rank_in_sample;

-- RANK() vs. DENSE_RANK(): both handle ties, but RANK() leaves gaps in the
-- numbering after a tie (1,2,2,4) while DENSE_RANK() doesn't (1,2,2,3).
SELECT
    chrom, pos, qual,
    RANK()       OVER (ORDER BY qual DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY qual DESC) AS dense_rnk
FROM variants
ORDER BY qual DESC;

-- Only the top-ranked row per sample, using the ROW_NUMBER() query above as
-- a CTE and filtering on it — you can't filter on a window function result
-- directly in the same SELECT's WHERE clause, this two-step pattern (CTE,
-- then filter) is the standard way around that.
WITH ranked AS (
    SELECT
        sample_id, chrom, pos, qual,
        ROW_NUMBER() OVER (PARTITION BY sample_id ORDER BY qual DESC) AS rnk
    FROM variants
)
SELECT sample_id, chrom, pos, qual FROM ranked WHERE rnk = 1;

-- LAG()/LEAD(): look at the previous/next row within a window — handy for
-- e.g. spacing between consecutive variant positions on the same chromosome.
SELECT
    chrom, pos,
    LAG(pos)  OVER (PARTITION BY chrom ORDER BY pos) AS prev_pos,
    pos - LAG(pos) OVER (PARTITION BY chrom ORDER BY pos) AS gap_from_prev
FROM variants
ORDER BY chrom, pos;

-- Running total: a SUM() as a window function accumulates instead of
-- collapsing, when combined with ORDER BY in the OVER() clause.
SELECT
    sample_id, pos, depth,
    SUM(depth) OVER (PARTITION BY sample_id ORDER BY pos) AS running_depth_total
FROM variants
ORDER BY sample_id, pos;