SQL Interview Prep · درس

تحديد المجموعة حسب أول إجراء

إسناد كل مستخدم إلى مجموعة استنادًا إلى تاريخ أول حدث له

تحديد المجموعة حسب أول إجراء درس مجاني في SQL Interview Prep على CoddyKit. هذا هو الدرس 1 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7. هذا الدرس جزء من مسار التعلم في SQL Interview Prep، وتقدمك يتزامن عبر الويب وتطبيق CoddyKit. تتضمن دورة SQL 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». اقتطع تاريخ أول إجراء إلى مستوى الفترة المطلوب.

في Postgres استخدم DATE_TRUNC('month', ...). وفي MySQL يمكنك استخدام DATE_FORMAT(d, '%Y-%m-01')؛ أما في SQL Server فاستخدم 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 مساءً في نيويورك تقع في اليوم التالي وفق 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;

استبعاد المستخدمين السابقين للفترة

تقيّد التحليلات الواقعية الفوج بنطاق زمني، مثل «الأفواج التي بدأت في الربع الأول». صفِّ حسب تاريخ أول إجراء المجمّع، ما يعني استخدام عبارة HAVING أو عامل تصفية خارجي على CTE، وليس WHERE على الأحداث الأولية.

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

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) حجم الفوج.
انتبه إلى حد اليوم في المنطقة الزمنية، وقيّد النطاقات الزمنية باستخدام أول إجراء محسوب، وليس الأحداث الأولية مطلقًا.

إذا أتقنت ذلك، فستصبح مصفوفة الاحتفاظ في الدرس التالي مجرد عملية ربط.

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

هل درس «تحديد المجموعة حسب أول إجراء» مجاني؟

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

ماذا ستتعلم في «تحديد المجموعة حسب أول إجراء»؟

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

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

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

كم من الوقت يستغرق درس «تحديد المجموعة حسب أول إجراء»؟

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

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

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

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

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