Window functions

-- GROUP BY: 3 departments → 3 rows. the employees are gone.
SELECT dept, AVG(salary) FROM emp GROUP BY dept;

-- window: 50 employees → 50 rows, each with its dept average alongside
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS vs_avg
  FROM emp;
--   comparing a row to its group is the canonical use.
--   without windows this needs a join to a grouped subquery.

-- whole result set, all rows in the window
SUM(x) OVER ()

-- one window per dept, all of that dept's rows
SUM(x) OVER (PARTITION BY dept)

-- one window per dept, ordered — and NOW the frame default
-- makes this a RUNNING total, not a partition total.
SUM(x) OVER (PARTITION BY dept ORDER BY hired_at)

-- with ORDER BY in OVER(), the default frame is:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- without ORDER BY, it is:
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

CREATE TABLE t (d DATE, amt INT);
INSERT INTO t VALUES
  ('2026-01-01', 10),
  ('2026-01-02', 20),
  ('2026-01-02', 30),    -- a tie on the ORDER BY column
  ('2026-01-03', 40);

SELECT d, amt,
  SUM(amt) OVER (ORDER BY d)                    AS default_range,
  SUM(amt) OVER (ORDER BY d
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS explicit_rows
FROM t ORDER BY d, amt;

--  d           amt   default_range   explicit_rows
--  2026-01-01   10        10              10
--  2026-01-02   20        60  ← jumps     30
--  2026-01-02   30        60  ← same!     60
--  2026-01-03   40       100             100
--
-- RANGE included BOTH rows dated 01-02 for each of them.
-- If you wanted a row-by-row running total, you needed ROWS.

UNBOUNDED PRECEDING -- start of the partition
n PRECEDING -- n rows/values back
CURRENT ROW -- this row (and peers, under RANGE)
n FOLLOWING -- n rows/values forward
UNBOUNDED FOLLOWING -- end of the partition
-- a centred 7-day moving average
AVG(x) OVER (ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)

-- RANGE with an INTERVAL: genuinely 7 days, not 7 rows
-- (correct when days can be missing)
AVG(x) OVER (ORDER BY d
  RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)

-- the three highest-paid employees in each department
WITH ranked AS (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept
                            ORDER BY salary DESC) AS rn
    FROM emp
)
SELECT * FROM ranked WHERE rn <= 3;
--   the CTE is required: you cannot filter on rn in WHERE (ch 6).

LAG(x, offset, default)    -- a previous row's value
LEAD(x, offset, default)   -- a following row's value
FIRST_VALUE(x)            -- first in the FRAME
LAST_VALUE(x)             -- last in the FRAME  ← see the trap
NTH_VALUE(x, n)           -- nth in the frame

-- day-over-day change, and percentage change
SELECT d, revenue,
  LAG(revenue) OVER (ORDER BY d)                    AS prev,
  revenue - LAG(revenue) OVER (ORDER BY d)        AS delta,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY d))
        / NULLIF(LAG(revenue) OVER (ORDER BY d), 0), 1) AS pct
FROM daily;
--   NULLIF guards the division when the previous day was 0.
-- gap between consecutive events per user
SELECT user_id, event_at,
  TIMESTAMPDIFF(SECOND,
    LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at),
    event_at) AS gap_s
FROM events;

-- running total — note the explicit ROWS (ch 32)
SUM(amt) OVER (ORDER BY d
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- running total per account, resetting each account
SUM(amt) OVER (PARTITION BY account_id ORDER BY d
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- trailing 7-row moving average
AVG(amt) OVER (ORDER BY d ROWS 6 PRECEDING)

-- share of the partition total: two windows, one query
ROUND(100.0 * amt / SUM(amt) OVER (PARTITION BY region), 1) AS pct_of_region

-- islands of consecutive days a user was active
WITH marked AS (
  SELECT user_id, d,
         DATE_SUB(d, INTERVAL
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d)
           DAY) AS grp
    FROM activity
)
SELECT user_id,
       MIN(d)   AS streak_start,
       MAX(d)   AS streak_end,
       COUNT(*) AS streak_days
  FROM marked
 GROUP BY user_id, grp
 ORDER BY user_id, streak_start;

-- why it works:
--   d           rn    d - rn days
--   01-01        1    2025-12-31   ┐ same → one island
--   01-02        2    2025-12-31   ┘
--   01-05        3    2026-01-02   ← changed → new island
--   01-06        4    2026-01-02

WITH flagged AS (
  SELECT user_id, event_at,
    CASE WHEN TIMESTAMPDIFF(MINUTE,
           LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at),
           event_at) > 30 THEN 1 ELSE 0 END AS new_session
  FROM events
),
sessioned AS (
  SELECT *, SUM(new_session) OVER (PARTITION BY user_id
                                ORDER BY event_at
    ROWS UNBOUNDED PRECEDING) AS session_id
  FROM flagged
)
SELECT user_id, session_id,
       MIN(event_at) AS started, MAX(event_at) AS ended,
       COUNT(*) AS events
  FROM sessioned
 GROUP BY user_id, session_id;

-- keep the most recent row per email
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email
                              ORDER BY updated_at DESC, id DESC) AS rn
    FROM users
)
SELECT * FROM ranked WHERE rn = 1;

-- and to actually DELETE the duplicates:
DELETE u FROM users u
  JOIN (
    SELECT id FROM (
      SELECT id, ROW_NUMBER() OVER (PARTITION BY email
                                    ORDER BY updated_at DESC, id DESC) rn
        FROM users
    ) x WHERE rn > 1
  ) d ON d.id = u.id;

-- Postgres only
SELECT DISTINCT ON (email) *
  FROM users
 ORDER BY email, updated_at DESC;
--   the ORDER BY must lead with the DISTINCT ON columns.

-- this window wants rows ordered by (user_id, created_at)
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)

-- so this index removes the sort entirely
CREATE INDEX idx ON events (user_id, created_at);

-- repetitive and easy to get subtly wrong
SELECT d, amt,
  SUM(amt)        OVER (PARTITION BY acct ORDER BY d ROWS UNBOUNDED PRECEDING),
  AVG(amt)        OVER (PARTITION BY acct ORDER BY d ROWS UNBOUNDED PRECEDING),
  ROW_NUMBER()    OVER (PARTITION BY acct ORDER BY d)
FROM ledger;

-- named once, reused
SELECT d, amt,
  SUM(amt)     OVER w_run,
  AVG(amt)     OVER w_run,
  ROW_NUMBER() OVER w_ord
FROM ledger
WINDOW
  w_ord AS (PARTITION BY acct ORDER BY d),
  w_run AS (w_ord ROWS UNBOUNDED PRECEDING);   -- extends w_ord