SQL Interview Prep · Pelajaran

Membangun Matriks Retensi

Menghitung pengguna aktif berdasarkan kohort dan selisih periode untuk membentuk tabel retensi.

Membangun Matriks Retensi adalah pelajaran SQL Interview Prep gratis di CoddyKit. Ini adalah pelajaran 2 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.

Apa Itu Matriks Retensi

Kelanjutan dari penentuan kohort adalah matriks retensi yang terkenal: baris berisi kohort, kolom berisi selisih periode (bulan 0, 1, 2, ...), dan setiap sel menghitung jumlah anggota kohort yang masih aktif pada selisih tersebut.

Pewawancara menyukai hal ini karena Anda harus menggabungkan penetapan kohort, penggabungan kembali dengan aktivitas, perhitungan selisih periode, dan pengubahan baris menjadi kolom. Ini adalah kueri yang paling mewakili analitik produk.

Dua Masukan

Anda memerlukan dua hal: periode kohort setiap pengguna (dari pelajaran sebelumnya) dan catatan tentang setiap periode aktif untuk setiap pengguna. Aktivitas berasal dari tabel peristiwa yang sama, yang diringkas ke tingkat periode.

Jadi, rancang kueri sebagai berikut: CTE kohort, lalu CTE aktivitas yang mencantumkan bulan ketika setiap pengguna aktif, kemudian gabungkan keduanya.

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Mencantumkan Periode Aktif

CTE aktivitas menjawab pertanyaan "pada bulan apa setiap pengguna aktif?" Potong setiap peristiwa ke tingkat bulan dan hapus duplikat dengan DISTINCT atau GROUP BY, sehingga pengguna yang aktif 40 kali pada bulan Maret menghasilkan satu baris untuk bulan Maret.

Daftar per pengguna dan per bulan inilah yang digabungkan dengan kohort untuk mengukur keberlangsungan pada berbagai selisih periode.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Menghitung Selisih Periode

Inti matriks ini adalah nomor periode: berapa bulan setelah awal kohort mereka suatu aktivitas terjadi? Kurangkan bulan kohort dari bulan aktivitas.

Di Postgres, cara yang rapi adalah menghitung jumlah bulan penuh di antara kedua tanggal. Rumus yang dapat digunakan lintas sistem mengalikan selisih tahun dengan 12 lalu menambahkan selisih bulan; banyak mesin basis data juga menyediakan fungsi bantuan. Selisih 0 berarti bulan awal kohort itu sendiri.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Menggabungkan Kohort dengan Aktivitas

Gabungkan CTE kohort dengan CTE aktivitas berdasarkan user_id. Setiap baris hasil menyatakan: pengguna ini, yang lahir di kohort X, aktif pada selisih N. Penghitungan pengguna unik untuk setiap (kohort, selisih) membentuk matriks dalam bentuk panjang.

Karena setiap anggota kohort aktif pada bulan awalnya sendiri, selisih 0 seharusnya sama dengan ukuran kohort, sebagai pemeriksaan kewajaran bawaan.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

Tabel Retensi Bentuk Panjang

Tambahkan perhitungan selisih dan lakukan agregasi. Kini Anda memiliki hasil yang rapi dalam bentuk panjang: satu baris untuk setiap kohort dan selisih, yang berisi jumlah pengguna yang bertahan. Banyak pewawancara menerima hasil ini secara langsung karena pembuatan pivot hanya bersifat kosmetik.

Perhatikan bahwa ekspresi selisih muncul dalam SELECT dan GROUP BY karena ekspresi tersebut dihitung, bukan kolom tersimpan.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Mengubah Menjadi Kolom Lebar

Untuk mendapatkan kisi klasik, ubah selisih menjadi kolom dengan agregasi bersyarat: SUM dari satu CASE untuk setiap selisih. Pola portabel ini berfungsi di setiap dialek tanpa sintaks PIVOT khusus.

Setiap CASE menghasilkan 1 ketika nomor_periode pada baris cocok dengan kolom tersebut, sehingga SUM menghitung pengguna yang bertahan pada selisih itu.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

Dari Jumlah Menjadi Tingkat Retensi

Pewawancara biasanya menginginkan persentase, bukan jumlah mentah. Bagi pengguna yang bertahan pada setiap selisih dengan ukuran kohort (selisih 0). Ubah menjadi bilangan desimal atau kalikan dengan 1.0 untuk menghindari pembagian bilangan bulat, kesalahan tersembunyi yang paling umum di sini.

Hasilnya adalah kurva retensi: 100% pada bulan 0, lalu menurun menuju tingkat datar. Tingkat datar itulah metrik yang sebenarnya diperhatikan oleh pihak berkepentingan.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

Jebakan Pembagian Bilangan Bulat

Ini adalah jebakan wawancara yang hampir pasti muncul: pada sebagian besar mesin, 120 / 500 menghasilkan 0, bukan 0.24, karena kedua operand merupakan bilangan bulat. Persentase retensi pun diam-diam muncul sebagai nol semua.

Perbaiki dengan menjadikan salah satu sisi bertipe numerik: kalikan dengan 100.0, ubah salah satu operand menjadi NUMERIC dengan CAST, atau bagi dengan NULLIF(size, 0) untuk sekaligus menangani kohort kosong. Jika Anda mengatakan "dan NULLIF mencegah pembagian dengan nol", Anda akan mendapat nilai tambahan.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Mengisi Selisih yang Hilang dengan Nol

Jika suatu kohort tidak memiliki pengguna yang bertahan pada selisih 2, JOIN tidak menghasilkan baris, sehingga muncul celah dalam matriks. Untuk menampilkan 0 secara eksplisit, buat kisi lengkap dari semua kombinasi (kohort, selisih), lalu gunakan LEFT JOIN untuk memasukkan jumlahnya.

Buat kisi tersebut dengan melakukan CROSS JOIN antara kohort dan daftar angka/selisih, lalu ubah jumlah yang hilang menjadi nol. Pewawancara akan menghargai bahwa Anda menyadari adanya celah tersebut.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Bentuk Segitiga dan Bias Kemutakhiran

Ada satu hal lagi yang dapat dibahas: matriksnya berbentuk segitiga. Kohort yang dimulai bulan lalu belum dapat memiliki nilai bulan ke-3, sehingga selisih yang lebih besar memiliki lebih sedikit kohort yang berkontribusi.

Oleh karena itu, membandingkan rata-rata suatu kolom di seluruh kohort akan condong ke kohort yang lebih lama. Sebutkan bahwa Anda akan menampilkan bentuk segitiga tersebut secara apa adanya atau membatasi perbandingan pada selisih yang telah dicapai semua kohort. Kesadaran ini membedakan analis dari penulis kueri.

Pemeriksaan Singkat

Kueri retensi Anda membagi pengguna yang bertahan dengan ukuran kohort, tetapi setiap persentase tampil sebagai 0 kecuali bulan 0. Apa penyebab yang paling mungkin?

Ringkasan: Matriks Retensi

Untuk membuat matriks retensi dalam wawancara:

Tetapkan setiap pengguna ke suatu periode kohort, lalu daftarkan periode aktif setiap pengguna tanpa duplikasi.
Gabungkan keduanya dan hitung selisih periode (jumlah bulan antara kohort dan aktivitas).
Lakukan agregasi ke bentuk panjang dengan COUNT(DISTINCT user_id); ubah menjadi pivot melalui CASE jika kisi diperlukan.
Ubah jumlah menjadi tingkat dengan hati-hati, hindari pembagian bilangan bulat dan pembagian dengan nol menggunakan 100.0 dan NULLIF.
Gunakan LEFT JOIN pada kisi yang dibuat untuk mengisi sel bernilai nol, dan ingat bahwa matriksnya berbentuk segitiga.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Membangun Matriks Retensi” gratis?

Ya — teks lengkap “Membangun Matriks Retensi” 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 “Membangun Matriks Retensi”?

Menghitung pengguna aktif berdasarkan kohort dan selisih periode untuk membentuk tabel retensi. 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 2 dari 4.

Berapa lama pelajaran “Membangun Matriks Retensi” 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