SQL Interview Prep · Pelajaran

Membangun Corong Multi-Langkah

Menghitung pengguna yang mencapai setiap langkah berurutan dan menghitung tingkat konversi per langkah.

Membangun Corong Multi-Langkah adalah pelajaran SQL Interview Prep gratis di CoddyKit. Ini adalah pelajaran 1 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 yang Sebenarnya Diuji oleh Pertanyaan tentang Corong

Ketika pewawancara mengatakan "buatkan corong pendaftaran", mereka menguji apakah Anda dapat menghitung pengguna unik yang mencapai setiap langkah berurutan dan menyatakan penurunan di antara langkah-langkah tersebut.

Corong memiliki tahapan seperti visit -> signup -> activate -> purchase. Hasil yang biasanya diminta adalah satu baris untuk setiap langkah, yang berisi jumlah pengguna dan tingkat konversi.

Hitung pengguna, bukan peristiwa (jika satu pengguna memicu suatu peristiwa dua kali, pengguna tersebut tetap dihitung satu kali).
Langkah-langkahnya berurutan; mencapai langkah 3 berarti pengguna tersebut telah melewati langkah 1 dan 2.

Tabel Peristiwa yang Akan Diberikan kepada Anda

Hampir setiap pertanyaan tentang corong memberi Anda satu tabel peristiwa dalam format panjang. Bayangkan strukturnya:

user_id yang melakukan tindakan
event_name seperti 'kunjungan', 'pendaftaran', 'pembelian'
event_time berupa stempel waktu

Satu baris untuk setiap tindakan. Tugas Anda adalah mengubahnya menjadi hitungan langkah demi langkah. Selalu pastikan nama peristiwa yang tepat kepada pewawancara sebelum menulis SQL.

CREATE TABLE events (
  user_id    INT,
  event_name VARCHAR(50),
  event_time TIMESTAMP
);

Menghitung Pengguna pada Satu Langkah

Mulailah dari yang sederhana: berapa banyak pengguna unik yang mencapai satu langkah? Gunakan COUNT(DISTINCT user_id) dengan penyaring berdasarkan nama peristiwa.

Ini merupakan dasar bagi setiap corong. Jika Anda dapat menghitung satu langkah dengan rapi, Anda dapat menghitung semuanya.

SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';

Agregasi Bersyarat untuk Semua Langkah

Jawaban wawancara yang rapi menghitung setiap langkah dalam satu pemindaian menggunakan agregasi bersyarat: sebuah CASE di dalam COUNT(DISTINCT ...).

Untuk setiap langkah, hitung pengguna unik yang peristiwanya sesuai dengan langkah tersebut. Satu pemindaian, satu baris berisi total setiap langkah.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'visit'    THEN user_id END) AS step1_visit,
  COUNT(DISTINCT CASE WHEN event_name = 'signup'   THEN user_id END) AS step2_signup,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;

Kesalahan Tersembunyi: Langkah-Langkah Tidak Berurutan

Kueri yang baru saja Anda lihat memiliki jebakan yang disukai pewawancara. Kueri tersebut menghitung siapa pun yang melakukan 'pembelian', meskipun mereka tidak pernah melakukan kunjungan atau pendaftaran dalam data.

Corong yang sebenarnya mengharuskan setiap langkah berikutnya menjadi himpunan bagian dari langkah sebelumnya. Menghitung peristiwa secara terpisah dapat menghasilkan langkah 3 yang lebih besar daripada langkah 2, sesuatu yang secara logis mustahil bagi sebuah corong.

Perbaikannya adalah mengaitkan langkah-langkah setiap pengguna, biasanya dengan meringkasnya menjadi satu baris per pengguna terlebih dahulu.

Satu Baris per Pengguna dengan Penanda

Pola yang andal adalah meringkas log peristiwa menjadi satu baris per pengguna, dengan penanda bernilai benar/salah (sebagai 0/1) yang menunjukkan apakah mereka pernah melakukan setiap langkah. MAX(CASE ...) mengubah log panjang menjadi ringkasan lebar untuk setiap pengguna.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_steps;

Memastikan Urutan Langkah

Sekarang pastikan aturan corong diterapkan: seorang pengguna hanya dihitung untuk langkah N jika mereka juga melakukan setiap langkah sebelumnya. Mencapai 'pembelian' hanya berarti jika mereka juga melakukan kunjungan dan pendaftaran.

Jumlahkan penanda dengan menggabungkan semua syarat prasyarat sehingga setiap langkah benar-benar menjadi himpunan bagian dari langkah sebelumnya.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(did_visit)                                       AS step1_visit,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END)                       AS step2_signup,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END)  AS step3_purchase
FROM user_steps;

Mengubah Hitungan Menjadi Hasil Panjang yang Rapi

Pewawancara sering lebih menyukai satu baris untuk setiap langkah daripada satu baris lebar. Ubah total berbentuk lebar menjadi format panjang dengan UNION ALL sederhana, lalu sertakan nomor langkah untuk pengurutan.

Dengan begitu, perhitungan tingkat konversi dan pembuatan grafik akan jauh lebih mudah pada langkah berikutnya.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;

Tingkat Konversi dari Satu Langkah ke Langkah Berikutnya

Ada dua tingkat yang penting, dan pewawancara akan menanyakan yang Anda maksud:

Konversi langkah: pengguna pada langkah ini dibagi pengguna pada langkah sebelumnya.
Konversi keseluruhan: pengguna pada langkah ini dibagi pengguna pada puncak corong.

Gunakan LAG untuk mengambil jumlah langkah sebelumnya dalam perhitungan tingkat konversi dari satu langkah ke langkah berikutnya. Ubah nilainya menjadi desimal agar Anda tidak mendapatkan pembagian bilangan bulat.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;

Konversi Keseluruhan dari Puncak

Untuk konversi keseluruhan, bagi setiap langkah dengan jumlah pada langkah pertama. FIRST_VALUE pada corong yang telah diurutkan menetapkan angka puncak tersebut untuk setiap baris.

Selalu sebutkan kepada pewawancara bahwa Anda mencegah pembagian bilangan bulat dengan mengalikan menggunakan 100.0.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;

Menyatukan Seluruh Corong

Inilah jawaban menyeluruh yang diharapkan pewawancara: ringkas menjadi penanda per pengguna, pastikan urutannya, ubah ke format panjang, lalu hitung kedua tingkat konversi. Jelaskan dengan lantang sambil menyebutkan tujuan setiap CTE.

Struktur ini dapat diperluas: tambahkan satu langkah dengan menambahkan satu penanda dan satu baris UNION ALL.

WITH user_steps AS (
  SELECT user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS s1,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS s2,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
  FROM events GROUP BY user_id
),
totals AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
  SELECT 2, 'signup',   SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
  SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;

Pemeriksaan Cepat

Seorang pewawancara melihat bahwa corong Anda menunjukkan 95 pengguna pada 'pembelian', tetapi hanya 80 pada 'pendaftaran'. Apa penyebab yang paling mungkin?

Ringkasan: Corong Multi-Langkah

Sekarang Anda dapat membangun corong seperti yang diharapkan dalam wawancara:

Hitung pengguna unik untuk setiap langkah berurutan, jangan pernah menghitung peristiwa mentah.
Ringkas log peristiwa menjadi satu baris per pengguna dengan penanda MAX(CASE ...).
Pastikan urutannya sehingga setiap langkah menjadi himpunan bagian dari langkah sebelumnya.
Hitung konversi dari satu langkah ke langkah berikutnya (LAG) dan konversi keseluruhan (FIRST_VALUE), sambil mencegah pembagian bilangan bulat.

Selanjutnya: pastikan langkah-langkah tersebut benar-benar terjadi dalam urutan yang tepat dan dalam jendela waktu.

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Membangun Corong Multi-Langkah” gratis?

Ya — teks lengkap “Membangun Corong Multi-Langkah” 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 Corong Multi-Langkah”?

Menghitung pengguna yang mencapai setiap langkah berurutan dan menghitung tingkat konversi per langkah. 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 1 dari 4.

Berapa lama pelajaran “Membangun Corong Multi-Langkah” 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

Membangun Corong Multi-Langkah
Kejadian Berurutan dan Jendela Waktu
Penetapan Uji A/B dan Metrik
Peningkatan, Signifikansi, dan Pengaman dalam SQL

← Kembali ke SQL Interview Prep