Temu Duga SQL: Mengira Corong Penukaran Bertertib dalam Tempoh 24 Jam

Gesaan dan Konteks Berkenaan

Anda diberikan jadual PostgreSQL ini:

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

Kira corong tiga langkah: visit → signup → purchase. Seseorang pengguna memasuki kohort jika mereka mempunyai sekurang-kurangnya satu visit dalam selang pelaporan separuh terbuka [start_at, end_at). Lawatan terawal mereka dalam selang tersebut ialah sauh corong. Pendaftaran yang layak ialah pendaftaran terawal selepas sauh tersebut; pembelian yang layak ialah pembelian terawal selepas pendaftaran yang dipilih. Kedua-duanya mesti berlaku tidak lewat daripada 24 jam selepas lawatan sauh.

event_id adalah unik. Pengeluar peristiwa menjamin bahawa apabila dua peristiwa untuk pengguna yang sama berkongsi cap masa, event_id yang lebih kecil berlaku dahulu. Pembelian tepat 24 jam selepas sauh diambil kira; pembelian walaupun satu mikrosaat kemudian tidak diambil kira. Peristiwa antara langkah corong dibenarkan, dan peristiwa langkah yang pendua tidak boleh mengira pengguna dua kali.

Kembalikan satu baris dengan visited_users, signed_up_users, purchased_users, signup_rate, dan purchase_rate. Kedua-dua kadar adalah kumulatif daripada kohort lawatan, dinyatakan sebagai pecahan yang dibundarkan kepada empat tempat perpuluhan. Kohort kosong mengembalikan kiraan sifar dan kadar null.

Soalan ini sesuai untuk temu duga penganalisis data, jurutera analitik, dan analitik produk. Ia menguji jauh lebih banyak daripada pengagregatan bersyarat: calon mesti menentukan butiran (grain) kohort, mengekalkan susunan peristiwa, memilih rantai yang sah apabila peristiwa berulang, mengendalikan sempadan masa, dan membuktikan bahawa pertanyaan tidak boleh mengira perjalanan yang mustahil.

Perkara yang Dinilai oleh Penemu Duga

Isyarat pertama ialah disiplin kontrak metrik. "Pengguna yang menyelesaikan ketiga-tiga peristiwa" tidak mencukupi. Penemu duga ingin mengetahui lawatan mana yang menyauhkan perjalanan, sama ada langkah-langkah mesti bertertib, sama ada tetingkap ditutup 24 jam selepas lawatan atau selepas setiap langkah, dan sama ada kadar menggunakan langkah sebelumnya atau kohort asal sebagai penyebut. Setiap pilihan mengubah hasil.

Isyarat kedua ialah kawalan butiran (grain). Jadual mentah adalah satu baris setiap peristiwa, tetapi output mengira pengguna. Kohort mesti mempunyai paling banyak satu baris setiap pengguna, dan setiap carian kemudian mesti menghasilkan paling banyak satu peristiwa untuk baris kohort tersebut. Gabungan luas yang diikuti oleh COUNT(DISTINCT user_id) mungkin menyembunyikan rantai peristiwa yang salah dan bukannya membentuk rantai yang betul.

Isyarat ketiga ialah penaakulan berturutan. Ungkapan bebas seperti MIN(CASE WHEN event_name = 'purchase' ...) tidak semestinya mencari pembelian terawal selepas pendaftaran yang dipilih. Pengguna mungkin membuat pembelian, kemudian mendaftar, kemudian membuat pembelian lagi. Pembelian yang sah ialah yang kedua. Oleh itu, setiap langkah memerlukan peristiwa yang dipilih oleh langkah sebelumnya.

Isyarat keempat ialah logik temporal deterministik. Cap masa sahaja tidak dapat menyusun peristiwa pada masa yang sama. Gesaan ini membekalkan event_id sebagai pemutus seri, jadi kunci perbandingan ialah tupel (event_time, event_id). Tanpa jaminan tersebut, data tidak dapat membuktikan peristiwa masa sama mana yang berlaku dahulu, dan SQL tidak sepatutnya mereka-reka sebab akibat.

Isyarat terakhir ialah pertimbangan operasi. Corong yang tetingkapnya menjangkau melebihi end_at memerlukan data peristiwa yang lebih baharu. Laporan matang hanya selepas talian paip selesai memproses sehingga end_at + 24 hours. Calon harus membincangkan ketibaan lewat, indeks, pelan pertanyaan, dan lekapan ujian yang menguji takrifan metrik secara agresif dan bukannya berhenti pada SQL yang sah dari segi sintaks sahaja.

Soalan untuk Dijelaskan Sebelum Menjawab

• Peristiwa manakah yang menyauhkan pengguna? Jawapan ini menggunakan lawatan terawal di dalam selang pelaporan,

walaupun pengguna melawat sebelum selang tersebut. "Lawatan pertama sepanjang masa" memerlukan data sejarah dan penapis kohort yang berbeza.

• Adakah corong ini bertertib? Ya. Pendaftaran mesti menyusuli lawatan sauh, dan pembelian mesti menyusuli

pendaftaran yang dipilih. Kehadiran peristiwa tanpa tertib ialah metrik yang berbeza.

• Di manakah tetingkap bermula dan berakhir? Ia bermula pada lawatan sauh dan ditutup secara inklusif pada

visit_time + 24 hours. Ia tidak bermula semula selepas pendaftaran.

• Bolehkah langkah kemudian berada di luar end_at? Ya, dengan syarat ia berada dalam tetingkap 24 jam pengguna.

end_at memilih lawatan sauh; ia tidak memotong peluang penukaran.

• Bagaimanakah cap masa yang sama disusun? Bandingkan event_time dahulu dan event_id kedua, menggunakan

jaminan yang dinyatakan oleh pengeluar. Jika tiada urutan yang boleh dipercayai, langkah-langkah pada masa yang sama adalah samar-samar.

• Penyebut manakah yang mentakrifkan setiap kadar? Kedua-duanya menggunakan visited_users. Kadar pembelian langkah-ke-langkah akan

membahagikan pembeli dengan pengguna yang mendaftar dan harus mempunyai nama lajur yang berbeza.

• Apakah yang dikembalikan oleh input kosong? Kiraan adalah sifar; kadar adalah null kerana tiada penyebut

yang bermakna. NULLIF menghalang pembahagian dengan sifar.

• Bilakah laporan itu muktamad? Hanya selepas penanda aras (watermark) kesempurnaan data meliputi peluang 24 jam penuh

setiap ahli kohort dan dasar ketibaan lewat telah digunakan.

Rangka Kerja Jawapan 30 Saat

"Saya mula-mula akan menjadikan kohort satu baris bagi setiap pengguna dengan meletakkan kedudukan lawatan dalam selang pelaporan separuh terbuka dan mengekalkan kedudukan satu. Bagi setiap sauh, carian lateral kiri mencari pendaftaran terawal yang (event_time, event_id)-nya adalah selepas lawatan dan masanya dalam tempoh 24 jam. Carian lateral kiri kedua melakukan perkara yang sama untuk pembelian, tetapi bermula selepas pendaftaran yang dipilih sambil mengekalkan tarikh akhir 24 jam asal. Ini menghasilkan satu baris kemajuan bagi setiap pengguna yang melawat. Saya kemudiannya boleh menggunakan kiraan bertapis untuk dua peringkat yang dicapai dan membahagikan kedua-duanya dengan kiraan lawatan. Saya akan menguji peristiwa terbalik, pendua, cap masa yang sama, sempadan tepat 24 jam, lawatan berulang, kohort kosong, dan kematangan laporan."

Perincian Langkah demi Langkah

Kohort diutamakan dahulu. Penapisan sebelum ROW_NUMBER() bermaksud "lawatan terawal dalam selang pelaporan," tepat sepadan dengan gesaan. Menambah event_id pada susunan tetingkap menjadikan sauh yang dipilih stabil apabila lawatan berkongsi cap masa yang sama.

WITH ranked_visits AS (
  SELECT
    user_id,
    event_id AS visit_event_id,
    event_time AS visit_time,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_time, event_id
    ) AS visit_rank
  FROM events
  WHERE event_name = 'visit'
    AND event_time >= :start_at
    AND event_time < :end_at
),
cohort AS (
  SELECT user_id, visit_event_id, visit_time
  FROM ranked_visits
  WHERE visit_rank = 1
)
SELECT *
FROM cohort;

Pertanyaan utama menggunakan dua carian lateral bersandar. PostgreSQL menilai subpertanyaan lateral dengan nilai daripada baris di sebelah kirinya. LEFT JOIN LATERAL mengekalkan baris kohort apabila tiada peristiwa yang layak ditemui, yang tepat diperlukan oleh corong: pelawat yang tidak pernah mendaftar kekal dalam penyebut.

WITH ranked_visits AS (
  SELECT
    user_id,
    event_id AS visit_event_id,
    event_time AS visit_time,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_time, event_id
    ) AS visit_rank
  FROM events
  WHERE event_name = 'visit'
    AND event_time >= :start_at
    AND event_time < :end_at
),
cohort AS (
  SELECT user_id, visit_event_id, visit_time
  FROM ranked_visits
  WHERE visit_rank = 1
),
progress AS (
  SELECT
    c.user_id,
    c.visit_time,
    s.signup_time,
    p.purchase_time
  FROM cohort AS c
  LEFT JOIN LATERAL (
    SELECT
      e.event_id AS signup_event_id,
      e.event_time AS signup_time
    FROM events AS e
    WHERE e.user_id = c.user_id
      AND e.event_name = 'signup'
      AND (
        e.event_time > c.visit_time
        OR (
          e.event_time = c.visit_time
          AND e.event_id > c.visit_event_id
        )
      )
      AND e.event_time <= c.visit_time + INTERVAL '24 hours'
    ORDER BY e.event_time, e.event_id
    LIMIT 1
  ) AS s ON TRUE
  LEFT JOIN LATERAL (
    SELECT e.event_time AS purchase_time
    FROM events AS e
    WHERE e.user_id = c.user_id
      AND e.event_name = 'purchase'
      AND s.signup_time IS NOT NULL
      AND (
        e.event_time > s.signup_time
        OR (
          e.event_time = s.signup_time
          AND e.event_id > s.signup_event_id
        )
      )
      AND e.event_time <= c.visit_time + INTERVAL '24 hours'
    ORDER BY e.event_time, e.event_id
    LIMIT 1
  ) AS p ON TRUE
)
SELECT
  COUNT(*) AS visited_users,
  COUNT(*) FILTER (WHERE signup_time IS NOT NULL) AS signed_up_users,
  COUNT(*) FILTER (WHERE purchase_time IS NOT NULL) AS purchased_users,
  ROUND(
    COUNT(*) FILTER (WHERE signup_time IS NOT NULL)::numeric
      / NULLIF(COUNT(*), 0),
    4
  ) AS signup_rate,
  ROUND(
    COUNT(*) FILTER (WHERE purchase_time IS NOT NULL)::numeric
      / NULLIF(COUNT(*), 0),
    4
  ) AS purchase_rate
FROM progress;

Carian pendaftaran membuktikan tiga sifat bersama-sama: pengguna yang sama, susunan tupel ketat selepas sauh, dan kemasukan dalam tetingkap 24 jam sauh. Susunan ditambah LIMIT 1 memilih satu peristiwa deterministik. Carian pembelian bergantung pada pendaftaran yang dipilih tersebut, jadi pembelian sebelum pendaftaran itu diabaikan manakala pembelian sah yang lebih lewat kekal layak. Tarikh akhirnya masih merujuk kepada c.visit_time; jika tidak, pertanyaan secara tidak sengaja akan memberikan sehingga 48 jam.

Pilihan tamak (greedy) ini betul untuk corong bertertib tiga langkah yang tetap. Memilih pendaftaran terawal yang layak tidak boleh menghapuskan pembelian yang mungkin dibenarkan oleh pendaftaran kemudian: sebarang pembelian selepas pendaftaran kemudian juga berlaku selepas pendaftaran yang lebih awal, dan tarikh akhir bersama tidak berubah. Hujah yang sama terpakai untuk memilih pembelian terawal yang layak. Invarian selepas setiap carian ialah rantai yang dipilih adalah sah dan meninggalkan baki sufiks tetingkap yang paling besar mungkin.

CTE progress mempunyai tepat satu baris bagi setiap pengguna kohort kerana setiap carian lateral mengembalikan paling banyak satu baris. Oleh itu, agregat bertapis mengira pengguna tanpa memerlukan DISTINCT. Kiraan pembelian tidak boleh melebihi kiraan pendaftaran, dan kiraan pendaftaran tidak boleh melebihi kiraan lawatan. Ketaksamaan tersebut merupakan penegasan peringkat hasil yang berguna.

Elakkan corak minimum bebas yang menggoda:

MIN(CASE WHEN event_name = 'signup' THEN event_time END),
MIN(CASE WHEN event_name = 'purchase' THEN event_time END)

Bagi urutan visit(09:00), purchase(09:05), signup(09:10), purchase(09:20), nilai minimum bebas memilih pembelian 09:05 dan mungkin menolak pengguna tersebut. Pertanyaan bersandar memilih pendaftaran 09:10 dan kemudian pembelian 09:20. Nilai minimum bersyarat hanya boleh berfungsi apabila setiap minimum dikekang oleh langkah terpilih sebelumnya, yang merupakan kesukaran utama di sini.

Dua indeks merupakan titik permulaan yang munasabah:

CREATE INDEX events_anchor_scan_idx
  ON events (event_name, event_time, user_id, event_id);

CREATE INDEX events_user_step_lookup_idx
  ON events (user_id, event_name, event_time, event_id);

Indeks pertama menyokong nama peristiwa dan julat masa yang digunakan untuk membina kohort. Indeks kedua menyokong siasatan langkah setiap pengguna yang berulang. Ia menambah storan dan amplifikasi penulisan, dan nilai sebenar bergantung pada keselektifan, pengelompokan (clustering), saiz jadual, dan pelan pilihan PostgreSQL. Sahkan dengan EXPLAIN (ANALYZE, BUFFERS) pada data wakil. Untuk laporan berskala besar yang kerap, model peristiwa dengan medan urutan yang stabil atau jadual perjalanan pengguna yang diselenggara secara bertambah (inkremental) mungkin lebih sesuai, tetapi ia mesti mengekalkan kontrak kohort dan tetingkap yang sama.

Lekapan ujian musuh (adversarial) yang padat harus merangkumi kes-kes ini:

1. purchase before visit -> visitor only
2. visit, purchase, signup -> signed up, not purchased
3. visit, purchase, signup, later purchase -> completes all steps
4. duplicate signups and purchases -> user still counts once per stage
5. purchase exactly at visit + 24 hours -> counts
6. purchase one microsecond after visit + 24 hours -> does not count
7. equal timestamps -> event_id determines sequence
8. repeated visits in the interval -> earliest visit remains the anchor
9. no visits -> zero counts and null rates

Uji juga kitaran hayat pelaporan. Jika end_at ialah tengah malam 1 Julai, pelawat pada 23:59 pada 30 Jun boleh menukar sehingga 23:59 pada 1 Julai. Larian pada tengah malam 1 Julai adalah tidak lengkap. Gunakan penanda aras penyerapan (ingestion), bukan jam dinding, dan kira semula kohort yang terjejas apabila peristiwa lewat tiba.

Contoh Jawapan Berkualiti Tinggi

"Saya akan menyatakan peraturan atribusi sebelum menulis SQL: satu peluang corong bagi setiap pengguna, disauhkan pada lawatan terawal mereka di dalam selang pelaporan. Selang ini adalah separuh terbuka, langkah-langkah kemudian boleh berlaku selepas penamatannya, dan kedua-dua pendaftaran serta pembelian mesti muat dalam tempoh 24 jam dari sauh. Cap masa yang sama disusun mengikut urutan ID peristiwa yang dijamin.

Mula-mula saya menapis lawatan kepada selang tersebut, meletakkan kedudukannya mengikut (event_time, event_id) bagi setiap pengguna, dan mengekalkan kedudukan satu. Ini memberikan butiran (grain) penyebut yang betul. Bagi setiap sauh, saya menggunakan subpertanyaan lateral kiri yang disusun mengikut tupel yang sama untuk mengambil pendaftaran sah terawal. Subpertanyaan lateral kiri kedua merujuk kepada pendaftaran yang dipilih dan mengambil pembelian sah terawal, sambil mengekalkan tarikh akhir terikat kepada lawatan. Oleh kerana setiap carian mempunyai LIMIT 1, hubungan kemajuan kekal satu baris bagi setiap pelawat. Langkah yang hilang kekal null dan bukannya menyingkirkan pengguna.

Agregat akhir mengira semua baris kemajuan, kemudian menggunakan kiraan bertapis untuk cap masa pendaftaran dan pembelian yang bukan null. Kedua-dua kadar dibahagikan dengan kiraan kohort lawatan dan menggunakan NULLIF untuk input kosong. Saya akan menegaskan purchased_users <= signed_up_users <= visited_users, memeriksa rantai sampel untuk setiap peringkat, dan menguji secara khusus peristiwa terbalik, langkah berulang, cap masa yang sama, dan kedua-dua belah sempadan 24 jam.

Untuk pengeluaran, saya tidak akan menganggap kohort terbaharu selesai sehingga penanda aras penyerapan meliputi tetingkap peluang penuhnya. Saya akan membandingkan pelan dengan dan tanpa indeks yang menyokong imbasan sauh dan carian langkah setiap pengguna. Jika cap masa dan ID peristiwa tidak mewakili urutan yang boleh dipercayai, saya akan berhenti dan membetulkan kontrak peristiwa kerana pertanyaan tidak dapat memulihkan sebab akibat yang hilang."

Kesilapan Biasa

• Hanya menyemak kehadiran peristiwa. Tiga bendera peristiwa boleh mengira purchase → visit → signup. Sesuatu corong

memerlukan rantai bertertib yang eksplisit.

• Mengambil cap masa pertama yang bebas. Pembelian terawal mungkin mendahului pendaftaran yang dipilih walaupun

pembelian kemudian melengkapkan perjalanan yang sah.

• Menyauhkan pada setiap lawatan secara tidak sengaja. Ini mengubah butiran daripada satu peluang pengguna kepada satu

peluang lawatan dan boleh mengira atau mengatribusikan pengguna secara berbeza.

• Hanya menggunakan event_time untuk menyusun. Cap masa yang sama menjadikan hasil tidak stabil. Gunakan kunci

urutan yang boleh dipercayai atau akui bahawa urutan tidak dapat diketahui.

• Memulakan semula tetingkap pada setiap langkah. Memberikan pendaftaran 24 jam dan pembelian 24 jam lagi melanggar

kontrak 24 jam berasaskan sauh.

• Menapis langkah kemudian dengan end_at. Ini memendekkan peluang untuk pelawat berhampiran sempadan pelaporan.

Ambil peristiwa kemudian melalui tarikh akhir setiap sauh.

• Menggunakan gabungan lateral dalaman (inner join). Pelawat tanpa pendaftaran akan hilang, menyebabkan kadar penukaran melambung secara palsu.
• Mengira baris peristiwa mentah. Percubaan semula dan tindakan berulang boleh menjadikan kiraan peringkat melebihi saiz kohort.
• Membahagikan dengan langkah sebelumnya secara tidak sengaja. Ini mengira kadar penukaran langkah, bukan kadar

kumulatif yang dinyatakan daripada lawatan.

• Menerbitkan kohort yang belum matang. Ketiadaan peristiwa kemudian bukanlah bukti keciciran (drop-off) sehingga

tetingkap penuh diliputi oleh data yang boleh dipercayai.

Soalan Susulan dan Maklum Balas

Bagaimana jika mana-mana lawatan boleh memulakan perjalanan yang sah?

Butirannya berubah. Hasilkan calon sauh untuk setiap lawatan, padankan rantai yang sah daripada setiap satu, dan kemudian gunakan peraturan atribusi seperti perjalanan selesai terawal bagi setiap pengguna. Jangan hanya menggantikan CTE sauh: tetingkap yang bertindih boleh bersaing untuk peristiwa kemudian yang sama, jadi kontrak produk mesti menyatakan sama ada penggunaan semula dibenarkan.

Bagaimana jika setiap langkah mesti berkongsi sesi atau produk?

Bawa kunci korelasi dari sauh dan wajipkannya dalam kedua-dua carian lateral. Butirannya menjadi pengguna-sesi atau pengguna-produk dan bukan pengguna sahaja. Tentukan cara kunci null bertindak dan sama ada kunci itu tidak boleh diubah sebelum bergantung pada gabungan tersebut.

Bagaimanakah anda akan menyokong sepuluh langkah corong?

Sepuluh gabungan lateral tulisan tangan sukar untuk diaudit. Bergantung pada pangkalan data, gunakan SQL rekursif, fungsi corong atau urutan asli (native), atau tugas prapemprosesan yang menelusuri peristiwa bertertib sebagai mesin keadaan (state machine). Kekalkan invarian yang sama: satu sauh yang ditakrifkan, susunan langkah monotonik, satu tarikh akhir bersama, dan peraturan atribusi yang boleh diaudit.

Bagaimana jika peristiwa tiba lewat atau tidak mengikut urutan?

Asingkan masa peristiwa daripada masa penyerapan (ingestion), terbitkan penanda aras kesempurnaan, dan nyatakan semula kohort yang tetingkapnya menerima data lewat. Urutan masa peristiwa masih boleh dikira selepas ketibaan, tetapi laporan adalah sementara sehingga selang kelewatan yang dipersetujui telah berlalu.

Bolehkah fungsi tetingkap menyelesaikan keseluruhan masalah?

Boleh, contohnya dengan mengimbas strim teratur setiap pengguna dan membawa keadaan (state), tetapi LAG sahaja tidak mencukupi kerana peristiwa yang tidak berkaitan dan pendua mungkin berada di antara langkah-langkah. Penyelesaian lateral menjadikan kebergantungan antara langkah terpilih jelas. Pilih formulasi lain hanya jika peralihan keadaan dan semantik atribusinya sama jelas dan pelan terukurnya lebih baik.