SQL Interview Prep · درس

استعلامات التوقف عن الاستخدام والعودة

تحديد المستخدمين الذين غادروا والذين عادوا بعد انقطاع

استعلامات التوقف عن الاستخدام والعودة درس مجاني في SQL Interview Prep على CoddyKit. هذا هو الدرس 4 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 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 لإجراء anti-join، أو NOT EXISTS. ويُعدّ anti-join الأكثر قابلية للنقل، وهو ما يرغب المحاوِرون في رؤيته غالبًا.

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;

تعريف إعادة التنشيط

إعادة التنشيط (وتُعرف أيضًا باسم reactivation) هي حالة مستخدم كان متسرّبًا ثم أصبح نشطًا مرة أخرى. وتتمثل علامتها المميزة في وجود فجوة في خطه الزمني: نشاط، ثم فترة صمت أطول من عتبة التسرّب، ثم نشاط من جديد.

إذًا، المستخدم الذي أُعيد تنشيطه هذا الشهر هو مستخدم نشط الآن، لكنه كان غير نشط في الفترة السابقة، مع وجود نشاط له في فترة أقدم. وهذه هي الصورة المعاكسة للتسرّب.

اكتشاف الفجوات باستخدام LAG

الطريقة الأنيقة لاكتشاف إعادة التنشيط هي استخدام دالة النافذة LAG: بالنسبة إلى كل فترة نشاط لكل مستخدم، انظر إلى فترة النشاط السابقة. إذا تجاوزت الفجوة بينهما العتبة، فهذا يعني أن الفترة الحالية تمثل إعادة تنشيط.

تتجنب LAG الحاجة إلى self-join، كما تجعل الاستعلام واضحًا وسهل القراءة. قسّم البيانات حسب المستخدم، ورتّبها حسب فترة النشاط، ثم قارن كل فترة بسابقتها.

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;

فخ NOT IN مع NULL

إليك مشكلة أخيرة شائعة. إذا كتبت التسرّب بهذه الصورة: 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(last activity)، ثم قارنها بـ CURRENT_DATE - threshold.
التسرّب من فترة إلى أخرى هو فرق مجموعتين: استخدم EXCEPT أو NOT EXISTS أو الربط المضاد باستخدام LEFT JOIN / IS NULL.
إعادة التنشيط هي فجوة في الخط الزمني؛ اكتشفها باستخدام LAG لتصنيف المستخدمين إلى جدد / محتفظ بهم / مُعاد تنشيطهم.
تجنب NOT IN عند احتمال وجود NULL، لأنه يفرغ النتيجة بصمت.

الأسئلة الشائعة

هل درس «استعلامات التوقف عن الاستخدام والعودة» مجاني؟

نعم — نص درس «استعلامات التوقف عن الاستخدام والعودة» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7) وفتح باقي دورة SQL Interview Prep، انتقل إلى CoddyKit PRO. تتضمن دورة SQL Interview Prep 4 دروس في المجموع.

ماذا ستتعلم في «استعلامات التوقف عن الاستخدام والعودة»؟

تحديد المستخدمين الذين غادروا والذين عادوا بعد انقطاع تتمرن على SQL Interview Prep مع أكواد عملية تشغلها مباشرة في المتصفح، ومدرس ذكاء اصطناعي متاح 24/7 يجيب على أسئلتك أثناء عملك.

هل أحتاج إلى خبرة سابقة لأبدأ SQL Interview Prep؟

لا تُشترط خبرة سابقة. SQL Interview Prep على CoddyKit منظم للمبتدئين حتى المتقدمين، لذا يمكنك البدء من هنا أو من البداية والتقدم بسرعتك الخاصة. هذا هو الدرس 4 من أصل 4.

كم من الوقت يستغرق درس «استعلامات التوقف عن الاستخدام والعودة»؟

معظم دروس CoddyKit تستغرق حوالي 5–10 دقائق. كل منها موجز وتفاعلي، لذا تحرز تقدماً مستمراً وتستأنف من حيث توقفت عبر الويب والتطبيق.

هل يمكنني كتابة وتشغيل أكواد في درس SQL Interview Prep هذا؟

نعم. كل درس في SQL Interview Prep يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

جميع الدروس في هذه الدورة

← العودة إلى SQL Interview Prep