SQL Interview Prep · Pelajaran

Kueri Churn dan Kembalinya Pengguna

Mengidentifikasi pengguna yang pergi dan pengguna yang kembali setelah jeda.

Kueri Churn dan Kembalinya Pengguna 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.

Sisi Lain Retensi

Jika retensi mengukur siapa yang bertahan, penghentian penggunaan mengukur siapa yang pergi, dan pengaktifan kembali mengukur siapa yang kembali. Pewawancara memasangkan metrik-metrik ini dengan retensi karena metrik tersebut menunjukkan apakah Anda dapat memahami ketiadaan aktivitas, yang lebih sulit daripada menghitung kehadiran.

Trik yang terus muncul: Anda tidak dapat menyaring baris yang tidak ada. Kueri penghentian penggunaan pada dasarnya mencari celah antara aktivitas terakhir pengguna dan saat ini (atau antara aktivitasnya yang satu dan berikutnya).

Mendefinisikan Penghentian Penggunaan dengan Tepat

Istilah "berhenti" tidak bermakna tanpa jendela waktu. Definisi yang umum: pengguna disebut berhenti jika tidak memiliki aktivitas dalam 30 hari terakhir. Batas 30 hari tanpa aktivitas adalah pilihan bisnis yang harus Anda pastikan.

Untuk produk berlangganan, penghentian penggunaan dapat berarti langganan dibatalkan atau kedaluwarsa, yaitu perubahan status, bukan celah aktivitas. Pastikan model mana yang berlaku sebelum menulis SQL.

Aktivitas Terakhir Setiap Pengguna

Dasar penghitungan penghentian penggunaan berdasarkan celah aktivitas adalah peristiwa terbaru setiap pengguna. Kelompokkan berdasarkan pengguna dengan GROUP BY, lalu ambil MAX tanggal peristiwa.

Nilai tunggal ini, jika dibandingkan dengan hari ini, memberi tahu berapa lama pengguna tidak aktif. Semua proses berikutnya merupakan perbandingan dengan tanggal terakhir pengguna terlihat.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

Kueri Pengguna yang Berhenti

Seorang pengguna dianggap berhenti jika aktivitas terakhirnya terjadi lebih dari 30 hari lalu. Bandingkan last_active dengan CURRENT_DATE - 30. Siapa pun yang peristiwa terbarunya lebih awal daripada batas tersebut sudah tidak aktif.

Perhatikan bahwa prosesnya dilakukan setelah agregasi: kurangi data menjadi satu baris per pengguna, lalu uji celahnya. Menyaring peristiwa mentah berdasarkan tanggal hanya akan memberi tahu siapa yang tidak aktif dalam suatu jendela, bukan siapa yang secara keseluruhan telah berhenti.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Menghitung Tingkat Penghentian

Tingkat penghentian adalah jumlah pengguna yang berhenti dibagi basis yang relevan, sering kali pengguna yang aktif pada awal periode. Gunakan agregasi bersyarat untuk menghitung pengguna yang berhenti dan jumlah total dalam satu pemindaian, lalu lakukan pembagian dengan hati-hati menggunakan 100.0 dan NULLIF.

Jelaskan penyebutnya secara tegas dalam wawancara: penghentian di antara seluruh pengguna sepanjang waktu dan penghentian di antara pengguna yang sebelumnya aktif merupakan metrik yang berbeda.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Penghentian Antarperiode dengan Logika Himpunan

Cara lain untuk merumuskannya adalah: siapa yang aktif bulan lalu tetapi tidak bulan ini? Ini merupakan selisih himpunan. Buat himpunan pengguna aktif bulan lalu dan himpunan pengguna aktif bulan ini, lalu cari anggota himpunan pertama yang tidak ada dalam himpunan kedua.

Anda dapat menyatakannya dengan EXCEPT, operasi anti-JOIN menggunakan LEFT JOIN / IS NULL, atau NOT EXISTS. Operasi anti-JOIN adalah yang paling portabel dan paling sering ingin dilihat oleh pewawancara.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

Bentuk Anti-Gabungan

Kueri pengguna yang berhenti pada periode ini dalam bentuk anti-gabungan: terapkan LEFT JOIN pada pengguna aktif bulan ini ke pengguna aktif bulan lalu, lalu pertahankan baris ketika hasil pencocokannya NULL. Pengguna tersebut ada bulan lalu tetapi tidak ada bulan ini, yaitu pengguna yang berhenti beraktivitas.

NOT EXISTS merupakan jawaban yang sama baiknya dan menangani NULL dengan aman. Sebutkan bahwa NOT IN dapat berisiko jika himpunan bagian dalamnya mungkin berisi NULL, sebuah jebakan klasik.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Mendefinisikan Reaktivasi

Reaktivasi (atau pengaktifan kembali) adalah kondisi ketika pengguna yang sebelumnya berhenti beraktivitas menjadi aktif lagi. Ciri khasnya adalah jeda dalam linimasanya: aktif, kemudian mengalami masa hening yang lebih lama daripada ambang berhenti beraktivitas, lalu aktif lagi.

Jadi, pengguna yang mengalami reaktivasi bulan ini adalah pengguna yang aktif sekarang, tidak aktif pada periode sebelumnya, tetapi memiliki aktivitas pada periode yang lebih awal. Ini merupakan kebalikan dari pengguna yang berhenti beraktivitas.

Mendeteksi Jeda dengan LAG

Cara elegan untuk menemukan reaktivasi adalah menggunakan fungsi jendela LAG: untuk setiap periode aktivitas seorang pengguna, lihat periode aktif sebelumnya. Jika jeda di antara keduanya melampaui ambang, periode ini merupakan reaktivasi.

LAG menghindari gabungan mandiri dan menghasilkan kueri yang mudah dibaca. Partisikan berdasarkan pengguna, urutkan berdasarkan periode aktif, lalu bandingkan setiap periode dengan pendahulunya.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

Baru, Dipertahankan, atau Direaktivasi

Kueri klasifikasi aktivitas yang lengkap memberi label kepada setiap pengguna yang aktif pada periode ini sebagai salah satu dari: baru (tidak ada aktivitas sebelumnya), dipertahankan (juga aktif pada periode sebelumnya), atau direaktivasi (memiliki aktivitas sebelumnya, tetapi terdapat jeda). Nilai prev_month dari LAG menentukan ketiganya.

prev_month IS NULL → baru
prev_month = active_month - 1 → dipertahankan
selain itu (terdapat jeda) → direaktivasi

Menghasilkan perincian ini merupakan jawaban yang kuat dan lengkap.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

Jebakan NULL pada NOT IN

Jebakan terakhir. Jika Anda menulis kondisi pengguna yang berhenti sebagai WHERE user_id NOT IN (SELECT user_id FROM this_month) dan subkueri tersebut mengembalikan bahkan satu NULL, seluruh hasil menjadi kosong karena NOT IN dievaluasi menjadi UNKNOWN terhadap NULL.

Lebih baik gunakan NOT EXISTS atau anti-gabungan LEFT JOIN / IS NULL, yang berperilaku benar saat terdapat NULL. Menyebutkan perbedaan ini tanpa diminta merupakan sinyal yang dapat diandalkan bahwa Anda berpengalaman dalam wawancara retensi.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Pemeriksaan Cepat

Anda menginginkan pengguna yang aktif bulan lalu tetapi tidak aktif bulan ini. Seorang rekan menulis WHERE user_id NOT IN (SELECT user_id FROM this_month), tetapi kueri tersebut mengembalikan nol baris meskipun jelas ada pengguna yang berhenti beraktivitas. Apa perbaikan yang paling aman?

Ringkasan: Pengguna yang Berhenti dan Reaktivasi

Hal penting tentang pengguna yang berhenti beraktivitas dan reaktivasi:

Tentukan pengguna yang berhenti berdasarkan ambang ketidakaktifan (misalnya, tidak ada aktivitas selama 30 hari) atau perubahan status langganan — jelaskan pilihan Anda.
Hitung MAX(aktivitas terakhir) setiap pengguna, lalu bandingkan dengan CURRENT_DATE - threshold.
Pengguna yang berhenti dari satu periode ke periode berikutnya merupakan selisih himpunan: gunakan EXCEPT, NOT EXISTS, atau anti-gabungan LEFT JOIN / IS NULL.
Reaktivasi merupakan jeda dalam linimasa; deteksi dengan LAG untuk mengklasifikasikan pengguna sebagai baru / dipertahankan / direaktivasi.
Hindari NOT IN jika NULL mungkin muncul — operator ini dapat mengosongkan hasil secara diam-diam.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Kueri Churn dan Kembalinya Pengguna” gratis?

Ya — teks lengkap “Kueri Churn dan Kembalinya Pengguna” 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 “Kueri Churn dan Kembalinya Pengguna”?

Mengidentifikasi pengguna yang pergi dan pengguna yang kembali setelah jeda. 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 “Kueri Churn dan Kembalinya Pengguna” 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

Menentukan Kohort berdasarkan Tindakan Pertama
Membangun Matriks Retensi
Retensi Hari ke-N dan Retensi Bergulir
Kueri Churn dan Kembalinya Pengguna

← Kembali ke SQL Interview Prep