تقرير لوحة المعلومات - المساهمين الجدد

البيانات والتقارير
, ,
1

هذا هو إصدار SQL من تقرير لوحة المعلومات للمساهمين الجدد.

تم تصميم تقرير لوحة معلومات SQL هذا لمنح المسؤولين نظرة عامة واضحة على تفاعل المستخدمين في منتدى Discourse الخاص بهم من خلال تحديد عدد المستخدمين الذين قاموا بأول منشور لهم في كل يوم ضمن نطاق تاريخ محدد.

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

شرح استعلام SQL

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

المعلمات:

  • يقبل الاستعلام معلمتين، :start_date و :end_date، اللتين تحددان النطاق الزمني للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.

الاستعلام الداخلي: تحديد تاريخ أول منشور لكل مستخدم

يحتوي هذا التقرير على استعلام فرعي يحدد عمودين من جدول posts: user_id وأقدم طابع زمني لـ created_at (مُسمى مستعار first_post_date). يمثل أقدم طابع زمني لـ created_at أول منشور للمستخدم. يتضمن هذا الاستعلام الفرعي شرط WHERE الذي يأخذ في الاعتبار فقط المنشورات التي لم يتم حذفها (deleted_at IS NULL). أخيرًا، يقوم بتجميع النتائج حسب user_id لضمان أننا ننظر فقط إلى أول منشور لكل مستخدم.

الاستعلام الرئيسي: عد المستخدمين الذين قاموا بأول منشور لهم

يقوم الاستعلام الرئيسي بتنفيذ العمليات التالية:

  • JOIN: يتم ربط جدول posts الرئيسي (مُسمى مستعار p) بنتيجة الاستعلام الفرعي (مُسمى مستعار fp) على user_id لمطابقة كل منشور مع أول منشور للمستخدم المقابل.
  • التصفية حسب التاريخ: تتضمن عبارة WHERE شرطين - فهي تقارن الطابع الزمني لـ created_at لكل منشور مع first_post_date من الاستعلام الفرعي لضمان أننا نتعامل فقط مع المنشورات الأولى، وتتحقق من أن الطابع الزمني لـ created_at يقع ضمن النطاق الزمني المحدد، بما في ذلك end_date (+ يوم واحد لتضمين يوم النهاية بالكامل).
  • التجميع: ثم يتم تجميع المنشورات حسب التاريخ، مقطوعة إلى اليوم بدون مكون الوقت (date_trunc('day', p.created_at)::date)، مما يسمح بعد المستخدمين الفريدين الذين نشروا لأول مرة في كل يوم.
  • العد: باستخدام COUNT(DISTINCT p.user_id)، نحصل على عدد المستخدمين الفريدين الذين قاموا بأول منشور لهم في كل يوم على حدة.
  • الترتيب: يتم فرز النتائج حسب اليوم بترتيب تصاعدي (ORDER BY day) لإعطاء نظرة عامة زمنية على تفاعل المستخدم.

الناتج النهائي

يتكون التقرير النهائي من عمودين:

  • day: تاريخ تفاعل المستخدم بدون مكون الوقت.
  • new_contributors: عدد المستخدمين المميزين الذين قاموا بأول منشور لهم في المنتدى لكل يوم.

نتائج مثال

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
إعجابَين (2)