Temu Duga SQL: Mengira Pengekalan Kohort Hari Ke-7 Tepat

Prompt dan Konteks Berkenaan

Anda diberikan dua jadual 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
);

Bagi pengguna yang tarikh pendaftarannya jatuh dari 1 Januari hingga 31 Januari 2026, laporkan pengekalan Hari ke-7 yang tepat dikumpulkan mengikut tarikh pendaftaran dan saluran pemerolehan. Zon masa perniagaan ialah America/New_York. Hari 0 ialah tarikh pendaftaran tempatan pengguna. Seorang pengguna dikekalkan pada Hari ke-7 jika mereka mempunyai sekurang-kurangnya satu peristiwa core_action_completed pada tarikh kalendar tempatan signup_day + 7. Peristiwa pada Hari 1–6, Hari 8, atau mana-mana tarikh kemudian tidak memenuhi definisi ini.

Kembalikan cohort_day, acquisition_channel, cohort_size, retained_users, dan d7_retention_rate. Pelbagai peristiwa yang layak dikira sekali bagi setiap pengguna. Pengguna tanpa peristiwa kembali mesti kekal dalam penyebut. Saluran bernilai null dikumpulkan sebagai unknown.

Anggap data_complete_through = '2026-02-01 05:00:00+00'. Ini ialah tera air penyerapan (ingestion watermark) eksklusif dan bersamaan dengan tengah malam pada permulaan 1 Februari di New York. Hanya sertakan kohort pendaftaran apabila keseluruhan tarikh kalendar Hari ke-7 mereka adalah sebelum tera air tersebut. Kohort 24 Januari adalah matang kerana Hari ke-7 nya ialah 31 Januari; kohort 25 Januari adalah belum matang.

Masalah ini berguna dalam temu duga jurutera analitik, penganalisis data, dan analitik produk kerana bahagian yang sukar ialah kontrak metrik, bukannya operasi bahagi. Pertanyaan mesti menyelaraskan butiran (grain) kohort, tetingkap kembali, zon masa, kesempurnaan data, dan pembahagian segmen sebelum mengagregatkan apa-apa.

Perkara yang Dinilai oleh Penemu Duga

Isyarat pertama ialah sama ada calon bertanya maksud "pengekalan Hari ke-7". Tiga metrik yang kedengaran serupa adalah berbeza secara ketara: aktiviti tepat pada Hari ke-7, aktiviti pada bila-bila masa semasa Hari 1–7, dan aktiviti pada Hari ke-7 atau kemudian. Sesuatu pertanyaan boleh menjadi sempurna dari segi sintaksis tetapi masih menjawab metrik yang salah. Prompt ini memerlukan definisi yang pertama.

Isyarat kedua ialah disiplin penyebut. Penyebut ialah setiap pengguna yang layak dalam kohort pendaftaran yang matang. Bermula dari jadual peristiwa, atau melakukan inner-join peristiwa kembali kepada pendaftaran, secara senyap membuang pengguna yang tidak pernah kembali dan melebih-lebihkan pengekalan. Kohort mesti dibina terlebih dahulu dan dipelihara dengan left join.

Isyarat ketiga ialah kawalan butiran (grain). Output mempunyai satu baris bagi setiap tarikh pendaftaran dan saluran, manakala bendera (flag) pengekalan mempunyai paling banyak satu baris bagi setiap pengguna. Data peristiwa mentah mungkin mengandungi percubaan semula, duplikasi, dan banyak tindakan sah pada tarikh yang sama. Mengira baris peristiwa akan mengukur volum tindakan dan bukannya pengguna yang dikekalkan, jadi pengangka mesti dinyahduplikasi pada peringkat pengguna.

Isyarat keempat ialah ketepatan temporal. Hari kalendar tempatan tidak selalunya merupakan selang 24 jam yang tetap dan zon bernama boleh menukar ofset UTC di bawah peraturan waktu jimat siang (DST). Menghasilkan tarikh tempatan terlebih dahulu dan membina sempadan hari sasaran setiap pengguna daripada tengah malam zon bernama menyatakan kontrak secara langsung. Ujian seperti event_at = signup_at + INTERVAL '7 days' sebaliknya menjawab soalan masa berlalu (elapsed-time).

Isyarat terakhir ialah pertimbangan operasi. Kohort terkini memerlukan tetingkap pemerhatian yang lengkap, dan jam dinding tidak membuktikan bahawa saluran paip peristiwa adalah lengkap. Seorang calon harus menggunakan tera air data, menerangkan peristiwa yang tiba lewat (late-arriving events), mengesahkan butiran pelaporan terkecil yang boleh dipercayai, dan memilih indeks atau jadual aktiviti harian mengikut kekerapan pertanyaan dan volum data.

Soalan untuk Dijelaskan Sebelum Menjawab

• Adakah Hari ke-7 tepat, bersempadan, atau bergolek? Jawapan ini menggunakan peristiwa tepat pada tarikh tempatan ketujuh selepas pendaftaran. "Dalam masa tujuh hari" dan "pada atau selepas Hari ke-7" memerlukan predikat yang berbeza.
• Peristiwa apakah yang membuktikan pengekalan? Prompt menggunakan core_action_completed. Log masuk, paparan halaman, pembelian, atau sebarang peristiwa akan menghasilkan makna produk yang berbeza dan tidak boleh digantikan sewenang-wenangnya.
• Apakah yang mentakrifkan sehari? Kontrak pelaporan menggunakan tarikh kalendar America/New_York. Zon masa bagi setiap pengguna atau UTC akan mengubah keahlian kohort dan tetingkap kembali.
• Bilakah kohort matang? Kohort boleh dilaporkan hanya apabila tera air data eksklusif berada pada atau melepasi tengah malam tempatan seterusnya selepas Hari ke-7 kohort tersebut.
• Bagaimanakah peristiwa lewat dikendalikan? Jika tera air kemudiannya maju atau peristiwa isi semula (backfill) tiba sebelum tera air tersebut, kohort yang terjejas mesti dikira semula. Kadar yang dipaparkan tidak boleh dianggap tidak boleh diubah (immutable).
• Nilai saluran yang manakah terpakai? Pertanyaan menganggap users.acquisition_channel ialah atribusi masa pendaftaran yang tidak boleh diubah. Saluran semasa yang boleh berubah sebaliknya memerlukan snapshot atribusi berversi.
• Apakah butiran (grain) kohort? Prompt ini mengelompokkan mengikut tarikh pendaftaran dan saluran. Kohort mingguan menggunakan logik peringkat pengguna yang sama tetapi kunci pengelompokan akhir yang berbeza.
• Patutkah kadar itu dalam bentuk pecahan atau peratusan? Pertanyaan mengembalikan pecahan yang dibundarkan kepada empat tempat perpuluhan: 0.3333 bermaksud 33.33%.

Rangka Kerja Jawapan 30 Saat

"Saya akan mentakrifkan Hari ke-7 yang tepat sebagai tarikh kalendar pendaftaran pengguna ditambah tujuh dalam zon masa perniagaan yang dipersetujui. Mula-mula saya membina kohort Januari daripada sempadan UTC tengah malam tempatan dan menyimpan satu baris bagi setiap pengguna dengan hari pendaftaran dan saluran masa pendaftaran. Kemudian saya mengecualikan kohort yang hari sasaran penuhnya berada di luar tera air data eksklusif. Bagi setiap pengguna yang tinggal, saya mencari peristiwa sasaran dalam selang separuh terbuka Hari ke-7 tempatan, menyahduplikasi kepada satu baris dikekalkan bagi setiap pengguna, dan melakukan left-join bendera tersebut kembali kepada kohort matang. Akhir sekali, saya mengira pengguna kohort dan pengguna yang dikekalkan mengikut tarikh dan saluran. Saya akan menguji duplikasi, sifar-kembali, peristiwa Hari ke-6 dan Hari ke-8, tengah malam tempatan, sempadan DST, dan pemotongan kematangan."

Penyelaman Mendalam Langkah Demi Langkah

Mulakan dengan parameter yang menjadikan kontrak pelaporan jelas kelihatan. cohort_end adalah eksklusif, dan tera air ialah detik pertama yang belum diperhatikan. Selang separuh terbuka mengelakkan pengiraan dua kali peristiwa pada tengah malam dan tersusun rapi merentasi tarikh bersebelahan.

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;

Penapis kohort menukar sempadan tengah malam tempatan Januari kepada detik UTC sebelum membandingkannya dengan nilai timestamptz yang diindeks. Ini lebih baik daripada menggunakan penukaran tarikh pada setiap baris signup_at dalam klausa WHERE: ia menyatakan peraturan tarikh tempatan sambil mengekalkan lajur cap masa layak untuk imbasan julat biasa.

Kematangan lebih mudah difahami dalam bentuk tarikh. Tera air bertukar kepada tarikh tempatan 1 Februari. Tarikh sasaran mestilah lebih awal daripada 1 Februari, bermakna tengah malam penutupnya diliputi. 24 Januari tambah tujuh ialah 31 Januari dan lulus. 25 Januari tambah tujuh ialah 1 Februari dan gagal. Jika tera air berada pada waktu tengah hari dan bukannya tengah malam tempatan, bentuk yang teguh akan membandingkan detik akhir hari sasaran secara langsung dengan tera air:

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

CTE retained mencipta bendera pengguna seperti semi-join. DISTINCT menjadikan setiap pengguna menyumbang paling banyak satu baris walaupun pengeluar peristiwa mencuba semula atau pengguna menyelesaikan tindakan teras sepuluh kali. Oleh kerana pengguna tergolong dalam tepat satu baris kohort, penyertaan melalui user_id adalah mencukupi di bawah skema yang dinyatakan. Jika orang yang sama boleh mempunyai beberapa episod pendaftaran, model tersebut memerlukan pengecam episod yang stabil dan penyertaan akan menggunakannya.

Left join terakhir mengekalkan setiap ahli kohort yang matang. Oleh itu, COUNT(*) mengukur penyebut, manakala COUNT(r.user_id) mengira hanya bendera retained yang sepadan. NULLIF adalah bersifat defensif pada peringkat kumpulan, walaupun kumpulan yang dihasilkan daripada mature_cohort semestinya mengandungi sekurang-kurangnya satu baris.

Untuk contoh kecil, andaikan kohort organik 1 Januari mempunyai tiga pengguna dan hanya seorang yang mempunyai tindakan Hari ke-7 yang layak. Hasilnya ialah 3, 1, dan 0.3333. Jika kohort berbayar 2 Januari mempunyai dua pengguna dan kedua-duanya kembali, hasilnya ialah 2, 2, dan 1.0000. Pendaftaran 25 Januari tiada tanpa mengira peristiwa kemudian kerana kohort itu belum matang pada tera air yang dibekalkan.

Jangan puratakan kadar kumpulan yang dipaparkan untuk mencipta jumlah keseluruhan. Kohort harian bagi seorang pengguna tidak boleh membawa berat yang sama seperti kohort seribu pengguna. Pengagregatan (rollup) yang betul ialah:

overall_retention = SUM(retained_users) / SUM(cohort_size)

Pada data bersaiz pengeluaran, indeks yang berguna sepadan dengan sempadan selektif dan kunci cantuman (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 separa hanya sesuai apabila definisi peristiwa ini stabil dan cukup penting untuk mewajarkan kos tulis dan storannya. Untuk papan pemuka berulang melalui aliran peristiwa yang sangat besar, jadual yang diselenggara secara bertambah (incrementally maintained) dengan kunci unik seperti (user_id, activity_day, event_name) boleh menghapuskan imbasan peristiwa mentah yang berulang. activity_day nya mesti diterbitkan di bawah kontrak zon bernama yang sama; jika tidak, pengoptimuman tersebut mengubah metrik.

Katakan U ialah pengguna yang layak dan E ialah baris peristiwa berkaitan yang diperiksa melalui cantuman. Imbasan kohort adalah linear dalam pengguna yang dipilih, penyahduplikasian biasanya merupakan hash atau susunan (sort) ke atas calon yang dikekalkan, dan pengagregatan akhir adalah linear dalam pengguna matang. Kos sebenar bergantung pada kepilihan peristiwa, indeks, statistik, dan bentuk pelan, jadi periksa EXPLAIN (ANALYZE, BUFFERS) pada data representatif dan bukannya menjanjikan kerumitan universal daripada teks SQL sahaja.

Contoh Jawapan Berkualiti Tinggi

"Sebelum menulis SQL, saya akan mengunci empat definisi: Hari ke-7 tepat, core_action_completed sebagai peristiwa kembali, tarikh kalendar New York, dan tera air kesempurnaan eksklusif. Penyebut ialah setiap pendaftaran Januari yang seluruh tarikh Hari ke-7 nya telah diperhatikan; pengangka ialah pengguna berbeza (distinct users) daripada set tersebut dengan sekurang-kurangnya satu peristiwa yang layak.

Saya akan membina kohort terlebih dahulu. Saya menukar tengah malam mula dan tamat tempatan Januari kepada detik UTC untuk penapis julat signup_at, kemudian mengekalkan tarikh pendaftaran tempatan dan saluran masa pendaftaran setiap pengguna. Saya menapis kematangan secara berasingan supaya ia boleh diaudit: dengan tera air tengah malam tempatan 1 Februari, 24 Januari ialah tarikh pendaftaran terakhir yang disertakan.

Bagi setiap pengguna matang, saya mencantumkan peristiwa pada user_id, nama peristiwa sasaran, dan selang separuh terbuka dari tengah malam tempatan pada signup_day + 7 hingga tengah malam tempatan pada signup_day + 8. Sempadan tersebut dikira dalam zon masa bernama, jadi peralihan DST tidak mengubah peraturan kalendar menjadi peraturan 168 jam. Saya memilih ID pengguna unik, melakukan left-join bendera retained ke kohort, dan mengagregatkan mengikut tarikh pendaftaran dan saluran. Left join itu penting kerana pengguna tanpa kembali masih berada dalam penyebut.

Saya akan mengesahkan CTE secara bebas. CTE cohort mesti mempunyai satu baris bagi setiap pendaftaran Januari; CTE maturity mesti berhenti pada 24 Januari untuk tera air ini; CTE retained mesti mempunyai pengguna unik; dan kiraan akhir mesti memenuhi 0 <= retained_users <= cohort_size. Kes ujian akan merangkumi peristiwa pendua, tiada pulangan, nama peristiwa yang salah, Hari 6 dan Hari 8, kedua-dua belah tengah malam tempatan, tarikh DST, saluran null, dan pengisian semula peristiwa lewat. Untuk jumlah keseluruhan merentas kumpulan, saya akan membahagikan jumlah pengguna dikekalkan dengan jumlah pengguna kohort dan bukannya mempuratakan kadar."

Kesilapan Biasa

• Menggunakan inner join. Ini membuang pengguna sifar-kembali dan melambungkan kadar pengekalan. Bina kohort terlebih dahulu dan lakukan left-join pada bendera retained peringkat pengguna.
• Mengira peristiwa dan bukannya pengguna. COUNT(e.event_id) boleh melebihi saiz kohort. Nyahduplikasi pengangka mengikut pengguna atau gunakan ujian kewujudan boolean.
• Membiarkan Hari ke-7 kabur. Predikat yang meliputi Hari 1–7 atau Hari 7 dan seterusnya mengira metrik yang berbeza. Tulis takrifan selang dalam perkataan sebelum SQL.
• Membandingkan dengan cap masa pendaftaran ditambah 168 jam. Itu ialah pengekalan masa berlalu (elapsed-time), bukan takrifan tarikh kalendar zon bernama, dan ia boleh menyimpang di sekitar perubahan waktu jimat siang.
• Mengepos jenis cap masa berindeks di dalam penapis. Menukar setiap signup_at kepada tarikh boleh menghalang imbasan julat yang berguna. Tukarkan sempadan tempatan malar kepada detik UTC sebagai ganti.
• Memasukkan kohort yang belum matang. Pengguna baru-baru ini belum mempunyai peluang penuh untuk kembali, mewujudkan prestasi rendah buatan. Hadkan pada tera air saluran paip, bukan sekadar masa semasa.
• Menganggap tera air sebagai kebenaran kekal. Isi semula (backfill) boleh mengubah kohort yang telah dilaporkan. Tentukan tingkah laku pengiraan semula dan kesegaran data.
• Membaca nilai saluran yang boleh berubah. Atribusi semasa boleh membocorkan maklumat masa hadapan ke dalam kohort sejarah. Gunakan medan pendaftaran yang tidak boleh diubah atau snapshot berversi.
• Mempuratakan peratusan kumpulan. Purata tanpa wajaran memesongkan jumlah keseluruhan apabila saiz kohort berbeza. Agregatkan kiraan nombor asas terlebih dahulu.
• Mengabaikan semantik kosong dan null. Petakan saluran null secara sengaja, dan tentukan sama ada data peristiwa yang hilang bermaksud sifar aktiviti atau saluran paip yang tidak lengkap sebelum menerbitkan metrik.

Soalan Susulan dan Maklum Balas

Bagaimanakah anda mengira pengekalan dalam Hari 1–7?

Kekalkan pendekatan kohort dan kematangan yang sama, tetapi tukar selang peristiwa untuk bermula pada tengah malam tempatan pada signup_day + 1 dan berakhir sebelum tengah malam tempatan pada signup_day + 8. Kematangan masih memerlukan seluruh hari ketujuh selesai. Nyatakan sama ada Hari 0 harus dikira; alat produk dan pasukan sering berbeza mengenai perkara ini.

Bagaimanakah anda mengira pengekalan "Hari ke-7 atau kemudian"?

Sempadan bawah kekal tengah malam tempatan pada signup_day + 7, tetapi sempadan atas menjadi pemotongan pelaporan. Metrik itu adalah kumulatif dan bergantung pada tempoh pemerhatian: kohort yang lebih lama mempunyai lebih banyak peluang untuk kembali. Bandingkan kohort hanya pada umur yang sama atau terbitkan keluk pengekalan dan bukannya satu nombor tanpa had.

Bolehkah PostgreSQL mengagregatkan ini tanpa CTE retained yang berasingan?

Boleh. Carian lateral EXISTS atau agregat yang dibina dengan teliti boleh mengembalikan satu boolean bagi setiap pengguna kohort. Cantuman langsung ditambah COUNT(DISTINCT e.user_id) FILTER (WHERE ...) juga boleh dilakukan, tetapi ia boleh merealisasikan (materialize) banyak baris peristiwa sebelum pengagregatan. CTE yang berasingan menjadikan butiran data dan bukti ketepatan mudah diaudit; pilih pelan akhir daripada data yang diukur.

Bagaimana jika setiap pengguna mempunyai zon masa pelaporan yang berbeza?

Simpan zon yang digunakan untuk episod pendaftaran dan terbitkan kedua-dua hari kohort dan sempadan sasaran daripada nilai yang sama. Perubahan zon sejarah memerlukan dasar yang dinyatakan. Zon bagi setiap pengguna juga bermakna bahawa satu label kohort kalendar merangkumi selang UTC yang berbeza, jadi pra-agregasi mesti mengekalkan zon yang berkenaan atau hari tempatan yang telah dinormalkan.

Bagaimanakah anda menguji sempadan kematangan?

Cipta pendaftaran pada 24 Januari dan 25 Januari di bawah tera air yang dibekalkan. Berikan kedua-duanya peristiwa hari sasaran yang sah. 24 Januari mesti muncul dan 25 Januari tidak boleh muncul. Uji juga tera air satu saat sebelum dan tepat pada tengah malam penutup hari sasaran untuk mengesahkan peraturan sempadan eksklusif.

Bagaimanakah peristiwa yang tiba lewat harus dikendalikan?

Terbitkan tera air masa peristiwa dan kira semula semua kohort yang selang peristiwa layaknya bertindih dengan isi semula (backfill). Jika kelewatan penyerapan mempunyai tahap perkhidmatan yang diketahui, laporan boleh menambah kelewatan keselamatan melangkaui penutupan nominal Hari ke-7. Simpan kiraan mentah dan versi metrik supaya pembetulan boleh dijejaki.

Apakah invariant yang akan anda pantau dalam pengeluaran?

Semak bahawa setiap kumpulan mempunyai cohort_size positif, pengguna yang dikekalkan kekal antara sifar dan saiz kohort, tiada tarikh kohort yang belum matang hadir, jumlah kohort diselaraskan dengan sumber pendaftaran, dan masa peristiwa maksimum meliputi tera air yang diisytiharkan. Beri amaran secara berasingan mengenai anomali volum peristiwa atau kelewatan penyerapan supaya jurang saluran paip tidak disalahertikan sebagai penurunan pengekalan.