Advanced SQL

Advanced Window Patterns

Layered window queries solve deterministic top-N, running and moving measures, session boundaries, and cohort retention by first establishing the correct row grain and total order.

Top-N-per-group ranks inside each group and filters outside.
Running and moving windows are different contracts.
Sessionization marks gaps, then cumulatively numbers starts.
Retention needs one explicit grain per cohort and period.
Ties require a business rule.
Window pipelines often require multiple query levels.

Deterministic top three orders per customer

WITH order_totals AS (
  SELECT o.customer_id, o.order_id, o.placed_at,
         SUM(i.quantity * i.unit_price) AS order_total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.status = 'PAID'
  GROUP BY o.customer_id, o.order_id, o.placed_at
), ranked AS (
  SELECT order_totals.*, ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_total DESC, placed_at DESC, order_id
  ) AS rn
  FROM order_totals
)
SELECT customer_id, order_id, order_total, placed_at
FROM ranked WHERE rn <= 3
ORDER BY customer_id, rn;

Seven-order moving average

WITH order_totals AS (
  SELECT o.customer_id, o.order_id, o.placed_at,
         SUM(i.quantity * i.unit_price) AS order_total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.status = 'PAID'
  GROUP BY o.customer_id, o.order_id, o.placed_at
)
SELECT customer_id, order_id, placed_at, order_total,
       AVG(order_total) OVER (
         PARTITION BY customer_id
         ORDER BY placed_at, order_id
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS last_7_order_average
FROM order_totals
ORDER BY customer_id, placed_at, order_id;

First establish one row per paid order. For each row, the frame then contains that order and at most six predecessors; early rows average the smaller frame that actually exists.

Sessionize paid-order events

WITH order_events AS (
  SELECT o.customer_id, o.order_id AS event_id, o.placed_at AS occurred_at
  FROM orders AS o
  WHERE o.status = 'PAID'
), previous AS (
  SELECT order_events.*, LAG(occurred_at) OVER (
    PARTITION BY customer_id ORDER BY occurred_at, event_id
  ) AS previous_at
  FROM order_events
), boundaries AS (
  SELECT previous.*, CASE
    WHEN previous_at IS NULL
      OR occurred_at > previous_at + INTERVAL '30 minutes' THEN 1 ELSE 0
  END AS session_start
  FROM previous
)
SELECT boundaries.*, SUM(session_start) OVER (
  PARTITION BY customer_id ORDER BY occurred_at, event_id
  ROWS UNBOUNDED PRECEDING
) AS session_number
FROM boundaries;

Monthly cohort retention

WITH customer_months AS (
  SELECT DISTINCT o.customer_id,
         EXTRACT(YEAR FROM o.placed_at) AS activity_year,
         EXTRACT(MONTH FROM o.placed_at) AS activity_month
  FROM orders AS o
  WHERE o.status = 'PAID'
), first_month AS (
  SELECT customer_id, MIN(activity_year * 12 + activity_month) AS cohort_month
  FROM customer_months
  GROUP BY customer_id
), retention AS (
  SELECT f.cohort_month,
         (m.activity_year * 12 + m.activity_month) - f.cohort_month AS month_number,
         COUNT(*) AS retained_customers
  FROM customer_months AS m
  JOIN first_month AS f ON f.customer_id = m.customer_id
  GROUP BY f.cohort_month,
           (m.activity_year * 12 + m.activity_month) - f.cohort_month
)
SELECT cohort_month, month_number, retained_customers
FROM retention
ORDER BY cohort_month, month_number;

`customer_months` removes repeated orders in a month; `first_month` fixes one cohort per customer; the final grouping counts each customer once in each elapsed month.

Choosing a pattern
Question	Window contract	Precondition
Lifetime spend	Unbounded preceding to current row	One row per intended transaction
Last seven orders	Six rows preceding to current row	Deterministic order
Top three including ties	`RANK() <= 3`	Tie behavior accepted
Retention by month	Cohort plus activity-period grouping	Customer-period deduplicated