تقرير لوحة التحكم - أفضل المستخدمين حسب الإعجابات المستلمة من مستخدم بمستوى ثقة أقل

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

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

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

--[params]
--date :start_date = 2024-01-01
--date :end_date = 2025-01-01

WITH user_liked_tl_lower AS (
    SELECT
        users.id AS user_id,
        users.username,
        users.trust_level,
        COUNT(*) AS likes,
        rank() OVER (
            PARTITION BY users.trust_level
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM users
    INNER JOIN posts p ON p.user_id = users.id
    INNER JOIN user_actions ua ON ua.target_post_id = p.id AND ua.action_type = 1
    INNER JOIN users u_liked ON ua.user_id = u_liked.id AND u_liked.trust_level < users.trust_level
    WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY users.id
    ORDER BY trust_level DESC, likes DESC
)

SELECT * FROM user_liked_tl_lower
WHERE rank <= 10

شرح استعلام SQL

  • معلمات التاريخ:
    • يقبل الاستعلام معلمتين، :start_date و :end_date، اللتين تحددان النطاق الزمني للتقرير. تقبل كلتا معلمتي التاريخ تنسيق التاريخ YYYY-MM-DD.
  • تعبير الجدول المشترك (CTE):
    • يتم تعريف CTE user_liked_tl_lower في البداية لإعادة الاستخدام داخل الاستعلام الرئيسي.
    • يحسب العدد الإجمالي للإعجابات التي تلقاها كل مستخدم على منشوراته من مستخدمين ذوي مستويات ثقة أقل ضمن الإطار الزمني المحدد بواسطة :start_date و :end_date.
  • الربط الداخلي (Inner Joins):
    • تتم عمليات الربط بين جدول users وجدولي posts و user_actions لتصفية الإجراءات التي هي “إعجابات” (يشار إليها بـ action_type = 1).
    • يتم إجراء ربط إضافي بجدول users (u_liked) للتأكد من أن المعجب لديه مستوى ثقة أقل من مؤلف المنشور.
  • الترتيب:
    • يتم تقسيم النتائج حسب مستوى ثقة المستخدم وترتيبها حسب عدد الإعجابات بترتيب تنازلي.
    • يتم تعيين ترتيب بناءً على عدد الإعجابات، مع حصول التعادلات على نفس الترتيب وتخطي الترتيب التالي للأرقام وفقًا لذلك (هذا هو الترتيب القياسي، وليس الترتيب الكثيف).
  • تصفية النتائج في CTE:
    • يتم احتساب الإعجابات التي تم تقديمها فقط خلال الفترة الزمنية المحددة.
  • الاختيار النهائي:
    • يحدد الاستعلام الرئيسي جميع الأعمدة من CTE حيث يكون rank 10 أو أقل.
    • يحد هذا النتائج بأعلى 10 مستخدمين في كل فئة من فئات مستوى الثقة الذين تلقوا أكبر عدد من الإعجابات من مستخدمين ذوي مستويات ثقة أقل.

نتائج مثال

user username trust_level likes rank
user1 user1 4 323 1
user2 user2 4 164 2
user3 user3 4 143 3
… … … …
user11 user11 3 45 1
user12 user12 3 34 2
… … … …
3 إعجابات