09 — Window Functions

-- Running total per customer, ordered by date.
-- GROUP BY would collapse each customer into one row; the window function keeps them all.
-- Default frame (with ORDER BY): RANGE UNBOUNDED PRECEDING TO CURRENT ROW → running accumulation.
SELECT
  customer_id,
  ordered_on,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_on) AS running_total
FROM orders;

function(args) OVER (
  [PARTITION BY ...]     -- groups; default = whole result set is one partition
  [ORDER BY ...]         -- order within each partition; default = unspecified (no order guarantee)
  [frame_clause]         -- which rows to include; default depends on function + presence of ORDER BY
)

-- Rank orders by amount within each customer.
-- For amounts [450, 100, 100, 50] within a customer:
--   ROW_NUMBER: 1, 2, 3, 4  (ties get distinct arbitrary numbers — non-deterministic without tie-breaker)
--   RANK:       1, 2, 2, 4  (skips 3 after the tie)
--   DENSE_RANK: 1, 2, 2, 3  (no skip — next distinct value gets next rank)
SELECT
  customer_id,
  id AS order_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn,
  RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS drnk
FROM orders;

-- Find the top 3 orders per customer.
-- ROW_NUMBER (not RANK/DENSE_RANK) is the right choice for "exactly N":
-- it never produces ties, so rn <= 3 returns exactly 3 rows per partition
-- (or fewer if the partition is smaller).
-- Wrap in a CTE because window functions can't appear in WHERE (computed after WHERE).
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS rn
    -- ↑ added 'id' as tie-breaker for deterministic ordering on equal amounts
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

-- Each order alongside its customer's total and the overall average.
-- Without ORDER BY in OVER, the frame is the ENTIRE partition → every row gets the same value.
-- SUM OVER (PARTITION BY customer_id) → customer's total (same for all their orders).
-- AVG OVER () → grand average across all rows (empty OVER = whole result set is one partition).
SELECT
  id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,  -- same per customer
  AVG(amount) OVER ()                       AS overall_avg          -- same for all rows
FROM orders;

{ROWS | RANGE | GROUPS} BETWEEN <start> AND <end>
  <start>/<end> := UNBOUNDED PRECEDING | <n> PRECEDING | CURRENT ROW | <n> FOLLOWING | UNBOUNDED FOLLOWING

-- Running total (sum from partition start to current row, strict row-count).
-- ROWS: exactly one row per step. Use this for row-by-row accumulation.
SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_on
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Running total EXCLUDING the current row (sum of all rows before this one).
SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_on
  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

-- Moving average over a 3-row window (1 before + current + 1 after).
AVG(amount) OVER (ORDER BY ordered_on
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Sum of the whole partition (same for every row — no ORDER BY, frame = full partition).
SUM(amount) OVER (PARTITION BY customer_id)

-- Cumulative sum up to and including all peers (default RANGE behavior).
-- RANGE CURRENT ROW includes all rows with the same ORDER BY value as the current row.
SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_on)

-- Day-over-day change in amount.
-- LAG(amount, 1, 0): previous row's amount; default 0 if no previous row (first row in partition).
SELECT
  ordered_on,
  amount,
  amount - LAG(amount) OVER (ORDER BY ordered_on) AS daily_delta,
  amount - LAG(amount, 1, 0) OVER (ORDER BY ordered_on) AS delta_with_default
FROM orders;

-- Compare each order to the customer's first order.
-- FIRST_VALUE with ASC order → the earliest order's amount.
SELECT
  customer_id, ordered_on, amount,
  amount - FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY ordered_on) AS diff_from_first
FROM orders;

-- 7-day moving average of daily revenue.
-- The frame is defined by ROWS BETWEEN 6 PRECEDING AND CURRENT ROW:
--   current row + 6 preceding rows = 7 rows total (the 7-day window).
-- ROWS (not RANGE) ensures exactly 7 physical rows, regardless of date gaps.
--   Note: if dates are missing (no orders on some days), ROWS counts rows, not calendar days.
--   For true calendar-day windows, use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW.
SELECT
  ordered_on,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY ordered_on
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW    -- 7-row sliding window
  ) AS moving_avg_7d,
  -- True calendar 7-day window using RANGE with interval (PostgreSQL).
  -- This handles gaps: if a date is missing, RANGE still looks back 6 calendar days.
  AVG(daily_revenue) OVER (
    ORDER BY ordered_on
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d_calendar
FROM (
  -- Stage 1: aggregate to daily revenue (window functions operate on rows, not pre-aggregated).
  SELECT DATE(ordered_on) AS ordered_on, SUM(amount) AS daily_revenue
  FROM orders
  GROUP BY DATE(ordered_on)
) daily
ORDER BY ordered_on;

-- Top 3 highest-value orders per customer, with rank and customer's total for context.
-- ROW_NUMBER ensures exactly 3 per customer (no ties, no extra rows).
-- The outer WHERE filters on rn (window functions computed after WHERE, so need a CTE/subquery).
SELECT
  customer_id,
  customer_name,
  order_id,
  amount,
  rn AS rank_in_customer,
  customer_total,
  customer_total - amount AS rest_of_orders_total
FROM (
  SELECT
    o.customer_id,
    c.name AS customer_name,
    o.id AS order_id,
    o.amount,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.amount DESC, o.id) AS rn,
    -- Tie-breaker: o.id ensures deterministic ranking when amounts are equal.
    SUM(o.amount) OVER (PARTITION BY o.customer_id) AS customer_total
    -- ↑ customer's lifetime total (no ORDER BY → frame = full partition → same for all their rows)
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
) ranked
WHERE rn <= 3
ORDER BY customer_id, rn;

-- Intent: get each customer's most recent order amount.
-- Bug: the default frame (with ORDER BY) is RANGE UNBOUNDED PRECEDING TO CURRENT ROW.
-- The frame ENDS at the current row → LAST_VALUE = the current row's value, not the partition's last.
SELECT
  customer_id,
  ordered_on,
  amount,
  LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY ordered_on) AS last_amount
FROM orders;
-- 'last_amount' equals 'amount' for every row — NOT what was intended.

-- Option 1: extend the frame to the partition end.
LAST_VALUE(amount) OVER (
  PARTITION BY customer_id ORDER BY ordered_on
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- frame = whole partition
)

-- Option 2 (cleaner): FIRST_VALUE with descending order.
-- The first row in DESC order is the most recent → no frame trap.
FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY ordered_on DESC)

-- WINDOW clause: define the partition/order once, reference by name.
-- Reduces repetition and makes the partitioning strategy explicit.
SELECT
  customer_id, ordered_on, amount,
  ROW_NUMBER() OVER cust_window AS rn,
  SUM(amount)   OVER cust_window AS running_total,    -- same OVER as rn
  AVG(amount)   OVER cust_window AS running_avg        -- same OVER as rn
FROM orders
WINDOW cust_window AS (PARTITION BY customer_id ORDER BY ordered_on);

SELECT
  customer_id,
  ordered_on,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_on
  ) AS running_total
FROM orders;