Data Explorer - 월별 활성 회원 비율

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

Data Explorer 플러그인을 플레이하고 탐색하고 계신 모든 분들께 인사드립니다!

@michebs 님, 이전 질문들에 대한 도움을 주셔서 감사합니다. Data Explorer 쿼리에 대해 한 가지 더 여쭤볼 것이 있습니다.

월별 활성 사용자 비율을 가져오는 쿼리를 작성해 보신 분 계신가요?

'활성’이란 해당 월에 어떤 스레드에서 좋아요를 눌렀거나 답글을 쓴 경우를 의미합니다. 횟수는 상관없지만 최소 1회 이상이어야 합니다. 비율은 커뮤니티 전체 회원 수를 기준으로 계산됩니다. 예를 들어, 한 달 동안 2명이 활동했다고 가정해 보겠습니다. 한 명은 어떤 게시물에 좋아요를 눌렀고, 다른 한 명은 답글을 남겼습니다. 그러면 이 2명을 커뮤니티 전체 회원 수의 합으로 나누는 방식입니다.

2

도움이 되기를 바랍니다.
전체 사용자 수는 시간에 따라 변하기 때문에, 쿼리에서 고려하는 값은 해당 작업이 수행된 연도와 월까지의 누적 사용자 수입니다.

WITH tt_users_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        COUNT(*) AS "new_users_month"
    FROM users
    WHERE id > 0
    GROUP BY date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),

total_users AS (
    SELECT
        year, 
        month, 
        SUM(new_users_month) over (ORDER BY year, month rows between unbounded preceding AND current row) AS total
    FROM tt_users_by_month ORDER BY year, month
)

SELECT 
    date_part('year', ua.created_at) AS year, 
    date_part('month', ua.created_at) AS month,
    TRUNC(COUNT(DISTINCT user_id)::decimal/tu.total*100,2) AS "%"
FROM 
    user_actions ua
INNER JOIN total_users tu ON (date_part('year', ua.created_at) = tu.year AND date_part('month', ua.created_at) = tu.month)
WHERE action_type IN (1,5)
	GROUP BY date_part('year', created_at), date_part('month', created_at), total
3

네, 완전히 이해했어요! 감사합니다!