활동 기반 그룹 생성 쿼리

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

이들을 병합하고 개선했습니다 (제 부족한 SQL 실력으로요). 사용자명이 필요할 때는 CSV를 다운로드해서 사용자名列을 복사/붙여넣기 하면 됩니다.
likes_received_max를 추가해서 그룹을 나눌 수 있게 했습니다. 위의 그룹은 제외됩니다.

예를 들어
first_steps: 5개의 좋아요 (<30), 500개 게시글 읽음, 지난 1년간 5개 이상의 게시글 작성,
beginners: 30개의 좋아요 (<100), 1000개 게시글 읽음, 지난 1년간 10개 이상의 게시글 작성
padawan: 100개의 좋아요, 2000개 게시글 읽음, 지난 1년간 10개 이상의 게시글 작성
hero: 200개의 좋아요, 5000개 게시글 읽음, 지난 1년간 10개 이상의 게시글 작성

-- [params]
-- int :likes_received
-- int :posts_read
-- int :likes_received_max
-- int :posts_count



WITH user_activity AS (
    SELECT 
        p.user_id, 
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT 
    us.user_id,
    u.username,
    us.likes_received,
    us.posts_read_count,
    ua.posts_count,
    u.title
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
  JOIN users u ON u.id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= :posts_count
  AND us.likes_received < :likes_received_max
ORDER BY 2 ASC, 3 ASC, 4 ASC