LAG、LEADと時系列パターン

SELECT id, user_id, created_at, total,
       LAG(total) OVER (
         PARTITION BY user_id ORDER BY created_at
       ) AS prev_total
FROM orders;

SELECT day, revenue,
       revenue - LAG(revenue) OVER (ORDER BY day) AS delta
FROM daily_revenue
ORDER BY day;

SELECT month, revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year_revenue,
       revenue / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) - 1 AS yoy
FROM monthly_revenue;

WITH d AS (
  SELECT day, LAG(day) OVER (ORDER BY day) AS prev_day FROM daily_signups
)
SELECT day, prev_day, day - prev_day AS gap_days
FROM d
WHERE day - prev_day > 1;

WITH gaps AS (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
                 > INTERVAL '30 min'
              THEN 1 ELSE 0 END AS new_session
  FROM events
)
SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps;

SELECT id, total,
       FIRST_VALUE(total) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order,
       LAST_VALUE(total)  OVER (PARTITION BY user_id ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_order
FROM orders;
-- LAST_VALUE needs the explicit frame, otherwise default frame ends at current row.

SELECT id,
       NTH_VALUE(total, 2) OVER (
         PARTITION BY user_id ORDER BY total DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_largest
FROM orders;

SELECT day, revenue,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM daily_revenue;
-- delta on first day = revenue itself (vs NULL)