Wawancara SQL: Menghitung Funnel Konversi Berurutan dalam 24 Jam

Konteks dan Petunjuk Soal

Anda diberikan tabel PostgreSQL berikut:

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

Hitung funnel tiga langkah: visit → signup → purchase. Pengguna masuk ke dalam kohort jika mereka memiliki setidaknya satu visit dalam interval pelaporan setengah terbuka [start_at, end_at). Kunjungan paling awal mereka dalam interval tersebut menjadi jangkar funnel. Pendaftaran yang memenuhi syarat adalah pendaftaran paling awal setelah jangkar tersebut; pembelian yang memenuhi syarat adalah pembelian paling awal setelah pendaftaran yang terpilih. Keduanya harus terjadi tidak lebih dari 24 jam setelah kunjungan jangkar.

event_id bersifat unik. Produsen peristiwa menjamin bahwa ketika dua peristiwa untuk pengguna yang sama memiliki timestamp yang sama, event_id yang lebih kecil terjadi terlebih dahulu. Pembelian tepat 24 jam setelah jangkar tetap dihitung; pembelian bahkan satu mikrodetik setelahnya tidak dihitung. Peristiwa di antara langkah-langkah funnel diperbolehkan, dan peristiwa langkah yang duplikat tidak boleh menghitung pengguna dua kali.

Kembalikan satu baris berisi visited_users, signed_up_users, purchased_users, signup_rate, dan purchase_rate. Kedua rasio bersifat kumulatif dari kohort kunjungan, dinyatakan sebagai pecahan yang dibulatkan ke empat tempat desimal. Kohort yang kosong mengembalikan jumlah nol dan rasio null.

Pertanyaan ini cocok untuk wawancara analis data, analytics engineer, dan analitik produk. Pertanyaan ini menguji lebih dari sekadar agregasi kondisional: kandidat harus mendefinisikan granularitas kohort, mempertahankan urutan peristiwa, memilih rantai yang valid saat peristiwa berulang, menangani batas waktu, dan membuktikan bahwa kueri tidak dapat menghitung perjalanan yang mustahil.

Hal yang Dievaluasi Pewawancara

Sinyal pertama adalah disiplin kontrak metrik. "Pengguna yang menyelesaikan ketiga peristiwa" tidaklah cukup. Pewawancara ingin mengetahui kunjungan mana yang menjangkar perjalanan, apakah langkah-langkah harus berurutan, apakah jendela waktu ditutup 24 jam setelah kunjungan atau setelah setiap langkah, dan apakah rasio menggunakan langkah sebelumnya atau kohort asli sebagai penyebut. Setiap pilihan mengubah hasil.

Sinyal kedua adalah kontrol granularitas. Tabel mentah berisi satu baris per peristiwa, tetapi output menghitung pengguna. Kohort harus memiliki paling banyak satu baris per pengguna, dan setiap pencarian berikutnya harus menghasilkan paling banyak satu peristiwa untuk baris kohort tersebut. Join yang luas diikuti oleh COUNT(DISTINCT user_id) mungkin menyembunyikan rantai peristiwa yang salah alih-alih membentuk rantai yang benar.

Sinyal ketiga adalah penalaran sekuensial. Ekspresi independen seperti MIN(CASE WHEN event_name = 'purchase' ...) tidak selalu menemukan pembelian paling awal setelah pendaftaran yang dipilih. Pengguna mungkin melakukan pembelian, lalu mendaftar, lalu membeli lagi. Pembelian yang valid adalah yang kedua. Oleh karena itu, setiap langkah membutuhkan peristiwa yang dipilih oleh langkah sebelumnya.

Sinyal keempat adalah logika temporal deterministik. Timestamp saja tidak dapat mengurutkan peristiwa pada waktu yang sama. Petunjuk ini menyediakan event_id sebagai pemecah seri, sehingga kunci perbandingannya adalah tupel (event_time, event_id). Tanpa jaminan tersebut, data tidak dapat membuktikan peristiwa waktu-sama mana yang terjadi lebih dahulu, dan SQL tidak boleh mengarang kausalitas.

Sinyal terakhir adalah pertimbangan operasional. Funnel yang jendelanya melampaui end_at membutuhkan data peristiwa yang lebih baru. Laporan ini matang hanya setelah pipeline selesai memproses hingga end_at + 24 hours. Kandidat harus mendiskusikan kedatangan data yang terlambat, indeks, rencana kueri, dan data uji yang menguji definisi metrik secara agresif, alih-alih berhenti pada SQL yang hanya valid secara sintaksis.

Pertanyaan untuk Diklarifikasi Sebelum Menjawab

• Peristiwa mana yang menjangkar pengguna? Jawaban ini menggunakan kunjungan paling awal di dalam interval pelaporan,

bahkan jika pengguna pernah berkunjung sebelum interval tersebut. "Kunjungan pertama sepanjang masa" akan membutuhkan data historis dan filter kohort yang berbeda.

• Apakah funnel ini berurutan? Ya. Pendaftaran harus terjadi setelah kunjungan jangkar, dan pembelian harus terjadi setelah

pendaftaran yang dipilih. Keberadaan peristiwa tanpa urutan adalah metrik yang berbeda.

• Di mana jendela waktu dimulai dan berakhir? Dimulai pada kunjungan jangkar dan ditutup secara inklusif pada

visit_time + 24 hours. Jendela ini tidak dimulai ulang setelah pendaftaran.

• Bisakah langkah-langkah selanjutnya jatuh di luar end_at? Ya, asalkan berada dalam jendela 24 jam pengguna.

end_at memilih kunjungan jangkar; ini tidak memotong peluang konversi.

• Bagaimana timestamp yang sama diurutkan? Bandingkan event_time terlebih dahulu dan event_id kedua, menggunakan

jaminan yang dinyatakan oleh produsen. Jika tidak ada urutan yang dapat dipercaya, langkah-langkah pada waktu yang sama bersifat ambigu.

• Penyebut mana yang mendefinisikan setiap rasio? Keduanya menggunakan visited_users. Rasio pembelian langkah-ke-langkah akan

membagi pembeli dengan pengguna yang mendaftar dan harus memiliki nama kolom yang berbeda.

• Apa yang dikembalikan oleh input kosong? Jumlah bernilai nol; rasio bernilai null karena tidak ada penyebut

yang bermakna. NULLIF mencegah pembagian dengan nol.

• Kapan laporan dinyatakan final? Hanya setelah watermark kelengkapan data mencakup seluruh peluang 24 jam

setiap anggota kohort dan kebijakan data terlambat telah diterapkan.

Kerangka Jawaban 30 Detik

"Pertama, saya akan membuat kohort menjadi satu baris per pengguna dengan meranking kunjungan dalam interval pelaporan setengah terbuka dan mempertahankan peringkat satu. Untuk setiap jangkar, pencarian left lateral menemukan pendaftaran paling awal yang (event_time, event_id)-nya setelah kunjungan dan waktunya dalam 24 jam. Pencarian left lateral kedua melakukan hal yang sama untuk pembelian, tetapi dimulai setelah pendaftaran yang dipilih sembari mempertahankan batas waktu 24 jam awal. Hal tersebut menghasilkan satu baris progres per pengguna yang berkunjung. Saya kemudian dapat menggunakan hitungan terfilter untuk dua tahap yang dicapai dan membagi keduanya dengan jumlah kunjungan. Saya akan menguji peristiwa terbalik, duplikat, timestamp yang sama, batas persis 24 jam, kunjungan berulang, kohort kosong, dan kematangan laporan."

Pembahasan Mendalam Langkah-demi-Langkah

Kohort didahulukan. Memfilter sebelum ROW_NUMBER() berarti "kunjungan paling awal dalam interval pelaporan," persis sesuai petunjuk. Menambahkan event_id ke urutan window membuat jangkar yang dipilih stabil ketika kunjungan berbagi timestamp 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;

Kueri utama menggunakan dua pencarian lateral yang dependen. PostgreSQL mengevaluasi subkueri lateral dengan nilai dari baris-baris di sebelah kirinya. LEFT JOIN LATERAL mempertahankan baris kohort ketika tidak ada peristiwa yang memenuhi syarat ditemukan, yang mana merupakan hal yang tepat dibutuhkan funnel: pengunjung yang tidak pernah mendaftar tetap berada di 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;

Pencarian pendaftaran membuktikan tiga properti secara bersamaan: pengguna yang sama, urutan tupel ketat setelah jangkar, dan inklusi dalam jendela 24 jam jangkar. Pengurutan ditambah LIMIT 1 memilih satu peristiwa deterministik. Pencarian pembelian bergantung pada pendaftaran yang dipilih tersebut, sehingga pembelian sebelum pendaftaran diabaikan sementara pembelian valid yang lebih baru tetap memenuhi syarat. Batas waktunya masih merujuk ke c.visit_time; jika tidak, kueri secara tidak sengaja akan memberikan waktu hingga 48 jam.

Pilihan greedy ini benar untuk funnel berurutan tiga langkah yang tetap. Memilih pendaftaran valid yang paling awal tidak dapat mengeliminasi pembelian yang mungkin diizinkan oleh pendaftaran yang lebih baru: setiap pembelian setelah pendaftaran yang lebih baru juga terjadi setelah pendaftaran yang lebih awal, dan batas waktu bersama tidak berubah. Argumen yang sama berlaku untuk memilih pembelian valid yang paling awal. Invarian setelah setiap pencarian adalah bahwa rantai yang dipilih valid dan menyisakan rentang sisa terbesar yang memungkinkan dari jendela tersebut.

CTE progress memiliki tepat satu baris per pengguna kohort karena setiap pencarian lateral mengembalikan paling banyak satu baris. Oleh karena itu, agregat terfilter menghitung pengguna tanpa memerlukan DISTINCT. Jumlah pembelian tidak dapat melebihi jumlah pendaftaran, dan jumlah pendaftaran tidak dapat melebihi jumlah kunjungan. Ketimpangan tersebut merupakan asersi tingkat hasil yang berguna.

Hindari pola minimum independen yang menggoda:

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

Untuk urutan visit(09:00), purchase(09:05), signup(09:10), purchase(09:20), nilai minimum independen memilih pembelian 09:05 dan mungkin menolak pengguna tersebut. Kueri dependen memilih pendaftaran 09:10 dan kemudian pembelian 09:20. Nilai minimum kondisional hanya dapat bekerja jika setiap minimum dibatasi oleh langkah terpilih sebelumnya, yang merupakan kesulitan utama di sini.

Dua indeks merupakan titik awal yang masuk akal:

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 mendukung rentang nama peristiwa dan waktu yang digunakan untuk membangun kohort. Indeks kedua mendukung pemeriksaan langkah per pengguna yang berulang. Indeks-indeks ini menambah penyimpanan dan write amplification, dan nilai sebenarnya bergantung pada selektivitas, pengelompokan (clustering), ukuran tabel, dan rencana yang dipilih PostgreSQL. Validasi dengan EXPLAIN (ANALYZE, BUFFERS) pada data representatif. Untuk laporan berskala besar yang sering dijalankan, model peristiwa dengan bidang urutan yang stabil atau tabel perjalanan pengguna yang dipelihara secara inkremental mungkin lebih tepat, tetapi harus mempertahankan kontrak kohort dan jendela waktu yang sama.

Data uji adversarial yang ringkas harus mencakup kasus-kasus berikut:

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 siklus hidup pelaporan. Jika end_at adalah tengah malam 1 Juli, pengunjung pada 23:59 tanggal 30 Juni dapat mengonversi hingga 23:59 tanggal 1 Juli. Eksekusi pada 1 Juli tengah malam belum lengkap. Gunakan watermark penyerapan (ingestion), bukan jam dinding, dan hitung ulang kohort yang terpengaruh saat peristiwa yang terlambat tiba.

Contoh Jawaban Berkualitas Tinggi

"Saya akan menyatakan aturan atribusi sebelum menulis SQL: satu peluang funnel per pengguna, dijangkarkan pada kunjungan paling awal mereka di dalam interval pelaporan. Intervalnya setengah terbuka, langkah-langkah selanjutnya dapat terjadi setelah akhirnya, dan pendaftaran serta pembelian harus masuk dalam 24 jam dari jangkar. Timestamp yang sama diurutkan berdasarkan urutan ID peristiwa yang dijamin.

Pertama, saya memfilter kunjungan ke dalam interval, merankingnya berdasarkan (event_time, event_id) per pengguna, dan mempertahankan peringkat satu. Itu memberikan granularitas penyebut yang benar. Untuk setiap jangkar, saya menggunakan subkueri left lateral yang diurutkan berdasarkan tupel yang sama untuk mengambil pendaftaran valid paling awal. Subkueri left lateral kedua merujuk ke pendaftaran yang dipilih dan mengambil pembelian valid paling awal, sembari menjaga batas waktu tetap terikat pada kunjungan. Karena setiap pencarian memiliki LIMIT 1, relasi progres tetap satu baris per pengunjung. Langkah yang hilang tetap null alih-alih menghapus pengguna.

Agregat akhir menghitung semua baris progres, lalu menggunakan hitungan terfilter untuk timestamp pendaftaran dan pembelian yang tidak null. Kedua rasio membagi dengan jumlah kohort kunjungan dan menggunakan NULLIF untuk input kosong. Saya akan mengasertasikan purchased_users <= signed_up_users <= visited_users, memeriksa sampel rantai untuk setiap tahap, dan secara khusus menguji peristiwa terbalik, langkah berulang, timestamp yang sama, dan kedua sisi batas 24 jam.

Untuk produksi, saya tidak akan menyatakan kohort terbaru selesai sampai watermark penyerapan mencakup jendela peluang penuhnya. Saya akan membandingkan rencana kueri dengan dan tanpa indeks yang mendukung pemindaian jangkar dan pencarian langkah per pengguna. Jika timestamp dan ID peristiwa tidak mewakili urutan yang dapat dipercaya, saya akan berhenti dan memperbaiki kontrak peristiwa karena kueri tidak dapat memulihkan kausalitas yang hilang."

Kesalahan Umum

• Hanya memeriksa keberadaan peristiwa. Tiga flag peristiwa dapat menghitung purchase → visit → signup. Funnel

membutuhkan rantai berurutan yang eksplisit.

• Mengambil timestamp pertama yang independen. Pembelian paling awal dapat mendahului pendaftaran yang dipilih bahkan

ketika pembelian berikutnya menyelesaikan perjalanan yang valid.

• Menjangkar pada setiap kunjungan secara tidak sengaja. Hal itu mengubah granularitas dari satu peluang pengguna menjadi satu

peluang kunjungan dan dapat menghitung atau mengatribusikan pengguna secara berbeda.

• Hanya menggunakan event_time untuk pengurutan. Timestamp yang sama membuat hasil tidak stabil. Gunakan kunci

urutan yang dapat dipercaya atau akui bahwa urutannya tidak dapat diketahui.

• Memulai ulang jendela waktu di setiap langkah. Memberikan pendaftaran 24 jam dan pembelian 24 jam lagi melanggar

kontrak 24 jam berbasis jangkar.

• Memfilter langkah-langkah selanjutnya dengan end_at. Itu memperpendek peluang bagi pengunjung di dekat batas pelaporan.

Ambil peristiwa selanjutnya hingga batas waktu masing-masing jangkar.

• Menggunakan inner lateral join. Pengunjung tanpa pendaftaran akan hilang, sehingga melipatgandakan rasio konversi secara semu.
• Menghitung baris peristiwa mentah. Percobaan ulang dan tindakan berulang dapat membuat hitungan tahap melebihi ukuran kohort.
• Membagi dengan langkah sebelumnya secara tidak sengaja. Itu menghitung rasio konversi langkah, bukan rasio

kumulatif yang diminta dari kunjungan.

• Mempublikasikan kohort yang belum matang. Tidak adanya peristiwa lanjutan bukanlah bukti drop-off sampai

seluruh jendela waktu tercakup oleh data yang andal.

Pertanyaan Lanjutan dan Tanggapan

Bagaimana jika kunjungan mana pun dapat memulai perjalanan yang valid?

Granularitasnya berubah. Buat kandidat jangkar untuk setiap kunjungan, cocokkan rantai yang valid dari masing-masing kunjungan, lalu terapkan aturan atribusi seperti perjalanan selesai paling awal per pengguna. Jangan hanya mengganti CTE jangkar: jendela yang tumpang tindih dapat bersaing untuk peristiwa lanjutan yang sama, sehingga kontrak produk harus menyatakan apakah penggunaan kembali diizinkan.

Bagaimana jika setiap langkah harus berbagi sesi atau produk yang sama?

Bawa kunci korelasi dari jangkar dan wajibkan kunci tersebut di kedua pencarian lateral. Granularitasnya menjadi pengguna-sesi atau pengguna-produk, bukan pengguna saja. Tentukan perilaku kunci null dan apakah kunci tersebut bersifat immutable sebelum bergantung pada join.

Bagaimana Anda mendukung sepuluh langkah funnel?

Sepuluh lateral join yang ditulis manual sulit untuk diaudit. Bergantung pada basis datanya, gunakan SQL rekursif, fungsi funnel atau urutan bawaan (native), atau proses prapemrosesan yang menyusuri peristiwa berurutan sebagai state machine. Pertahankan invarian yang sama: satu jangkar yang terdefinisi, urutan langkah yang monoton, satu batas waktu bersama, dan aturan atribusi yang dapat diaudit.

Bagaimana jika peristiwa tiba terlambat atau tidak berurutan?

Pisahkan waktu peristiwa dari waktu penyerapan (ingestion), publikasikan watermark kelengkapan, dan hitung ulang kohort yang jendelanya menerima data terlambat. Urutan waktu peristiwa masih dapat dihitung setelah kedatangan, tetapi laporannya bersifat sementara sampai interval keterlambatan yang disepakati telah berlalu.

Bisakah window function menyelesaikan seluruh masalah ini?

Bisa, misalnya dengan memindai aliran data terurut setiap pengguna dan membawa state, tetapi LAG saja tidak cukup karena peristiwa yang tidak relevan dan duplikat dapat berada di antara langkah-langkah. Solusi lateral membuat ketergantungan antara langkah-langkah yang dipilih menjadi eksplisit. Pilih formulasi lain hanya jika transisi state dan semantik atribusinya sama jelasnya serta rencana kueri terukurnya lebih baik.