SQL Interview Prep · Pelajaran

Islands dengan Perubahan Tanggal dan Status

Kelompokkan periode dengan status yang sama secara berurutan, pertanyaan umum tentang status langganan.

Islands dengan Perubahan Tanggal dan Status adalah pelajaran SQL Interview Prep gratis di CoddyKit. Ini adalah pelajaran 4 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar SQL Interview Prep, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus SQL Interview Prep mencakup 4 pelajaran total.

Pulau yang Ditentukan oleh Nilai yang Berubah

Varian celah-dan-pulau yang paling relevan bagi bisnis mengelompokkan baris berurutan yang memiliki status sama, sehingga catatan peristiwa yang berisik berubah menjadi periode keadaan yang rapi. Soal klasiknya: "Diberikan catatan peristiwa langganan, kembalikan satu baris untuk setiap periode berkelanjutan ketika pengguna berada dalam setiap status."

Di sini, keterurutan tidak berarti "nilai berbeda sebesar 1". Keterurutan berarti status tidak berubah dari baris sebelumnya. Pulau baru dimulai tepat saat status berubah. Di sinilah teknik berbasis LAG lebih unggul daripada trik nomor baris murni.

Contoh Data Langganan

Perhatikan tabel sub_events untuk satu pengguna, yang diurutkan berdasarkan tanggal:

2026-01-01 aktif
2026-02-01 aktif
2026-03-01 dijeda
2026-04-01 aktif
2026-05-01 aktif

Hasil yang diinginkan adalah tiga periode status: aktif Jan-Feb, dijeda Mar, aktif Apr-Mei. Perhatikan bahwa dua rentang aktif tersebut merupakan pulau yang terpisah karena periode dijeda menyela keduanya. Statusnya sama, tetapi karena tidak berurutan, pulau-pulaunya berbeda.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

Tandai Saat Status Berubah

Gunakan LAG untuk membandingkan status setiap baris dengan status baris sebelumnya. Ketika keduanya berbeda (atau nilai sebelumnya adalah NULL untuk baris pertama), pulau baru dimulai. Hasilkan angka 1 untuk perubahan dan 0 untuk kondisi lainnya.

Urutkan secara ketat berdasarkan tanggal dalam setiap pengguna. Untuk data kita, penanda perubahannya adalah 1,0,1,1,0, yang menandai tiga batas periode.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Mengubah Jumlah Berjalan Menjadi Kunci Periode

Seperti sebelumnya, jumlah berjalan dari penanda perubahan menghasilkan kunci grup yang konstan dalam setiap periode status: 1,1,2,3,3 untuk baris-baris kita. Setiap kunci yang berbeda merupakan satu periode berkelanjutan.

Trik selisih nomor baris tidak akan berhasil di sini karena status bukan angka yang bertambah 1; pola LAG ditambah jumlah berjalan adalah alat yang tepat ketika keterurutan berarti "nilai tidak berubah".

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Meringkas Menjadi Periode Status

Sekarang lakukan GROUP BY pada user_id, status, dan kunci jumlah berjalan untuk melaporkan rentang setiap periode. Menyertakan status dalam GROUP BY aman karena status tersebut konstan dalam satu periode, dan hal itu memungkinkan Anda memilihnya tanpa fungsi agregat.

Hasilnya tepat tiga baris: aktif dari 01-01 hingga 02-01, dijeda dari 03-01 hingga 03-01, aktif dari 04-01 hingga 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

Dari Peristiwa ke Interval Setengah Terbuka

Poin wawancara yang sering terlewat: tanggal peristiwa menandai kapan suatu status dimulai, dan periode benar-benar berakhir saat status berikutnya dimulai, bukan pada tanggal peristiwa terakhir dengan status yang sama. Akhir periode yang tepat sering kali adalah awal periode berikutnya, yang dimodelkan sebagai interval setengah terbuka [awal, awal_berikutnya).

Hitung awal periode berikutnya dengan LEAD pada periode yang telah digabungkan, dan biarkan periode terakhir tidak memiliki batas akhir (NULL atau 'saat ini').

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Menangani Status Berulang Berturut-turut

Bagaimana jika catatan memiliki baris redundan seperti aktif, aktif, aktif tanpa perubahan di antaranya? Penanda perubahan bernilai 0 untuk pengulangan tersebut, sehingga jumlah kumulatif secara otomatis mempertahankannya dalam satu kelompok. Inilah perilaku yang diinginkan: status identik yang berurutan digabung menjadi satu periode.

Penghapusan duplikasi berulang secara alami ini merupakan keunggulan utama metode penanda perubahan dan layak Anda sampaikan kepada pewawancara.

Saat Jeda Waktu Harus Mengakhiri Periode

Terkadang "status yang sama" saja tidak cukup; jeda waktu yang panjang juga harus mengakhiri periode meskipun statusnya identik. Misalnya, status aktif pada Januari lalu kembali aktif setelah enam bulan tanpa aktivitas mungkin dihitung sebagai dua periode.

Perluas penanda perubahan dengan kondisi kedua: mulai kelompok baru ketika status berubah atau waktu sejak peristiwa sebelumnya melebihi ambang batas. Dengan demikian, kedua aturan kedekatan ini dapat dipadukan dengan rapi.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Menghitung Peralihan Status

Pertanyaan lanjutan yang wajar: "Berapa kali pengguna ini beralih status?" Jawabannya cukup dengan menghitung penanda perubahan lalu mengurangkan penanda pertama (yang menandai status awal, bukan suatu peralihan).

Dengan kata lain, jumlah periode dikurangi 1. Kunci jumlah kumulatif sudah menyandikan informasi ini, sehingga jawabannya dapat diperoleh dari mekanisme yang sama dengan yang Anda bangun untuk menghitung periode.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Mengapa Ini Lebih Baik daripada Gabungan Mandiri

Solusi gabungan mandiri untuk menghitung periode status perlu memasangkan setiap baris dengan tetangganya, mendeteksi perubahan, lalu menyatukan batas-batasnya—sebuah proses multilangkah yang rawan kesalahan dan kesulitan menangani tiga periode atau lebih.

Alur LAG-penanda-jumlah kumulatif-pengelompokan menangani berapa pun jumlah periode dalam satu lintasan tanpa gabungan. Menjelaskan perbedaan ini—satu lintasan linear dibandingkan gabungan mandiri kuadratik—tepat merupakan penalaran tingkat senior yang dihargai pewawancara.

Templat yang Dapat Digunakan Kembali

Hafalkan templat empat klausa ini; templat ini menyelesaikan seluruh keluarga kelompok status dengan hanya mengubah pengujian kedekatan di CASE:

penanda: CASE dengan LAG untuk mendeteksi kelompok baru.
kunci: SUM kumulatif dari penanda, dipartisi dan diurutkan.
penggabungan: GROUP BY kolom partisi, status, dan kunci.
interval (opsional): LEAD untuk akhir periode setengah terbuka.

Kerangka yang sama berlaku untuk bilangan bulat berurutan, tanggal, dan status; hanya kondisi CASE yang berubah.

Pemeriksaan Singkat

Pastikan Anda memahami aturan pengelompokan kelompok status.

Rangkuman: Kelompok Status dan Tanggal

Sekarang Anda dapat menyelesaikan varian celah dan kelompok yang paling lengkap:

Kedekatan = status tidak berubah dari baris sebelumnya; penanda perubahan dengan LAG.
Jadikan penanda perubahan sebagai jumlah kumulatif untuk membentuk kunci kelompok per periode.
Gabungkan dengan GROUP BY user_id, status, key untuk mendapatkan rentang periode.
Gunakan LEAD untuk akhir interval setengah terbuka; perluas penanda agar periode terputus saat terjadi jeda waktu yang panjang.
Baris identik yang berulang digabung secara otomatis; jumlah peralihan dapat diperoleh dari penanda yang sama.
Satu templat yang dapat digunakan kembali mencakup bilangan bulat, tanggal, dan status; hanya CASE yang berubah.

Dengan demikian, kursus celah dan kelompok ini selesai—indikator andal tingkat senior dalam wawancara SQL.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Islands dengan Perubahan Tanggal dan Status” gratis?

Ya — teks lengkap “Islands dengan Perubahan Tanggal dan Status” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus SQL Interview Prep, upgrade ke CoddyKit PRO. Kursus SQL Interview Prep mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Islands dengan Perubahan Tanggal dan Status”?

Kelompokkan periode dengan status yang sama secara berurutan, pertanyaan umum tentang status langganan. Kamu berlatih SQL Interview Prep dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai SQL Interview Prep?

Tidak diperlukan pengalaman sebelumnya. SQL Interview Prep di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 4 dari 4.

Berapa lama pelajaran “Islands dengan Perubahan Tanggal dan Status” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran SQL Interview Prep ini?

Ya. Setiap pelajaran SQL Interview Prep menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

Mengenali Masalah Gaps-and-Islands
Trik Selisih Nomor Baris
Menemukan Celah dalam Deret
Islands dengan Perubahan Tanggal dan Status

← Kembali ke SQL Interview Prep