Wawancara SQL: Menghitung Retensi Kohort Hari ke-7 Tepat

Prompt dan Konteks yang Berlaku

Anda diberikan dua tabel PostgreSQL:

CREATE TABLE users (
  user_id bigint PRIMARY KEY,
  signup_at timestamptz NOT NULL,
  acquisition_channel text
);

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  event_at timestamptz NOT NULL,
  event_name text NOT NULL
);

Untuk pengguna yang tanggal pendaftarannya jatuh antara 1 Januari hingga 31 Januari 2026, laporkan retensi Hari ke-7 yang tepat yang dikelompokkan berdasarkan tanggal pendaftaran dan saluran akuisisi. Zona waktu bisnis adalah America/New_York. Hari ke-0 adalah tanggal pendaftaran lokal pengguna. Seorang pengguna dinyatakan tertahan (retained) pada Hari ke-7 jika mereka memiliki setidaknya satu peristiwa core_action_completed pada tanggal kalender lokal signup_day + 7. Peristiwa pada Hari ke-1–6, Hari ke-8, atau tanggal lebih lanjut tidak memenuhi definisi ini.

Kembalikan cohort_day, acquisition_channel, cohort_size, retained_users, dan d7_retention_rate. Beberapa peristiwa yang memenuhi syarat dihitung sekali per pengguna. Pengguna tanpa peristiwa kembali harus tetap berada di penyebut. Saluran bernilai null dikelompokkan sebagai unknown.

Asumsikan data_complete_through = '2026-02-01 05:00:00+00'. Ini adalah batas data masuk (ingestion watermark) eksklusif dan sama dengan tengah malam pada awal 1 Februari di New York. Hanya sertakan kohort pendaftaran jika seluruh tanggal kalender Hari ke-7 mereka berada sebelum batas tersebut. Kohort 24 Januari sudah matang (mature) karena Hari ke-7 nya adalah 31 Januari; kohort 25 Januari belum matang.

Masalah ini berguna dalam wawancara analytics-engineer, data-analyst, dan product-analytics karena bagian yang sulit adalah kontrak metriknya, bukan operas pembagiannya. Kueri harus menyelaraskan butiran (grain) kohort, jendela kembali, zona waktu, kelengkapan data, dan segmentasi sebelum melakukan agregasi apa pun.

Apa yang Dievaluasi oleh Pewawancara

Sinyal pertama adalah apakah kandidat menanyakan apa arti "retensi Hari ke-7". Tiga metrik yang terdengar serupa sebenarnya berbeda secara materiil: aktivitas tepat pada Hari ke-7, aktivitas pada titik mana pun selama Hari ke-1–7, dan aktivitas pada Hari ke-7 atau lebih lama. Sebuah kueri bisa sempurna secara sintaksis namun tetap menjawab metrik yang salah. Prompt ini memerlukan definisi pertama.

Sinyal kedua adalah disiplin penyebut. Penyebutnya adalah setiap pengguna yang memenuhi syarat dalam kohort pendaftaran yang matang. Memulai dari tabel peristiwa, atau melakukan inner-join peristiwa kembali ke pendaftaran, secara diam-diam menghapus pengguna yang tidak pernah kembali dan melebih-lebihkan retensi. Kohort harus dibangun terlebih dahulu dan dipertahankan dengan left join.

Sinyal ketiga adalah kontrol butiran data (grain). Output memiliki satu baris per tanggal pendaftaran dan saluran, sementara tanda (flag) retensi memiliki paling banyak satu baris per pengguna. Data peristiwa mentah mungkin berisi percobaan ulang (retries), duplikat, dan banyak tindakan valid pada tanggal yang sama. Menghitung baris peristiwa akan mengukur volume tindakan alih-alih pengguna yang tertahan, sehingga pembilang harus dideduplikasi pada tingkat pengguna.

Sinyal keempat adalah ketepatan temporal. Suatu hari kalender lokal tidak selalu merupakan interval 24 jam yang tetap dan zona bernama dapat mengubah offset UTC di bawah aturan daylight saving time (DST). Menurunkan tanggal lokal terlebih dahulu dan membangun batas hari target setiap pengguna dari tengah malam zona bernama mengekspresikan kontrak secara langsung. Pengujian seperti event_at = signup_at + INTERVAL '7 days' malah menjawab pertanyaan elapsed-time (waktu berlalu).

Sinyal terakhir adalah penilaian operasional. Kohort terbaru memerlukan jendela pengamatan yang lengkap, dan jam dinding tidak membuktikan bahwa pipeline peristiwa sudah lengkap. Seorang kandidat harus menggunakan data watermark, menjelaskan peristiwa yang datang terlambat (late-arriving events), memvalidasi butiran pelaporan terkecil yang andal, dan memilih indeks atau tabel aktivitas harian sesuai dengan frekuensi kueri dan volume data.

Pertanyaan untuk Diklarifikasi Sebelum Menjawab

• Apakah Hari ke-7 bersifat tepat (exact), berbatas (bounded), atau bergulir (rolling)? Jawaban ini menggunakan peristiwa tepat pada tanggal lokal ketujuh setelah pendaftaran. "Dalam tujuh hari" dan "pada atau setelah Hari ke-7" memerlukan predikat yang berbeda.
• Peristiwa apa yang membuktikan retensi? Prompt ini menggunakan core_action_completed. Login, tampilan halaman, pembelian, atau peristiwa apa pun akan menghasilkan arti produk yang berbeda dan tidak boleh diganti sembarangan.
• Apa yang mendefinisikan sebuah hari? Kontrak pelaporan menggunakan tanggal kalender America/New_York. Zona waktu per pengguna atau UTC akan mengubah keanggotaan kohort dan jendela kembali.
• Kapan sebuah kohort dikatakan matang? Sebuah kohort hanya dapat dilaporkan jika data watermark eksklusif berada pada atau setelah tengah malam lokal berikutnya setelah Hari ke-7 kohort tersebut.
• Bagaimana penanganan peristiwa yang terlambat? Jika watermark kemudian maju atau peristiwa backfill tiba sebelum watermark tersebut, kohort yang terpengaruh harus dihitung ulang. Tingkat yang ditampilkan tidak boleh dianggap kekal (immutable).
• Nilai saluran mana yang berlaku? Kueri mengasumsikan users.acquisition_channel adalah atribusi waktu pendaftaran yang tidak berubah. Saluran saat ini yang dapat berubah memerlukan snapshot atribusi berversi.
• Apa butiran (grain) kohortnya? Prompt ini mengelompokkan berdasarkan tanggal pendaftaran dan saluran. Kohort mingguan menggunakan logika tingkat pengguna yang sama tetapi kunci pengelompokan akhir yang berbeda.
• Apakah tingkat retensi harus berupa pecahan atau persentase? Kueri mengembalikan pecahan yang dibulatkan ke empat tempat desimal: 0.3333 berarti 33,33%.

Kerangka Jawaban 30 Detik

"Saya akan mendefinisikan Hari ke-7 yang tepat sebagai tanggal kalender pendaftaran pengguna ditambah tujuh dalam zona waktu bisnis yang disepakati. Pertama, saya membangun kohort Januari dari batas UTC tengah malam lokal dan menyimpan satu baris per pengguna dengan hari pendaftaran dan saluran saat pendaftaran. Kemudian saya mengecualikan kohort yang hari target lengkapnya berada di luar data watermark eksklusif. Untuk setiap pengguna yang tersisa, saya mencari peristiwa target dalam interval setengah terbuka Hari ke-7 lokal, mendeduplikasi menjadi satu baris tertahan per pengguna, dan melakukan left-join flag tersebut kembali ke kohort yang matang. Terakhir, saya menghitung pengguna kohort dan pengguna yang tertahan berdasarkan tanggal dan saluran. Saya akan menguji data duplikat, zero-return, peristiwa Hari ke-6 dan Hari ke-8, tengah malam lokal, batas DST, dan batas kematangan."

Pembahasan Mendalam Langkah Demi Langkah

Mulailah dengan parameter yang membuat kontrak pelaporan terlihat jelas. cohort_end bersifat eksklusif, dan watermark adalah instan pertama yang belum diamati. Interval setengah terbuka mencegah penghitungan ganda suatu peristiwa pada tengah malam dan tersusun rapi di seluruh tanggal yang berdekatan.

WITH params AS (
  SELECT
    'America/New_York'::text AS tz,
    DATE '2026-01-01' AS cohort_start,
    DATE '2026-02-01' AS cohort_end,
    TIMESTAMPTZ '2026-02-01 05:00:00+00' AS data_complete_through
),
cohort AS (
  SELECT
    u.user_id,
    COALESCE(u.acquisition_channel, 'unknown') AS acquisition_channel,
    (u.signup_at AT TIME ZONE p.tz)::date AS signup_day
  FROM users AS u
  CROSS JOIN params AS p
  WHERE u.signup_at >= (p.cohort_start::timestamp AT TIME ZONE p.tz)
    AND u.signup_at < (p.cohort_end::timestamp AT TIME ZONE p.tz)
),
mature_cohort AS (
  SELECT c.*
  FROM cohort AS c
  CROSS JOIN params AS p
  WHERE c.signup_day + 7
    < (p.data_complete_through AT TIME ZONE p.tz)::date
),
retained AS (
  SELECT DISTINCT c.user_id
  FROM mature_cohort AS c
  CROSS JOIN params AS p
  JOIN events AS e
    ON e.user_id = c.user_id
   AND e.event_name = 'core_action_completed'
   AND e.event_at >= ((c.signup_day + 7)::timestamp AT TIME ZONE p.tz)
   AND e.event_at < ((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
)
SELECT
  c.signup_day AS cohort_day,
  c.acquisition_channel,
  COUNT(*) AS cohort_size,
  COUNT(r.user_id) AS retained_users,
  ROUND(
    COUNT(r.user_id)::numeric / NULLIF(COUNT(*), 0),
    4
  ) AS d7_retention_rate
FROM mature_cohort AS c
LEFT JOIN retained AS r
  ON r.user_id = c.user_id
GROUP BY c.signup_day, c.acquisition_channel
ORDER BY c.signup_day, c.acquisition_channel;

Filter kohort mengonversi batas tengah malam lokal bulan Januari ke instan UTC sebelum membandingkannya dengan nilai timestamptz yang diindeks. Ini lebih baik daripada menerapkan konversi tanggal ke setiap baris signup_at dalam klausul WHERE: cara ini menyatakan aturan tanggal lokal sambil menjaga kolom timestamp tetap memenuhi syarat untuk range scan normal.

Kematangan lebih mudah dipahami dalam bentuk tanggal. Watermark dikonversi ke tanggal lokal 1 Februari. Tanggal target harus secara ketat lebih awal dari 1 Februari, yang berarti tengah malam penutupnya tercakup. 24 Januari ditambah tujuh adalah 31 Januari dan berhasil lolos. 25 Januari ditambah tujuh adalah 1 Februari dan gagal. Jika watermark berada di tengah hari alih-alih tengah malam lokal, bentuk yang kuat akan membandingkan instan akhir hari target secara langsung dengan watermark:

((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
  <= p.data_complete_through

CTE retained membuat flag pengguna mirip semi-join. DISTINCT membuat setiap pengguna berkontribusi paling banyak satu baris bahkan jika produsen peristiwa melakukan percobaan ulang atau pengguna menyelesaikan tindakan inti sepuluh kali. Karena seorang pengguna hanya menjadi milik tepat satu baris kohort, penggabungan dengan user_id sudah cukup di bawah skema yang dinyatakan. Jika orang yang sama dapat memiliki beberapa episode pendaftaran, model tersebut akan memerlukan pengidentifikasi episode yang stabil dan penggabungan akan menggunakannya.

Left join terakhir mempertahankan setiap anggota kohort yang matang. Oleh karena itu, COUNT(*) mengukur penyebut, sementara COUNT(r.user_id) hanya menghitung flag retained yang cocok. NULLIF bersifat defensif di tingkat grup, meskipun grup yang dihasilkan dari mature_cohort pasti berisi setidaknya satu baris.

Untuk contoh kecil, anggap kohort organik 1 Januari memiliki tiga pengguna dan hanya satu yang memiliki tindakan Hari ke-7 yang memenuhi syarat. Hasilnya adalah 3, 1, dan 0.3333. Jika kohort berbayar (paid) 2 Januari memiliki dua pengguna dan keduanya kembali, hasilnya adalah 2, 2, dan 1.0000. Pendaftaran 25 Januari tidak muncul terlepas dari peristiwa setelahnya karena kohort tersebut belum matang pada watermark yang diberikan.

Jangan merata-ratakan tingkat grup yang ditampilkan untuk membuat total. Kohort harian dari satu pengguna tidak boleh memiliki bobot yang sama dengan kohort seribu pengguna. Rollup yang benar adalah:

overall_retention = SUM(retained_users) / SUM(cohort_size)

Pada data berukuran produksi, indeks yang berguna mencocokkan batas selektif dan kunci penggabungan (join keys):

CREATE INDEX users_signup_at_idx
  ON users (signup_at);

CREATE INDEX events_core_action_user_time_idx
  ON events (user_id, event_at)
  WHERE event_name = 'core_action_completed';

Indeks peristiwa parsial hanya tepat jika definisi peristiwa ini stabil dan cukup penting untuk membenarkan biaya penulisan dan penyimpanannya. Untuk dasbor berkala atas aliran peristiwa yang sangat besar, tabel yang dipertahankan secara inkremental dengan kunci unik seperti (user_id, activity_day, event_name) dapat menghilangkan pemindaian berulang terhadap peristiwa mentah. activity_day miliknya harus diturunkan di bawah kontrak zona bernama yang sama; jika tidak, pengoptimalan tersebut mengubah metrik.

Misalkan U adalah pengguna yang memenuhi syarat dan E adalah baris peristiwa relevan yang diperiksa melalui penggabungan. Pemindaian kohort bersifat linier terhadap pengguna yang dipilih, deduplikasi biasanya berupa hash atau sort atas kandidat yang tertahan, dan agregasi akhir bersifat linier terhadap pengguna yang matang. Biaya aktual bergantung pada selektivitas peristiwa, indeks, statistik, dan bentuk rencana kueri (plan shape), jadi periksalah EXPLAIN (ANALYZE, BUFFERS) pada data representatif daripada menjanjikan kompleksitas universal dari teks SQL saja.

Contoh Jawaban Berkualitas Tinggi

"Sebelum menulis SQL, saya akan mengunci empat definisi: Hari ke-7 yang tepat, core_action_completed sebagai peristiwa kembali, tanggal kalender New York, dan watermark kelengkapan yang eksklusif. Penyebutnya adalah setiap pendaftaran bulan Januari yang seluruh tanggal Hari ke-7 nya telah diamati; pembilangnya adalah pengguna berbeda (distinct users) dari kumpulan tersebut dengan setidaknya satu peristiwa yang memenuhi syarat.

Saya akan membangun kohort terlebih dahulu. Saya mengonversi tengah malam awal dan akhir lokal bulan Januari ke instan UTC untuk filter rentang signup_at, lalu mempertahankan tanggal pendaftaran lokal dan saluran saat pendaftaran untuk setiap pengguna. Saya memfilter kematangan secara terpisah agar dapat diaudit: dengan watermark tengah malam lokal 1 Februari, 24 Januari adalah tanggal pendaftaran terakhir yang disertakan.

Untuk setiap pengguna yang matang, saya menggabungkan peristiwa pada user_id, nama peristiwa target, dan interval setengah terbuka dari tengah malam lokal pada signup_day + 7 hingga tengah malam lokal pada signup_day + 8. Batas-batas tersebut dihitung dalam zona waktu bernama, sehingga transisi DST tidak mengubah aturan kalender menjadi aturan 168 jam. Saya memilih user ID yang unik (distinct), melakukan left-join flag retained ke kohort, dan mengagregasi berdasarkan tanggal pendaftaran dan saluran. Left join tersebut sangat penting karena pengguna tanpa peristiwa kembali tetap berada di penyebut.

Saya akan memvalidasi CTE secara independen. CTE cohort harus memiliki satu baris per pendaftaran Januari; CTE maturity harus berhenti pada 24 Januari untuk watermark ini; CTE retained harus memiliki pengguna unik; dan hitungan akhir harus memenuhi 0 <= retained_users <= cohort_size. Kasus pengujian akan mencakup peristiwa duplikat, tanpa kembali (zero return), nama peristiwa yang salah, Hari ke-6 dan Hari ke-8, kedua sisi tengah malam lokal, tanggal DST, saluran null, dan backfill peristiwa yang terlambat. Untuk total di seluruh grup, saya akan membagi jumlah pengguna yang tertahan dengan jumlah pengguna kohort daripada merata-ratakan persentasenya."

Kesalahan Umum

• Menggunakan inner join. Ini membuang pengguna zero-return dan menaikkan tingkat retensi secara keliru. Bangun kohort terlebih dahulu dan lakukan left-join pada flag retained tingkat pengguna.
• Menghitung peristiwa alih-alih pengguna. COUNT(e.event_id) dapat melebihi ukuran kohort. Deduplikasi pembilang berdasarkan pengguna atau gunakan uji keberadaan boolean (boolean existence test).
• Membiarkan Hari ke-7 ambigu. Predikat yang mencakup Hari ke-1–7 atau Hari ke-7 dan seterusnya menghitung metrik yang berbeda. Tuliskan definisi interval dengan kata-kata sebelum menulis SQL.
• Membandingkan dengan timestamp pendaftaran ditambah 168 jam. Itu adalah retensi waktu berlalu (elapsed-time), bukan definisi tanggal kalender zona bernama, dan dapat berbeda di sekitar perubahan daylight saving time.
• Melakukan type casting timestamp berindeks di dalam filter. Mengonversi setiap signup_at menjadi tanggal dapat mencegah range scan yang berguna. Konversikan batas lokal konstan menjadi instan sebagai gantinya.
• Menyertakan kohort yang belum matang. Pengguna baru belum memiliki kesempatan penuh untuk kembali, sehingga menciptakan underperformance buatan. Batasi berdasarkan pipeline watermark, bukan hanya waktu saat ini.
• Memperlakukan watermark sebagai kebenaran permanen. Backfill dapat mengubah kohort yang sudah dilaporkan. Tentukan perilaku komputasi ulang dan kesegaran data (freshness).
• Membaca nilai saluran yang dapat berubah. Atribusi saat ini dapat membocorkan informasi masa depan ke dalam kohort historis. Gunakan kolom pendaftaran yang tidak berubah atau snapshot berversi.
• Merata-ratakan persentase grup. Rata-rata tanpa bobot mendistorsi total saat ukuran kohort berbeda. Lakukan roll up pada hitungan angka mentahnya terlebih dahulu.
• Mengabaikan semantik kosong dan null. Petakan saluran null secara sengaja, dan putuskan apakah data peristiwa yang hilang berarti nol aktivitas atau pipeline yang tidak lengkap sebelum memublikasikan metrik.

Pertanyaan Lanjutan dan Tanggapan

Bagaimana Anda menghitung retensi dalam Hari ke-1–7?

Pertahankan pendekatan kohort dan kematangan yang sama, tetapi ubah interval peristiwa untuk dimulai pada tengah malam lokal pada signup_day + 1 dan berakhir sebelum tengah malam lokal pada signup_day + 8. Kematangan tetap membutuhkan seluruh hari ketujuh selesai. Nyatakan apakah Hari ke-0 harus dihitung; alat produk dan tim sering kali berbeda pendapat tentang hal ini.

Bagaimana Anda menghitung retensi "Hari ke-7 atau lebih lama"?

Batas bawah tetap tengah malam lokal pada signup_day + 7, tetapi batas atas menjadi batas akhir pelaporan (reporting cutoff). Metrik tersebut bersifat kumulatif dan bergantung pada panjang pengamatan: kohort yang lebih lama memiliki lebih banyak kesempatan untuk kembali. Bandingkan kohort hanya pada usia yang sama atau publikasikan kurva retensi daripada satu angka tanpa batas.

Bisakah PostgreSQL mengagregasi ini tanpa CTE retained yang terpisah?

Bisa. Pencarian lateral EXISTS atau agregat yang dibangun dengan cermat dapat mengembalikan satu boolean per pengguna kohort. Penggabungan langsung ditambah COUNT(DISTINCT e.user_id) FILTER (WHERE ...) juga memungkinkan, tetapi dapat mematerialisasi banyak baris peristiwa sebelum agregasi. CTE terpisah membuat butiran data dan bukti kebenaran mudah diaudit; pilih rencana akhir berdasarkan data terukur.

Bagaimana jika setiap pengguna memiliki zona waktu pelaporan yang berbeda?

Simpan zona yang berlaku untuk episode pendaftaran dan turunkan hari kohort serta batas target dari nilai yang sama. Perubahan zona historis memerlukan kebijakan yang jelas. Zona per pengguna juga berarti bahwa satu label kohort kalender mencakup interval UTC yang berbeda, sehingga pra-agregasi harus mempertahankan zona yang berlaku atau hari lokal yang sudah dinormalisasi.

Bagaimana Anda menguji batas kematangan?

Buat pendaftaran pada 24 Januari dan 25 Januari di bawah watermark yang diberikan. Berikan keduanya peristiwa hari target yang valid. 24 Januari harus muncul dan 25 Januari tidak boleh muncul. Uji juga watermark satu detik sebelum dan tepat pada tengah malam penutup hari target untuk mengonfirmasi aturan batas eksklusif.

Bagaimana peristiwa yang datang terlambat harus ditangani?

Publikasikan watermark waktu peristiwa dan hitung ulang semua kohort yang interval peristiwa yang memenuhi syarat tumpang tindih dengan backfill. Jika keterlambatan data masuk memiliki service level yang diketahui, laporan dapat menambahkan penundaan pengaman di luar penutupan nominal Hari ke-7. Simpan hitungan mentah dan versi metrik sehingga koreksi dapat dilacak.

Invarian apa yang akan Anda pantau dalam produksi?

Periksa bahwa setiap grup memiliki cohort_size positif, pengguna yang tertahan berada di antara nol dan ukuran kohort, tidak ada tanggal kohort yang belum matang yang muncul, total kohort sesuai dengan sumber pendaftaran, dan waktu peristiwa maksimum mencakup watermark yang dideklarasikan. Berikan peringatan terpisah pada anomali volume peristiwa atau keterlambatan data masuk sehingga celah pipeline tidak disalahartikan sebagai penurunan retensi.