連続した期間・グループを検出する (Gaps and Islands)

-- ユーザーのログイン日から連続日数を計算
SELECT
  user_id,
  MIN(login_date) AS start_date,
  MAX(login_date) AS end_date,
  COUNT(*) AS streak_days
FROM (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ))::INT * INTERVAL '1 day' AS grp
  FROM logins
) t
GROUP BY user_id, grp
ORDER BY user_id, start_date;

-- 同じステータスが続く期間をグルーピング
SELECT
  machine_id, status,
  MIN(ts) AS start_ts, MAX(ts) AS end_ts
FROM (
  SELECT
    machine_id, status, ts,
    SUM(is_change) OVER (
      PARTITION BY machine_id ORDER BY ts
    ) AS grp
  FROM (
    SELECT
      machine_id, status, ts,
      CASE WHEN status <> LAG(status) OVER (
        PARTITION BY machine_id ORDER BY ts
      ) THEN 1 ELSE 0 END AS is_change
    FROM machine_status
  ) t
) u
GROUP BY machine_id, status, grp;

SELECT
  id + 1 AS gap_start,
  next_id - 1 AS gap_end
FROM (
  SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
  FROM tickets
) t
WHERE next_id - id > 1;