최근 7일간의 이메일 도메인별 사용자 게시물

커뮤니티 구축 데이터 및 보고
4

그룹 가입 때문에 숫자가 부풀려지는 것 같아 (또한 모든 사용자가 신뢰 수준에 따라 자동 그룹에 속해 있어서 약간 일관성이 떨어지기도 해). 이 부분을 제거하고, 주제, 게시물, 개인 메시지에 초점을 맞출 수 있도록 몇 가지 조정을 추가할 수 있을 것 같아.

한번 시도해 볼게.

이런 식으로 하면 어떨까:

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


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- 이메일의 도메인 부분을 추출
  COUNT(p.id) AS "Total Posts",
  COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS "Topics",
  COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS "Posts",
  COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS "Personal Message posts"
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 "Total Posts" DESC -- 게시물 수 기준 내림차순 정렬