SQL Interview Prep · บทเรียน

คำสั่งค้นหาการเลิกใช้และการกลับมาใช้งาน

ระบุผู้ใช้ที่เลิกใช้งาน และผู้ใช้ที่กลับมาใช้งานอีกครั้งหลังขาดช่วง

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

อีกด้านหนึ่งของการคงอยู่

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

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

การกำหนดการเลิกใช้งานอย่างแม่นยำ

คำว่า "เลิกใช้งาน" ไม่มีความหมายหากไม่ระบุช่วงเวลา คำจำกัดความทั่วไปคือ ผู้ใช้จะ เลิกใช้งาน หาก ไม่มีการใช้งานในช่วง 30 วันที่ผ่านมา เกณฑ์ไม่มีการใช้งาน 30 วันคือทางเลือกทางธุรกิจที่คุณต้องระบุให้ชัดเจน

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

กิจกรรมล่าสุดของผู้ใช้แต่ละคน

พื้นฐานของการเลิกใช้งานจากช่องว่างของกิจกรรมคือ เหตุการณ์ล่าสุด ของผู้ใช้แต่ละคน ให้จัดกลุ่มตามผู้ใช้และหา MAX ของวันที่เกิดเหตุการณ์

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

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

คำสั่งค้นหาผู้ใช้ที่เลิกใช้งาน

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

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

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

การนับอัตราการเลิกใช้งาน

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

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

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

การเลิกใช้งานระหว่างช่วงเวลาด้วยตรรกะของเซต

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

คุณสามารถเขียนสิ่งนี้ด้วย EXCEPT การเชื่อมเพื่อตัดสมาชิกออกด้วย LEFT JOIN / IS NULL หรือ NOT EXISTS การเชื่อมเพื่อตัดสมาชิกออกเป็นวิธีที่ใช้ได้กับหลายระบบมากที่สุด และเป็นวิธีที่ผู้สัมภาษณ์มักต้องการเห็นมากที่สุด

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

รูปแบบการเชื่อมแบบตัดออก

คำสั่งค้นหาผู้ใช้ที่เลิกใช้งานในช่วงเวลานี้แบบ การเชื่อมแบบตัดออก: ใช้ LEFT JOIN เพื่อเชื่อมผู้ใช้ที่ใช้งานอยู่ในเดือนนี้เข้ากับผู้ใช้ของเดือนที่แล้ว จากนั้นเก็บเฉพาะแถวที่ค่าที่จับคู่ได้เป็น NULL ผู้ใช้เหล่านี้มีอยู่ในเดือนที่แล้วแต่ไม่มีในเดือนนี้ ซึ่งก็คือผู้ใช้ที่เลิกใช้งาน

NOT EXISTS ก็เป็นคำตอบที่ดีไม่แพ้กันและจัดการกับ NULL ได้อย่างปลอดภัย ควรกล่าวถึงว่า NOT IN อาจมีความเสี่ยงหากชุดข้อมูลด้านในมีค่า NULL ซึ่งเป็นกับดักคลาสสิก

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

นิยามการกลับมาใช้งานอีกครั้ง

การกลับมาใช้งานอีกครั้ง หรือการเปิดใช้งานอีกครั้ง หมายถึงผู้ใช้ที่ เลิกใช้งานแล้ว และกลับมาใช้งานอีกครั้ง ลักษณะสำคัญคือมี ช่วงเว้นว่าง ในเส้นเวลาการใช้งาน: ใช้งานอยู่ จากนั้นเงียบหายไปนานเกินเกณฑ์การเลิกใช้งาน แล้วกลับมาใช้งานอีกครั้ง

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

ตรวจหาช่วงเว้นว่างด้วย LAG

วิธีที่งดงามในการค้นหาผู้ใช้ที่กลับมาใช้งานอีกครั้งคือ ฟังก์ชันหน้าต่าง LAG: สำหรับแต่ละช่วงเวลาที่ผู้ใช้แต่ละคนมีกิจกรรม ให้ดูช่วงเวลาที่ใช้งานอยู่ก่อนหน้า หากช่วงห่างระหว่างสองช่วงเวลานี้ยาวเกินเกณฑ์ ช่วงเวลาปัจจุบันก็ถือเป็นการกลับมาใช้งานอีกครั้ง

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

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

ผู้ใช้ใหม่ เทียบกับผู้ใช้ที่กลับมาใช้งานและผู้ใช้ที่ยังใช้งานอยู่

คำสั่งจัดประเภทกิจกรรมที่สมบูรณ์จะกำกับป้ายผู้ใช้ทุกคนที่ใช้งานอยู่ในช่วงเวลานี้เป็นประเภทใดประเภทหนึ่ง ได้แก่ ผู้ใช้ใหม่ (ไม่มีกิจกรรมก่อนหน้า) ผู้ใช้ที่ยังใช้งานอยู่ (ใช้งานอยู่ในช่วงเวลาก่อนหน้าด้วย) หรือ ผู้ใช้ที่กลับมาใช้งานอีกครั้ง (มีกิจกรรมก่อนหน้าแต่มีช่วงเว้นว่าง) ค่า prev_month จาก LAG เป็นตัวกำหนดทั้งสามประเภท

prev_month IS NULL → ผู้ใช้ใหม่
prev_month = active_month - 1 → ผู้ใช้ที่ยังใช้งานอยู่
กรณีอื่น (มีช่วงเว้นว่าง) → ผู้ใช้ที่กลับมาใช้งานอีกครั้ง

การสร้างการแจกแจงเช่นนี้เป็นคำตอบที่แข็งแรงและสมบูรณ์

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

กับดัก NULL ของ NOT IN

กับดักสำคัญประการสุดท้าย หากเขียนการเลิกใช้งานเป็น WHERE user_id NOT IN (SELECT user_id FROM this_month) แล้วแบบสอบถามย่อยนั้นคืนค่า NULL มาแม้เพียงหนึ่งค่า ผลลัพธ์ทั้งหมดจะว่างเปล่า เพราะ NOT IN จะประเมินเป็น UNKNOWN เมื่อเปรียบเทียบกับ NULL

ควรใช้ NOT EXISTS หรือการเชื่อมแบบตัดออกด้วย LEFT JOIN / IS NULL ซึ่งทำงานได้อย่างถูกต้องเมื่อมี NULL การชี้ให้เห็นความแตกต่างนี้โดยไม่ต้องมีผู้ถามเป็นสัญญาณที่น่าเชื่อถือว่าผู้สมัครมีความเข้าใจระดับอาวุโสในการสัมภาษณ์ด้านการรักษาผู้ใช้

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

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

คุณต้องการหาผู้ใช้ที่ใช้งานอยู่ในเดือนที่แล้วแต่ไม่ได้ใช้งานในเดือนนี้ เพื่อนร่วมทีมเขียนว่า WHERE user_id NOT IN (SELECT user_id FROM this_month) แต่ผลลัพธ์กลับไม่มีแถวเลย ทั้งที่เห็นได้ชัดว่ามีผู้ใช้บางส่วนเลิกใช้งานแล้ว วิธีแก้ไขใดปลอดภัยที่สุด

สรุป: การเลิกใช้งานและการกลับมาใช้งานอีกครั้ง

สาระสำคัญของการเลิกใช้งานและการกลับมาใช้งานอีกครั้ง:

กำหนดการเลิกใช้งานด้วย เกณฑ์การไม่มีการใช้งาน (เช่น ไม่มีกิจกรรมเป็นเวลา 30 วัน) หรือการเปลี่ยนสถานะการสมัครใช้งาน โดยต้องระบุให้ชัดเจนว่าจะใช้แบบใด
คำนวณ MAX(การใช้งานครั้งล่าสุด) ของผู้ใช้แต่ละคน แล้วเปรียบเทียบกับ CURRENT_DATE - threshold
การเลิกใช้งานเมื่อเปรียบเทียบระหว่างช่วงเวลาเป็น ผลต่างของเซต: ใช้ EXCEPT, NOT EXISTS หรือการเชื่อมแบบตัดออกด้วย LEFT JOIN / IS NULL
การกลับมาใช้งานอีกครั้ง คือช่วงเว้นว่างในเส้นเวลา ตรวจหาได้ด้วย LAG เพื่อจัดประเภทผู้ใช้เป็นผู้ใช้ใหม่ / ผู้ใช้ที่ยังใช้งานอยู่ / ผู้ใช้ที่กลับมาใช้งานอีกครั้ง
หลีกเลี่ยง NOT IN เมื่ออาจมี NULL เพราะจะทำให้ผลลัพธ์ว่างเปล่าโดยไม่แจ้งเตือน

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

บทเรียน “คำสั่งค้นหาการเลิกใช้และการกลับมาใช้งาน” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “คำสั่งค้นหาการเลิกใช้และการกลับมาใช้งาน”

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

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

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

บทเรียน “คำสั่งค้นหาการเลิกใช้และการกลับมาใช้งาน” ใช้เวลานานแค่ไหน

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

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

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

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

กำหนด Cohort จากการกระทำแรก
สร้างเมทริกซ์การรักษาผู้ใช้
การรักษาผู้ใช้ในวันที่ N และแบบเลื่อนต่อเนื่อง
คำสั่งค้นหาการเลิกใช้และการกลับมาใช้งาน

← กลับไปที่ SQL Interview Prep