Window 関数

window_function() OVER (
  PARTITION BY ...   -- グループ分け（GROUP BY 相当）
  ORDER BY ...       -- 順序
  ROWS / RANGE ...   -- 範囲指定
)

-- 各ユーザの最新投稿だけ（DISTINCT ON の代替）
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM posts
) t
WHERE rn = 1;

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY views DESC) AS rn
  FROM posts
) t
WHERE rn <= 3;

SELECT
  name, score,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scoreboard;

-- 前日からの増減
SELECT
  date,
  views,
  LAG(views) OVER (ORDER BY date) AS prev,
  views - LAG(views) OVER (ORDER BY date) AS diff
FROM daily_stats;

LAG(views, 1, 0) OVER (...)    -- 1 行前、無ければ 0
LEAD(views, 7, 0) OVER (...)   -- 7 行後

SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS cumulative
FROM revenue;

SUM(amount) OVER (PARTITION BY category ORDER BY date)

-- 過去 7 日（自分含む）の平均
SELECT
  date, sales,
  AVG(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM daily;

-- 過去 7 日（同日複数行も含む）
SUM(sales) OVER (
  ORDER BY date
  RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
)

SELECT
  user_id, post_id, created_at,
  FIRST_VALUE(post_id) OVER (PARTITION BY user_id ORDER BY created_at) AS first_post,
  LAST_VALUE(post_id) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_post
FROM posts;

SELECT
  user_id, total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_stats;
-- 上位 25% / 50% / 75% / 100% に分割

SELECT
  user_id, created_at,
  ROW_NUMBER() OVER w AS rn,
  RANK()       OVER w AS rank,
  LAG(views)   OVER w AS prev_views
FROM posts
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM events
)
SELECT * FROM ranked WHERE rn = 1;

-- email 重複の重複側だけを削除
DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) t WHERE rn > 1
);

-- 連続ログイン日数を求める常套手段
SELECT user_id, COUNT(*) AS streak
FROM (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::INT AS grp
  FROM logins
) t
GROUP BY user_id, grp
ORDER BY streak DESC;

SELECT
  user_id, action_at,
  action_at - LAG(action_at) OVER (PARTITION BY user_id ORDER BY action_at) AS gap
FROM actions;

SELECT
  category, name, sales,
  sales * 1.0 / SUM(sales) OVER (PARTITION BY category) AS pct_in_cat
FROM products;

WITH ranked AS (
  SELECT category, sum(amount) AS total,
    ROW_NUMBER() OVER (ORDER BY sum(amount) DESC) AS rn
  FROM orders
  GROUP BY category
)
SELECT
  CASE WHEN rn <= 3 THEN category ELSE 'Other' END AS category,
  sum(total) AS total
FROM ranked
GROUP BY 1;

SELECT
  category,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS median
FROM products
GROUP BY category;