0Pricing
SQL Interview Prep · บทเรียน

สร้างกระบวนการ Funnel หลายขั้นตอน

นับผู้ใช้ที่ไปถึงแต่ละขั้นตามลำดับ และคำนวณอัตราการเปลี่ยนผ่านในแต่ละขั้น

สร้างกระบวนการ Funnel หลายขั้นตอน เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 1 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คำถามเรื่องกรวยการเปลี่ยนผ่านต้องการทดสอบอะไร

เมื่อผู้สัมภาษณ์พูดว่า "สร้างกรวยการสมัครใช้งานให้ฉัน" ผู้สัมภาษณ์กำลังทดสอบว่าคุณสามารถนับ ผู้ใช้ที่ไม่ซ้ำกัน ซึ่งไปถึงแต่ละขั้นตอนตามลำดับ และแสดงจำนวนผู้ใช้ที่หลุดออกระหว่างขั้นตอนได้หรือไม่

กรวยการเปลี่ยนผ่านมีขั้นต่าง ๆ เช่น visit -> signup -> activate -> purchase โดยทั่วไปผลลัพธ์ที่ต้องส่งคือหนึ่งแถวต่อขั้นตอน พร้อมจำนวนผู้ใช้และอัตราการเปลี่ยนผ่าน

  • นับ ผู้ใช้ ไม่ใช่เหตุการณ์ (ผู้ใช้คนเดียวที่ทำให้เกิดเหตุการณ์สองครั้งก็ยังนับเป็นผู้ใช้คนเดียว)
  • ขั้นตอนต่าง ๆ มีลำดับ การไปถึงขั้นตอนที่ 3 หมายความว่าผู้ใช้นั้นผ่านขั้นตอนที่ 1 และ 2 มาแล้ว

ตารางเหตุการณ์ที่คุณจะได้รับ

คำถามเรื่องกรวยการเปลี่ยนผ่านแทบทุกข้อจะให้ตาราง เหตุการณ์ เพียงตารางเดียวในรูปแบบยาว ลองนึกภาพโครงสร้างดังนี้:

  • user_id ผู้ที่ทำการกระทำ
  • event_name เช่น 'การเข้าชม', 'การสมัครใช้งาน', 'การซื้อ'
  • event_time จุดเวลา

หนึ่งแถวต่อหนึ่งการกระทำ หน้าที่ของคุณคือแปลงข้อมูลนี้ให้เป็นจำนวนนับตามขั้นตอน ตรวจสอบชื่อเหตุการณ์ที่แน่นอนกับผู้สัมภาษณ์เสมอก่อนเขียนคำสั่งฐานข้อมูล

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

การนับผู้ใช้ในขั้นตอนเดียว

เริ่มจากสิ่งง่าย ๆ ก่อน: มีผู้ใช้ที่ไม่ซ้ำกันกี่คนไปถึงขั้นตอนเดียว ใช้ COUNT(DISTINCT user_id) พร้อมตัวกรองตามชื่อเหตุการณ์

นี่คือส่วนประกอบพื้นฐานของกรวยการเปลี่ยนผ่านทุกแบบ หากคุณนับหนึ่งขั้นตอนได้อย่างชัดเจน ก็จะนับทุกขั้นตอนได้

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

การรวมแบบมีเงื่อนไขสำหรับทุกขั้นตอน

คำตอบที่สะอาดในการสัมภาษณ์คือการนับ ทุกขั้นตอนในรอบเดียว ด้วยการรวมแบบมีเงื่อนไข โดยใช้ CASE ภายใน COUNT(DISTINCT ...)

สำหรับแต่ละขั้นตอน ให้นับผู้ใช้ที่ไม่ซ้ำกันซึ่งมีเหตุการณ์ตรงกับขั้นตอนนั้น สแกนข้อมูลครั้งเดียวและได้ผลรวมของทุกขั้นตอนในแถวเดียว

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;

ข้อผิดพลาดที่ซ่อนอยู่: ขั้นตอนไม่ได้เรียงตามลำดับ

คำสั่งที่เพิ่งเห็นมี กับดักที่ผู้สัมภาษณ์ชอบใช้ มันนับทุกคนที่ทำให้เกิดเหตุการณ์ 'การซื้อ' แม้ในข้อมูลจะไม่เคยเข้าชมหรือสมัครใช้งานเลย

กรวยการเปลี่ยนผ่านจริงต้องกำหนดให้แต่ละขั้นตอนหลังจากนั้นเป็น เซตย่อย ของขั้นตอนก่อนหน้า การนับเหตุการณ์แยกกันอาจทำให้ขั้นตอนที่ 3 มีจำนวนมากกว่าขั้นตอนที่ 2 ซึ่งเป็นไปไม่ได้ตามตรรกะของกรวยการเปลี่ยนผ่าน

วิธีแก้คือผูกขั้นตอนต่าง ๆ ของผู้ใช้แต่ละคนเข้าด้วยกัน โดยทั่วไปทำได้ด้วยการยุบข้อมูลให้เหลือหนึ่งแถวต่อผู้ใช้ก่อน

หนึ่งแถวต่อผู้ใช้พร้อมตัวบ่งชี้

รูปแบบที่แข็งแรงคือยุบบันทึกเหตุการณ์ให้เหลือ หนึ่งแถวต่อผู้ใช้ พร้อมตัวบ่งชี้ค่าจริง/เท็จในรูป 0/1 ว่าผู้ใช้นั้นเคยทำแต่ละขั้นตอนหรือไม่ MAX(CASE ...) จะเปลี่ยนบันทึกแบบยาวให้เป็นสรุปแบบกว้างสำหรับผู้ใช้แต่ละคน

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;

การบังคับใช้ลำดับขั้นตอน

ตอนนี้ให้บังคับใช้กฎของกรวยการเปลี่ยนผ่าน: ผู้ใช้จะ ถูกนับในขั้นตอนที่ N ก็ต่อเมื่อทำทุกขั้นตอนก่อนหน้านั้นด้วย การไปถึง 'การซื้อ' จะมีความหมายก็ต่อเมื่อผู้ใช้นั้นเข้าชมและสมัครใช้งานด้วย

รวมค่าตัวบ่งชี้โดยเชื่อมเงื่อนไขที่จำเป็นทั้งหมดด้วย AND เพื่อให้แต่ละขั้นตอนเป็นเซตย่อยที่แท้จริงของขั้นตอนก่อนหน้า

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;

เปลี่ยนผลลัพธ์แบบกว้างให้เป็นผลลัพธ์แบบยาวที่เป็นระเบียบ

ผู้สัมภาษณ์มักต้องการหนึ่งแถว ต่อขั้นตอน แทนที่จะเป็นแถวกว้างเพียงแถวเดียว ให้แปลงผลรวมแบบกว้างเป็นรูปแบบยาวด้วย UNION ALL ขนาดเล็ก พร้อมเพิ่มหมายเลขขั้นตอนเพื่อใช้จัดลำดับ

วิธีนี้ทำให้การคำนวณอัตราการเปลี่ยนผ่านและการสร้างแผนภูมิในขั้นตอนถัดไปง่ายขึ้นมาก

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;

อัตราการเปลี่ยนผ่านจากขั้นตอนหนึ่งไปยังขั้นตอนถัดไป

มีอัตราสองแบบที่สำคัญ และผู้สัมภาษณ์จะถามว่าคุณหมายถึงแบบใด:

  • การเปลี่ยนผ่านของขั้นตอน: ผู้ใช้ในขั้นตอนนี้หารด้วยผู้ใช้ในขั้นตอน ก่อนหน้า
  • การเปลี่ยนผ่านโดยรวม: ผู้ใช้ในขั้นตอนนี้หารด้วยจำนวนผู้ใช้ที่ จุดเริ่มต้น ของกรวยการเปลี่ยนผ่าน

ใช้ LAG เพื่อดึงจำนวนของขั้นตอนก่อนหน้ามาคำนวณอัตราการเปลี่ยนผ่านระหว่างขั้นตอน แปลงค่าเป็นเลขทศนิยมเพื่อไม่ให้เกิดการหารจำนวนเต็ม

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;

การเปลี่ยนผ่านโดยรวมจากจุดเริ่มต้น

สำหรับการเปลี่ยนผ่านโดยรวม ให้หารจำนวนผู้ใช้ในแต่ละขั้นตอนด้วยจำนวนผู้ใช้ใน ขั้นตอนแรก FIRST_VALUE ที่ทำงานบนกรวยการเปลี่ยนผ่านซึ่งเรียงตามลำดับจะตรึงตัวเลขจากจุดเริ่มต้นนั้นไว้สำหรับทุกแถว

ควรกล่าวกับผู้สัมภาษณ์เสมอว่าคุณป้องกันการหารจำนวนเต็มด้วยการคูณด้วย 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;

ประกอบกรวยการเปลี่ยนผ่านทั้งหมดเข้าด้วยกัน

นี่คือคำตอบตั้งแต่ต้นจนจบที่ผู้สัมภาษณ์ต้องการ: ยุบข้อมูลเป็นตัวบ่งชี้ต่อผู้ใช้ บังคับใช้ลำดับ แปลงเป็นรูปแบบยาว จากนั้นคำนวณอัตราทั้งสองแบบ อธิบายแต่ละขั้นตอนออกเสียง โดยบอกจุดประสงค์ของ CTE แต่ละรายการ

โครงสร้างนี้ขยายได้ง่าย เพียงเพิ่มขั้นตอนด้วยการเพิ่มตัวบ่งชี้หนึ่งรายการและแถว 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;

ตรวจสอบอย่างรวดเร็ว

ผู้สัมภาษณ์สังเกตว่ากรวยการเปลี่ยนผ่านของคุณมีผู้ใช้ 95 คนในขั้น 'การซื้อ' แต่มีเพียง 80 คนในขั้น 'การสมัครใช้งาน' สาเหตุที่เป็นไปได้มากที่สุดคืออะไร

สรุป: กรวยการเปลี่ยนผ่านหลายขั้นตอน

ตอนนี้คุณสามารถสร้างกรวยการเปลี่ยนผ่านตามที่การสัมภาษณ์คาดหวังได้แล้ว:

  • นับ ผู้ใช้ที่ไม่ซ้ำกัน ในแต่ละขั้นตอนตามลำดับ อย่านับเหตุการณ์ดิบ
  • ยุบปูมเหตุการณ์ให้เหลือ หนึ่งแถวต่อผู้ใช้ พร้อมตัวบ่งชี้จาก MAX(CASE ...)
  • บังคับใช้ลำดับเพื่อให้แต่ละขั้นตอนเป็น เซตย่อย ของขั้นตอนก่อนหน้า
  • คำนวณการเปลี่ยนผ่าน ระหว่างขั้นตอน (LAG) และการเปลี่ยนผ่าน โดยรวม (FIRST_VALUE) พร้อมป้องกันการหารจำนวนเต็ม

ถัดไป: ตรวจสอบให้แน่ใจว่าขั้นตอนเหล่านั้นเกิดขึ้นตามลำดับที่ถูกต้องและอยู่ภายในช่วงเวลาที่กำหนด

คำถามที่พบบ่อย

บทเรียน “สร้างกระบวนการ Funnel หลายขั้นตอน” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “สร้างกระบวนการ Funnel หลายขั้นตอน” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “สร้างกระบวนการ Funnel หลายขั้นตอน”

นับผู้ใช้ที่ไปถึงแต่ละขั้นตามลำดับ และคำนวณอัตราการเปลี่ยนผ่านในแต่ละขั้น คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 1 จากทั้งหมด 4 บทเรียน

บทเรียน “สร้างกระบวนการ Funnel หลายขั้นตอน” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม

ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. สร้างกระบวนการ Funnel หลายขั้นตอน
  2. เหตุการณ์ตามลำดับและช่วงเวลา
  3. การกำหนดกลุ่มการทดสอบ A/B และตัวชี้วัด
  4. ค่าการเพิ่มขึ้น นัยสำคัญ และเกณฑ์ควบคุมใน SQL
← กลับไปที่ SQL Interview Prep