SQL Interview Prep · درس

أطول سلسلة متتالية لكل مستخدم

حساب الحد الأقصى لطول السلسلة المتتالية داخل كل مجموعة

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

السؤال

من أسئلة المتابعة الشائعة بعد اكتشاف الأيام المتتالية: «ما أطول سلسلة من الأيام المتتالية التي كان فيها كل مستخدم نشطًا؟» تطرح فرق المنتجات والنمو هذا السؤال باستمرار لقياس التفاعل.

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

تذكّروا طريقة بناء الجزر

من الدرس السابق، يستخدم التجميع لكل سلسلة login_date - ROW_NUMBER() بوصفه مرساة الجزيرة. ويمكن أن يمتلك كل مستخدم عدة جزر؛ سنحسب أولًا صفًا واحدًا لكل جزيرة، ثم نختزل النتيجة إلى صف واحد لكل مستخدم.

احتفظوا بهذه الخطة ذات الطبقتين في أذهانكم: ابنوا الجزر أولًا، ثم اجمعوا نتائجها.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

صف واحد لكل جزيرة

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

نسمّي هذا الـ CTE باسم islands حتى تتمكن الطبقة التالية من القراءة منه بوضوح.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT * FROM islands;

الإجابة البسيطة: الطول الأقصى

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

هذه هي الإجابة الأوضح عندما لا تكون تواريخ البداية والنهاية مطلوبة.

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

إرجاع التواريخ أيضًا

غالبًا ما يضيف المحاوِر: «وأظهر متى حدثت هذه السلسلة». لا يستطيع MAX وحده أن يحدد أي جزيرة فازت. لذلك تحتاجون إلى ترتيب الجزر داخل كل مستخدم والاحتفاظ بالمرتبة 1.

استخدموا ROW_NUMBER مع ترتيب تنازلي حسب الطول، حتى تحصل أفضل سلسلة لكل مستخدم على المرتبة 1. وأضيفوا معيارًا فاصلًا لحسم حالات التعادل بطريقة حتمية.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

ترتيب الصفوف وتصفيتها

ضعوا الترتيب داخل CTE، ثم صفّوا النتائج بحيث تكون rnk = 1. لا يمكنكم التصفية على دالة نافذة مباشرةً داخل WHERE، لذا تصبح الطبقة الإضافية ضرورية.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

RANK مقابل ROW_NUMBER عند التعادل

ماذا لو كان لدى المستخدم سلسلتان متساويتان في أقصى طول، وكان المحاوِر يريد إرجاع كلتيهما؟ استبدلوا ROW_NUMBER بـ RANK واحتفظوا بالصفوف التي تحقق rnk = 1.

ROW_NUMBER — فائز واحد بالضبط لكل مستخدم، ويكون الاختيار عشوائيًا عند التعادل ما لم تضيفوا معيارًا فاصلًا.
RANK — تشترك جميع السلاسل الأطول المتعادلة في المرتبة 1، ويُحتفظ بها جميعًا.

وضّحوا السلوك المطلوب، فهذا يدل على انتباهكم إلى الحالات الطرفية.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

مثال محلول

لنفترض أن المستخدم 7 سجّل الدخول من 1 إلى 4 يناير، ثم من 10 إلى 11 يناير، ثم من 20 إلى 23 يناير. لدينا ثلاث جزر أطوالها 4 و2 و4. الطول الأكبر هو 4، وهناك تعادل.

باستخدام ROW_NUMBER مع المعيار الفاصل streak_start: ستُعاد سلسلة 1 إلى 4 يناير فقط.
باستخدام RANK: ستُعاد كلتا السلسلتين، من 1 إلى 4 يناير ومن 20 إلى 23 يناير.

إن ذكر ذلك بصوت واضح يبرهن على أنكم فكرتم في حالات التكرار.

التعامل مع المستخدمين الذين لم يسجّلوا الدخول

قد يسأل المحاور: «ماذا عن المستخدمين الذين لم يسجّلوا الدخول مطلقًا؟» لا تحتوي جداول logins على صفوف لهؤلاء المستخدمين، ولذلك يختفون من النتيجة. إذا كان يجب أن يظهروا بقيمة 0 للسلسلة المتتالية، فاستخدم LEFT JOIN مع جدول users الكامل ثم COALESCE.

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

ملاحظات حول الأداء

ينفّذ هذا النمط مرورًا مرتبًا واحدًا على البيانات، بالإضافة إلى التجميع. وللحفاظ على سرعته:

تأكّد من وجود فهرس على (user_id, login_date) حتى يتجنّب ORDER BY في النافذة عملية الفرز.
أزل التكرارات مبكرًا إذا كان المصدر يحتوي على أحداث متعددة في اليوم نفسه.
تجنّب تغليف login_date داخل دوال في ORDER BY، إذ قد يمنع ذلك استخدام الفهرس.

بالنسبة إلى الجداول الكبيرة جدًا، يتفوّق هذا الأسلوب بسهولة على أي نهج يعتمد على self-join.

إجابة المقابلة الكاملة

إليك الاستعلام الكامل والمصاغ بعناية، الذي يعرض أطول سلسلة متتالية لكل مستخدم مع تواريخها — وهو الإصدار الذي تكتبه على اللوح الأبيض.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

تحقّق سريع

اختر الأداة المناسبة للمتطلب.

مراجعة

لحساب أطول سلسلة متتالية لكل مستخدم:

أنشئ الجزر باستخدام مرساة login_date - ROW_NUMBER().
اختصر كل جزيرة إلى طولها ونطاق تواريخها.
لحساب الطول فقط، استخدم MAX(streak_len) مع التجميع حسب المستخدم.
لإظهار التواريخ أيضًا، رتّب الجزر لكل مستخدم واحتفظ بالترتيب 1 — استخدم RANK لتضمين حالات التعادل، وROW_NUMBER لاختيار فائز واحد.
استخدم LEFT JOIN مع users لإظهار المستخدمين ذوي السلسلة التي تساوي صفرًا.

التالي: اكتشاف N من الصفوف المتتالية التي تستوفي شرطًا.

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

هل درس «أطول سلسلة متتالية لكل مستخدم» مجاني؟

نعم — نص درس «أطول سلسلة متتالية لكل مستخدم» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 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 يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.

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

اكتشاف الأيام التقويمية المتتالية
أطول سلسلة متتالية لكل مستخدم
عدد N من الصفوف المتتالية التي تستوفي شرطًا
السلسلة النشطة الحالية حتى اليوم

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