استعلام لحساب المنشورات حسب نطاق البريد الإلكتروني للأيام السبعة الماضية، مع استبعاد المجموعات التلقائية (الروبوتات، النظام)
SELECT
split_part(ue.email, '@', 2) AS email_domain, -- يستخرج جزء النطاق من البريد الإلكتروني
COUNT(p.id) AS post_count
FROM
posts p
JOIN users u ON p.user_id = u.id
JOIN user_emails ue ON u.id = ue.user_id
AND ue.primary = TRUE -- يضمن استخدام البريد الإلكتروني الأساسي
LEFT JOIN group_users gu ON gu.user_id = u.id
LEFT JOIN GROUPS g ON gu.group_id = g.id
AND g.automatic = TRUE
WHERE
p.created_at >= CURRENT_DATE - INTERVAL '7 days' -- يقوم بتصفية المنشورات من الأيام السبعة الماضية
AND g.id IS NULL -- يضمن أن المستخدمين ليسوا جزءًا من أي مجموعات تلقائية
GROUP BY
email_domain
ORDER BY
post_count DESC -- يقوم بفرز النتائج حسب عدد المنشورات بترتيب تنازلي
كان هذا اختبارًا سريعًا وغير مصقول لروبوت مساعد SQL للعثور على عدد المشاركات حسب النطاق. قد تكون حالة الاستخدام للجمعيات أو منتديات العضوية التي ترغب في تتبع المجموعات الفرعية الأكثر تفاعلاً داخل المنتدى.
بالتأكيد يمكن تحسينه، مثل إزالة المشاركات المحذوفة أو الصغيرة، والرسائل الخاصة، وما إلى ذلك، ولكن اعتقدت أنني سأنشره في حال استلهم أي شخص منه.
أعتقد أنه يعطي أرقامًا مبالغًا فيها بسبب انضمامات المجموعات (أيضًا، الجميع جزء من مجموعة تلقائية لمستوى الثقة الخاص بهم، لذا هناك قدر من عدم الاتساق هناك). أعتقد أنه يمكننا إزالة هذه وإضافة بعض التعديلات للتركيز على المواضيع والمنشورات والرسائل الخاصة.
دعني أحاول.
ربما شيء كهذا:
-- [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 -- يقوم بفرز النتائج حسب عدد المنشورات بترتيب تنازلي