SQL Interview Prep · درس

إنشاء مصفوفة الاحتفاظ

حساب المستخدمين النشطين حسب المجموعة والفترة المنقضية لإنشاء جدول احتفاظ

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

ماهيّة مصفوفة الاحتفاظ

الخطوة التالية بعد تعريف الفوج هي مصفوفة الاحتفاظ الشهيرة: تمثل الصفوف الأفواج، وتمثل الأعمدة إزاحات الفترات، مثل الشهر 0 و1 و2، وتحصي كل خلية عدد أعضاء ذلك الفوج الذين ظلوا نشطين عند تلك الإزاحة.

يحبّ المحاورون هذا السؤال لأنه يجبرك على الجمع بين إسناد المستخدمين إلى الأفواج، والربط مجددًا بالنشاط، وحساب الفرق بين الفترات، ثم تحويل النتائج إلى مصفوفة. وهو الاستعلام الأكثر تمثيلًا لتحليلات المنتجات.

المدخلان

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

لذلك خطّط للاستعلام على النحو التالي: CTE للفوج، ثم CTE للنشاط تسرد الأشهر التي كان كل مستخدم نشطًا فيها، ثم اربط بينهما.

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

سرد الفترات النشطة

يجيب CTE النشاط عن السؤال: «في أي أشهر كان كل مستخدم نشطًا؟» اقتطع كل حدث إلى الشهر، وأزل التكرارات باستخدام DISTINCT أو GROUP BY، حتى ينتج عن مستخدم نشط 40 مرة في مارس صف واحد لشهر مارس.

هذه القائمة، التي تحتوي على صف لكل مستخدم ولكل شهر، هي ما تربطه بالفوج لقياس الاستمرار عبر الإزاحات.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

حساب إزاحة الفترة

جوهر المصفوفة هو رقم الفترة: كم شهرًا مضى منذ بداية الفوج حتى وقوع نشاط معين؟ اطرح شهر الفوج من شهر النشاط.

في Postgres، تتمثل إحدى الطرق الواضحة في حساب عدد الأشهر الكاملة بين التاريخين. أما الصيغة القابلة للنقل بين الأنظمة فتضرب فرق السنوات في 12 ثم تضيف فرق الأشهر؛ كما توفر محركات كثيرة دوال مساعدة لهذا الغرض. وتعني الإزاحة 0 شهر بداية الفوج نفسه.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

ضمّ الفوج إلى النشاط

اربط CTE الفوج بـ CTE النشاط باستخدام user_id. يوضّح كل صف ناتج أن هذا المستخدم، المنتمي إلى الفوج X، كان نشطًا عند الإزاحة N. ويشكّل عدّ المستخدمين المميّزين لكل زوج (الفوج، الإزاحة) المصفوفة بصيغتها الطويلة.

بما أن كل عضو في الفوج يكون نشطًا في شهر بدايته، فمن المفترض أن تساوي الإزاحة 0 حجم الفوج، وهذا اختبار سلامة مضمّن ومفيد.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

جدول الاحتفاظ بصيغته الطويلة

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

لاحظ أن تعبير الإزاحة يظهر في كل من SELECT وGROUP BY لأنه محسوب وليس عمودًا مخزّنًا.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

تحويل البيانات إلى أعمدة عريضة

للحصول على الشبكة التقليدية، حوّل الإزاحات إلى أعمدة باستخدام التجميع الشرطي: دالة SUM تتضمن CASE لكل إزاحة. تعمل هذه البنية القابلة للنقل في كل لهجات SQL من دون صياغة PIVOT خاصة.

يُرجع كل CASE القيمة 1 عندما يطابق row's period_number ذلك العمود، ولذلك يحسب SUM عدد المستخدمين المحتفَظ بهم عند تلك الإزاحة.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

من الأعداد إلى معدلات الاحتفاظ

يريد المحاوِرون عادةً نِسَبًا مئوية، لا أعدادًا خامًا. اقسم عدد المستخدمين المحتفَظ بهم عند كل إزاحة على حجم الفوج (عند الإزاحة 0). حوّل القيمة إلى float أو اضربها في 1.0 لتجنّب القسمة الصحيحة، وهي أكثر الأخطاء الصامتة شيوعًا هنا.

والنتيجة هي منحنى احتفاظ: يبدأ عند 100% في الشهر 0 ثم ينخفض تدريجيًا نحو مستوى مستقر. وهذا المستوى المستقر هو المقياس الذي يهتم به أصحاب المصلحة فعليًا.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

فخ القسمة الصحيحة

هذا من الأخطاء الشائعة المؤكدة في المقابلات: في معظم المحركات تساوي 120 / 500 القيمة 0، لا 0.24، لأن المعاملين عددان صحيحان. وهكذا تظهر نسب الاحتفاظ كلها أصفارًا من دون تنبيه.

أصلح ذلك بجعل أحد الطرفين قيمة عددية: اضرب في 100.0، أو حوّل أحد المعاملين إلى NUMERIC باستخدام CAST، أو اقسم على NULLIF(size, 0) للحماية أيضًا من الفوج الفارغ. وذكر أن "and NULLIF prevents divide-by-zero" يمنحك نقاطًا إضافية.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

ملء الإزاحات المفقودة بالصفر

إذا لم يكن للفوج أي مستخدمين محتفَظ بهم عند الإزاحة 2، فلن يُنتج JOIN أي صف، فتظهر فجوة في المصفوفة. ولإظهار 0 صراحةً، أنشئ الشبكة الكاملة لتوليفات (الفوج، الإزاحة)، ثم نفّذ LEFT JOIN مع الأعداد.

أنشئ الشبكة بضمّ الأفواج إلى قائمة أرقام/إزاحات باستخدام CROSS JOIN، ثم استخدم coalesce لتحويل الأعداد المفقودة إلى صفر. سيقدّر المحاوِرون ملاحظتك لهذه الفجوة.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

الشكل المثلث وانحياز الحداثة

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

لذلك يكون حساب متوسط عمود عبر الأفواج منحازًا نحو الأفواج الأقدم. اذكر أنك ستعرض المثلث كما هو بصدق، أو ستقصر المقارنات على الإزاحات التي بلغها كل فوج. هذا الوعي هو ما يميّز المحللين عن كاتبي الاستعلامات.

اختبار سريع

يقسم استعلام الاحتفاظ عدد المستخدمين المحتفَظ بهم على حجم الفوج، لكن كل نسبة تظهر كـ 0 باستثناء الشهر 0. ما السبب الأكثر احتمالًا؟

مراجعة: مصفوفة الاحتفاظ

لبناء مصفوفة احتفاظ في مقابلة:

حدّد لكل مستخدم فترة الفوج، ثم أدرج الفترات النشطة لكل مستخدم بعد إزالة التكرارات.
اربط بينهما واحسب إزاحة الفترة، أي عدد الأشهر بين الفوج والنشاط.
جمّع النتائج بصيغة طويلة باستخدام COUNT(DISTINCT user_id)، ثم حوّلها إلى أعمدة باستخدام CASE إذا كانت الشبكة مطلوبة.
حوّل الأعداد إلى معدلات بحذر، وتجنّب القسمة الصحيحة والقسمة على صفر باستخدام 100.0 وNULLIF.
نفّذ LEFT JOIN مع شبكة مولّدة لملء الخلايا الصفرية، وتذكّر أن المصفوفة مثلثة.

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

هل درس «إنشاء مصفوفة الاحتفاظ» مجاني؟

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

ماذا ستتعلم في «إنشاء مصفوفة الاحتفاظ»؟

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

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

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

كم من الوقت يستغرق درس «إنشاء مصفوفة الاحتفاظ»؟

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

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

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

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

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