Gap and Island detection di SQL (consecutive sequences)

Pertanyaan analytics yang common: “berapa hari user aktif berturut-turut?”, “kapan periode terpanjang tanpa downtime?”, “berapa range tanggal yang berturut-turut ada data?” Jawabannya: gap-and-island pattern.

Setup data

CREATE TABLE user_login (
  user_id INT,
  login_date DATE,
  PRIMARY KEY (user_id, login_date)
);

INSERT INTO user_login VALUES
  (1, '2026-05-01'), (1, '2026-05-02'), (1, '2026-05-03'),  -- streak 1: 3 days
  (1, '2026-05-08'), (1, '2026-05-09'),                       -- streak 2: 2 days
  (1, '2026-05-15'),                                          -- streak 3: 1 day
  (1, '2026-05-20'), (1, '2026-05-21'), (1, '2026-05-22'), (1, '2026-05-23'); -- streak 4: 4 days

Query: temukan semua “islands” (consecutive streaks)

WITH ranked AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days')::INTERVAL AS grp
  FROM user_login
),
streaks AS (
  SELECT
    user_id,
    grp,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*) AS streak_length
  FROM ranked
  GROUP BY user_id, grp
)
SELECT
  user_id,
  streak_start,
  streak_end,
  streak_length
FROM streaks
WHERE user_id = 1
ORDER BY streak_start;

Output

 user_id | streak_start | streak_end | streak_length
---------+--------------+------------+---------------
       1 | 2026-05-01   | 2026-05-03 |             3
       1 | 2026-05-08   | 2026-05-09 |             2
       1 | 2026-05-15   | 2026-05-15 |             1
       1 | 2026-05-20   | 2026-05-23 |             4

Cara kerja

Trick: kalau tanggal konsekutif, tanggal - rownumber menghasilkan nilai konstan untuk semua row dalam satu streak. Group by hasil itu = group by streak.

login_date	rn	tanggal - rn
2026-05-01	1	2026-04-30
2026-05-02	2	2026-04-30
2026-05-03	3	2026-04-30
2026-05-08	4	2026-05-04
2026-05-09	5	2026-05-04
2026-05-15	6	2026-05-09
2026-05-20	7	2026-05-13
2026-05-21	8	2026-05-13
2026-05-22	9	2026-05-13
2026-05-23	10	2026-05-13

Use cases real

Streak harian user aktif

Sudah di atas.

Periode tanpa downtime

-- monitoring_status: hari yang status='UP'
WITH ranked AS (
  SELECT
    ts::DATE AS day,
    ROW_NUMBER() OVER (ORDER BY ts::DATE) AS rn
  FROM monitoring_status
  WHERE status = 'UP'
),
periods AS (
  SELECT
    MIN(day) AS uptime_start,
    MAX(day) AS uptime_end,
    COUNT(*) AS uptime_days
  FROM ranked
  GROUP BY day - (rn || ' days')::INTERVAL
)
SELECT * FROM periods ORDER BY uptime_days DESC LIMIT 5;

Top 5 periode uptime terpanjang.

Periode kerja tanpa libur

-- attendance: catatan kehadiran
WITH ranked AS (
  SELECT
    employee_id,
    date,
    ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY date) AS rn
  FROM attendance
)
SELECT
  employee_id,
  MIN(date) AS period_start,
  MAX(date) AS period_end,
  COUNT(*) AS consecutive_days
FROM ranked
GROUP BY employee_id, date - (rn || ' days')::INTERVAL
HAVING COUNT(*) > 60  -- yang > 60 hari berturut-turut
ORDER BY consecutive_days DESC;

Identifikasi karyawan yang kerja > 60 hari tanpa hari libur (untuk HR follow-up).

Catatan

• Date interval works for daily granularity. Untuk hourly/minutely, ganti 'days' ke 'hours' atau 'minutes'.
• PostgreSQL syntax di atas. MySQL 8+ juga support window function, syntax mirip.
• Performance: untuk dataset besar (>10M row), tambahkan index di kolom partition + order. Window function tetap O(N log N).

Variasi: detect gaps (kebalikan dari islands)

WITH consecutive_pairs AS (
  SELECT
    user_id,
    login_date AS day,
    LEAD(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS next_day
  FROM user_login
)
SELECT
  user_id,
  day + INTERVAL '1 day' AS gap_start,
  next_day - INTERVAL '1 day' AS gap_end,
  (next_day - day - 1) AS gap_days
FROM consecutive_pairs
WHERE next_day - day > 1
ORDER BY gap_days DESC;

Cari periode di mana user TIDAK login (gap).

Pattern gap-and-island adalah salah satu “advanced SQL technique” yang sering muncul di whiteboard interview senior backend. Worth dikuasai.