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

กำหนด Cohort จากการกระทำแรก

กำหนด Cohort ให้ผู้ใช้แต่ละคนตามวันที่เกิดเหตุการณ์แรก

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

เหตุใดการแบ่งกลุ่มจึงมักปรากฏในการสัมภาษณ์

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

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

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

ตารางต้นทาง

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

  • user_id — ผู้ที่กระทำ
  • event_type — สิ่งที่ทำ
  • event_at — เวลาที่เกิดขึ้นในรูปค่าการประทับเวลา

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

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

การกระทำแรก = MIN ของการประทับเวลา

ขั้นตอนหลักนั้นเรียบง่าย: จัดกลุ่มด้วย user_id แล้วเลือก MIN(event_at) ค่าต่ำสุดนี้คือการกระทำแรกของผู้ใช้ ซึ่งเป็นช่วงเวลาที่ทำให้ผู้ใช้อยู่ในกลุ่มโคฮอร์ต

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

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

การกรองให้เหลือเหตุการณ์ที่ใช้กำหนด

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

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

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

การจัดลงช่วงเวลาของกลุ่มโคฮอร์ต

โดยทั่วไปกลุ่มโคฮอร์ตคือช่วงเวลา ไม่ใช่ค่าการประทับเวลาที่เจาะจง เช่น "กลุ่มโคฮอร์ตเดือน 2024-03" หรือ "สัปดาห์ของวันที่ 2024-03-04" ให้ตัดทอนวันที่ทำกิจกรรมครั้งแรกลงมาเป็นระดับช่วงเวลานั้น

ในโพสต์เกรส ให้ใช้ DATE_TRUNC('month', ...) ใน MySQL คุณอาจใช้ DATE_FORMAT(d, '%Y-%m-01') ส่วนในเซิร์ฟเวอร์ SQL ให้ใช้ DATETRUNC(month, d) หรือคำนวณวันแรกของเดือนขึ้นมา ในการสัมภาษณ์ควรระบุระบบที่ใช้ เพื่อให้เห็นว่าการเลือกไวยากรณ์เป็นไปอย่างมีเหตุผล

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

การห่อหุ้มไว้ใน CTE

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

จากจุดนี้ แบบสอบถามขั้นถัดไปทุกแบบสามารถเชื่อมกลับไปยัง user_cohort เพื่อทราบว่าผู้ใช้แต่ละรายอยู่ในกลุ่มใด

WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT * FROM user_cohort;

ขนาดกลุ่มโคฮอร์ต: การนับสมาชิก

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

ใช้ COUNT(DISTINCT user_id) เพื่อป้องกันความผิดพลาด แม้ว่า CTE จะมีหนึ่งแถวต่อผู้ใช้หนึ่งรายอยู่แล้วก็ตาม เพราะแสดงให้เห็นว่าคุณคำนึงถึงระดับรายละเอียดของข้อมูล จำนวนนี้จะเป็นตัวส่วนสำหรับคำนวณร้อยละการคงอยู่ทั้งหมดในภายหลัง

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

ทางเลือกด้วยฟังก์ชันหน้าต่าง

บางครั้งผู้สัมภาษณ์ขอให้คุณแนบป้ายกำกับกลุ่มโคฮอร์ตกับทุกแถวเหตุการณ์แทนที่จะสร้างตารางที่ยุบรวม ในกรณีนี้ฟังก์ชันหน้าต่างเหมาะอย่างยิ่ง: MIN(event_at) OVER (PARTITION BY user_id) จะคำนวณการกระทำแรกโดยไม่ลบแถวใดออก

วิธีนี้มีประโยชน์เมื่อต้องการทั้งรายละเอียดเหตุการณ์และป้ายกำกับกลุ่มโคฮอร์ตในรอบเดียว ซึ่งเป็นพื้นฐานสำหรับการนับการคงอยู่

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

ข้อควรระวังเรื่องค่าที่เสมอกันและรายการซ้ำ

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

ลองเปรียบเทียบกับแนวทาง ROW_NUMBER() ... ORDER BY event_at ซึ่งจะตัดสินกรณีค่าเท่ากันแบบไม่แน่นอน และคุณต้องเพิ่มตัวตัดสินลำดับที่แน่นอน เช่น event_id เพื่อให้ได้ผลลัพธ์ที่คงที่ การพูดถึงข้อแลกเปลี่ยนนี้โดยไม่ต้องรอให้ผู้สัมภาษณ์ถามจะแสดงให้เห็นว่าคุณมีประสบการณ์ระดับสูง

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

เขตเวลาและขอบเขตของวัน

ประเด็นที่ผู้สัมภาษณ์อาจใช้ทดสอบความเข้าใจคือ การซื้อเวลา 11:30 PM ในนิวยอร์กถือเป็นวันถัดไปใน UTC หากจัดกลุ่มโคฮอร์ตตามวันปฏิทิน เขตเวลา จะเป็นตัวกำหนดว่าผู้ใช้จะถูกจัดอยู่ในกลุ่มใด

คำตอบที่ปลอดภัยคือ จัดเก็บค่าการประทับเวลาเป็น UTC แล้วแปลงเป็นเขตเวลาธุรกิจก่อนตัดทอนเป็นวัน ควรระบุอย่างชัดเจนว่าเขตเวลาใดเป็นตัวกำหนด "วัน" สำหรับตัวชี้วัด เพราะการตัดสินใจเพียงข้อนี้อาจย้ายผู้ใช้หลายพันรายระหว่างกลุ่มโคฮอร์ตได้

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

การตัดผู้ใช้นอกช่วงเวลาเริ่มต้นออก

การวิเคราะห์จริงจะจำกัดกลุ่มโคฮอร์ตให้อยู่ในช่วงวันที่ เช่น "กลุ่มโคฮอร์ตที่เริ่มต้นในไตรมาส 1" ให้กรองวันที่ทำกิจกรรมครั้งแรกที่ผ่านการรวมข้อมูลแล้ว ซึ่งหมายถึงใช้ส่วนคำสั่ง HAVING หรือตัวกรองภายนอก CTE ไม่ใช่ใช้ WHERE กับเหตุการณ์ดิบ

การกรองเหตุการณ์ดิบตามวันที่จะทำให้ผู้ใช้ที่ซื้อครั้งแรกในเดือนธันวาคมแต่ยังทำกิจกรรมในไตรมาส 1 หลุดเข้าไปอยู่ในกลุ่มโคฮอร์ตไตรมาส 1 อย่างไม่ถูกต้อง ควรใช้การกระทำแรกที่คำนวณแล้วเป็นเงื่อนไขเสมอ

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

ตรวจสอบความเข้าใจ

ผู้สัมภาษณ์ถามว่า "จัดผู้ใช้แต่ละรายเข้ากลุ่มโคฮอร์ตตามเดือนที่ซื้อครั้งแรก ผู้ใช้อาจเรียกดูสินค้าก่อนซื้อ" แนวทางใดถูกต้อง

สรุป: การกำหนดกลุ่มโคฮอร์ต

ประเด็นสำคัญสำหรับคำถามสัมภาษณ์เรื่องการกำหนดกลุ่มโคฮอร์ต:

  • กลุ่มโคฮอร์ตจัดกลุ่มผู้ใช้ตามการกระทำแรกที่ตรงเงื่อนไข
  • คำนวณด้วย MIN(event_at) หลังกรองให้เหลือเหตุการณ์ที่ใช้กำหนดใน WHERE
  • จัดลงช่วงเวลาด้วย DATE_TRUNC (หรือรูปแบบที่เทียบเท่าตามระบบ)
  • ห่อหุ้มการกำหนดกลุ่มไว้ใน CTE เพื่อนำกลับมาใช้ซ้ำ โดย COUNT(DISTINCT user_id) จะให้ขนาดกลุ่มโคฮอร์ต
  • ระวังขอบเขตวันตามเขตเวลา และจำกัดช่วงวันที่ด้วยการกระทำแรกที่คำนวณแล้ว ห้ามใช้เหตุการณ์ดิบเป็นเงื่อนไข

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

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

บทเรียน “กำหนด Cohort จากการกระทำแรก” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “กำหนด Cohort จากการกระทำแรก”

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

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

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

บทเรียน “กำหนด Cohort จากการกระทำแรก” ใช้เวลานานแค่ไหน

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

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

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

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

  1. กำหนด Cohort จากการกระทำแรก
  2. สร้างเมทริกซ์การรักษาผู้ใช้
  3. การรักษาผู้ใช้ในวันที่ N และแบบเลื่อนต่อเนื่อง
  4. คำสั่งค้นหาการเลิกใช้และการกลับมาใช้งาน
← กลับไปที่ Coding Interview Prep