مشاركات المستخدم حسب نطاق البريد الإلكتروني خلال الأيام السبعة الماضية

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

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

دعني أحاول.

ربما شيء كهذا:

-- [params]
-- string :interval = 7 days


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- يستخرج جزء النطاق من البريد الإلكتروني
  COUNT(p.id) AS "إجمالي المنشورات",
  COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS "المواضيع",
  COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS "المنشورات",
  COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS "منشورات الرسائل الشخصية"
FROM posts p
  JOIN user_emails ue ON p.user_id = ue.user_id AND ue.primary = TRUE -- يضمن استخدام البريد الإلكتروني الأساسي
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- يقوم بتصفية المنشورات من آخر x وقت بناءً على المعلمة
  AND p.user_id > 0 -- يستبعد المستخدمين النظاميين (النظام والروبوتات)
  AND p.deleted_at ISNULL
  AND t.deleted_at ISNULL
  AND p.post_type <> 3
GROUP BY email_domain
ORDER BY "إجمالي المنشورات" DESC -- يقوم بفرز النتائج حسب عدد المنشورات بترتيب تنازلي
إعجابَين (2)