القراء والمستخدمون النشطون يوميًا

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

يوفر تقرير SQL هذا عددًا يوميًا للقراء (المستخدمون الذين يقرؤون المنشورات فقط) والمستخدمين النشطين (المستخدمون الذين أنشأوا منشورًا واحدًا على الأقل خلال اليوم)، عبر نطاق تاريخ محدد، مع مرشح اختياري لمعرف الفئة.

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- int :reader_post_read_count = 10
-- null int :category_id

WITH posts_by_user AS (
    SELECT
        p.user_id,
        p.created_at::date AS post_date,
        p.topic_id,
        COUNT(*) AS posts
    FROM posts p
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, post_date, p.topic_id
), 

posts_read_by_user AS (
    SELECT
        uv.user_id,
        uv.visited_at::date AS visit_date,
        SUM(uv.posts_read) AS posts_read
    FROM user_visits uv
    LEFT JOIN group_users gu ON gu.user_id = uv.user_id
    LEFT JOIN groups g ON g.id = gu.group_id
    LEFT JOIN posts p ON p.user_id = uv.user_id AND p.created_at BETWEEN :start_date AND :end_date
    LEFT JOIN topics t ON t.id = p.topic_id AND t.category_id = :category_id
    WHERE uv.visited_at BETWEEN :start_date AND :end_date
    AND (:category_id IS NULL OR t.category_id IS NOT NULL)
    GROUP BY uv.user_id, visit_date
),

active_users_per_day AS (
    SELECT
        pbu.post_date,
        COUNT(DISTINCT pbu.user_id) AS active_user_count
    FROM posts_by_user pbu
    JOIN topics t ON pbu.topic_id = t.id
    WHERE posts > 0
    AND (:category_id IS NULL OR t.category_id = :category_id)
    GROUP BY post_date
),

readers_per_day AS (
    SELECT
        prbu.visit_date,
        COUNT(DISTINCT prbu.user_id) AS reader_count
    FROM posts_read_by_user prbu
    LEFT JOIN posts_by_user pbu ON prbu.user_id = pbu.user_id AND prbu.visit_date = pbu.post_date
    WHERE pbu.posts IS NULL
    AND prbu.posts_read > :reader_post_read_count
    GROUP BY prbu.visit_date
)

SELECT
    rpd.visit_date,
    rpd.reader_count,
    COALESCE(aupd.active_user_count, 0) AS active_user_count
FROM readers_per_day rpd
LEFT JOIN active_users_per_day aupd ON rpd.visit_date = aupd.post_date
ORDER BY rpd.visit_date

شرح استعلام SQL

المعلمات

  • start_date و end_date: يحددان الإطار الزمني للتحليل.
  • reader_post_read_count: يحدد الحد الأدنى لاعتبار شخص ما “قارئًا” بناءً على عدد المنشورات التي قرأها.
  • category_id: اختياري. عند تحديده، يركز التقرير على فئة واحدة، مما يسمح بتحليل أكثر استهدافًا.

عبارات الاستعلام المشتركة (CTEs)

  1. posts_by_user: تحدد عبارة CTE هذه عدد المنشورات التي أنشأها كل مستخدم في كل يوم ضمن النطاق الزمني المحدد. وهي ضرورية لفهم المستخدمين الذين يساهمون بالمحتوى وكيفية توزيع هذا النشاط بمرور الوقت.
  2. posts_read_by_user: تحسب عبارة CTE هذه عدد المنشورات التي قرأها كل مستخدم في كل يوم، مرة أخرى ضمن النطاق الزمني المحدد. تستفيد من بيانات زيارات المستخدم لتقدير استهلاك المحتوى، وهو مؤشر رئيسي للتفاعل.
  3. active_users_per_day: بناءً على عبارة CTE posts_by_user، تحسب عبارة CTE هذه عدد المستخدمين الفريدين الذين نشروا مرة واحدة على الأقل في اليوم. إنها توفر لقطة يومية للمنشئين النشطين للمحتوى.
  4. readers_per_day: باستخدام عبارة CTE posts_read_by_user، تحدد عبارة CTE هذه المستخدمين الذين قرأوا أكثر من عدد محدد من المنشورات (reader_post_read_count) دون النشر بأنفسهم. إنها تسلط الضوء على الأغلبية الصامتة التي تتفاعل من خلال القراءة.

النتائج

يقدم الناتج النهائي نظرة عامة يومية على تفاعل المستخدم، مفصلاً عدد القراء والمستخدمين النشطين (الذين نشروا) كل يوم.

نتائج مثال

visit_date reader_count active_user_count
2024-01-02 150 25
2024-01-03 175 30
2024-01-04 160 20
2024-01-05 180 22
… … …
4 إعجابات