从顶级质量会员查询中排除群组

社区 数据与报告
2

我并没有真正测试过这个。

-- [参数]
-- int_list :exclude_groups = 12, 2, 3

SELECT 
    sum(p.score) / count(p) as "每帖平均分数", 
    count(p.id) as 帖子数量，
    p.user_id
FROM posts p
JOIN users u ON u.id = p.user_id
LEFT JOIN group_users gu on u.id = gu.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
  AND u.active
  AND (gu.group_id not IN (:exclude_groups))
GROUP by p.user_id, u.views
HAVING count(p.id) > 40
ORDER BY sum(p.score) / count(p) DESC
LIMIT 100