Gap and Island Analysis di SQL

Gap and island analysis adalah teknik SQL buat ngelompokkin baris yang berurutan jadi grup (island) dan nemuin celah di antaranya (gap). Kuncinya satu trik selisih antara nilai data dan nomor urut baris.

Pertanyaan kayak "berapa hari pelanggan login berturut-turut?" atau "tanggal transaksi mana yang hilang?" itu susah dijawab pakai GROUP BY biasa. Gap and island yang nyelesaiin. Di bawah ini ada penjelasan triknya, query siap pakai, dan contoh streak login data Indonesia.

Apa itu gap and island analysis?

Gap and island analysis adalah cara ngelompokkin baris yang nilainya berurutan (island) sekaligus ngenalin bagian yang bolong (gap). Namanya dari gambaran deret data: bagian yang nyambung diibaratkan pulau, celah kosong diibaratkan laut di antaranya. Teknik ini masuk kategori window function dan sering muncul di soal wawancara data analyst.

Gimana trik dasarnya bekerja?

Intinya satu observasi sederhana. Buat baris yang berurutan, kalau kamu kurangi nilai datanya dengan nomor urut baris, hasilnya konstan.

Contoh, tanggal 1, 2, 3 Juli punya nomor urut 1, 2, 3. Selisihnya sama semua. Begitu ada lompatan ke tanggal 6, selisihnya berubah, dan grup baru dimulai. Selisih inilah yang kita pakai sebagai kunci GROUP BY.

login_date - ROW_NUMBER() OVER (ORDER BY login_date)

Gimana cara cari island (deret berturut-turut)?

Pakai CTE buat bikin kunci grup dulu, baru di-GROUP BY:

WITH bertanda AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ))::int AS grup_kunci
  FROM user_logins
)
SELECT
  user_id,
  MIN(login_date) AS mulai,
  MAX(login_date) AS selesai,
  COUNT(*)        AS panjang_streak
FROM bertanda
GROUP BY user_id, grup_kunci
ORDER BY mulai;

PARTITION BY user_id bikin perhitungan jalan per pelanggan, jadi streak satu orang nggak nyampur sama yang lain.

Contoh kasus: streak login pelanggan toko_berkah

Data login harian pelanggan 101 di dataset toko_berkah:

login_date
2026-07-01
2026-07-02
2026-07-03
2026-07-06
2026-07-07
2026-07-10

Setelah query island dijalanin, hasilnya tiga streak:

Mulai	Selesai	Panjang
2026-07-01	2026-07-03	3 hari
2026-07-06	2026-07-07	2 hari
2026-07-10	2026-07-10	1 hari

Streak terpanjang pelanggan ini 3 hari. Dari 6 hari login, ada 2 gap: tanggal 4-5 dan tanggal 8-9. Buat program loyalitas, angka streak kayak gini bisa jadi dasar kasih reward ke pelanggan yang aktif berturut-turut. Mau kelompokkin data per rentang waktu lebih dulu? Baca SQL date bucketing.

Gimana cara nemuin gap (data yang hilang)?

Buat nemu celah, bandingin tiap tanggal sama tanggal berikutnya pakai LEAD. Kalau selisihnya lebih dari 1 hari, di situ ada gap:

SELECT
  login_date + 1                         AS gap_mulai,
  LEAD(login_date) OVER (ORDER BY login_date) - 1 AS gap_selesai
FROM user_logins
WHERE LEAD(login_date) OVER (ORDER BY login_date) - login_date > 1;

Hasilnya nunjukin rentang tanggal yang bolong, misalnya 2026-07-04 sampai 2026-07-05.

Kesalahan umum saat gap and island

• Lupa PARTITION BY - kalau datanya multi-pelanggan, streak antar orang bakal nyampur. Selalu partisi per entitas.
• Salah casting tanggal - di PostgreSQL, selisih DATE ngasih integer, tapi ROW_NUMBER ngasih bigint. Samakan tipe biar aman.
• Ada duplikat tanggal - login dobel di hari sama bikin trik ROW_NUMBER meleset. Pakai DISTINCT atau agregasi dulu.
• Data belum urut - ORDER BY di window wajib bener, karena seluruh trik gantung ke urutan.

FAQ

Pertanyaan yang sering muncul soal gap and island ada di bagian bawah ini.

Penutup

Gap and island kelihatan rumit, padahal intinya satu trik: selisih nilai dikurangi nomor urut jadi kunci grup. Dari situ kamu bisa hitung streak, periode aktif, sampai deteksi data yang hilang.

Mau latihan window function SQL langsung di browser? Kulik latihan di NgulikSQL. Buat dalemin ROW_NUMBER dan teman-temannya, cek dokumentasi resmi PostgreSQL Window Functions, atau lanjut baca pandas read_csv kalau kamu mau olah data yang sama di Python.