일일 독자 및 활성 사용자 수

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

이 SQL 리포트는 지정된 날짜 범위 내에서 독자(게시글을 읽기만 하는 사용자)와 활성 사용자(당일 최소 1건의 게시글을 생성한 사용자)의 일일 수를 집계하며, 카테고리 ID에 대한 선택적 필터를 제공합니다.

이 리포트는 커뮤니티의 활성도를 단순히 콘텐츠 생성 측면뿐 아니라 콘텐츠 소비 측면에서도 이해하는 데 도움을 주기 위해 설계되었습니다. 이러한 이중적 관점은 사용자 참여에 대한 더 상세한 이해를 가능하게 하며, 높은 독서율이나 게시 활동이 있는 날짜를 강조합니다.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- int :reader_post_read_count = 10
-- null int :category_id

WITH posts_by_user AS (
    SELECT
        p.user_id,
        p.created_at::date AS post_date,
        p.topic_id,
        COUNT(*) AS posts
    FROM posts p
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, post_date, p.topic_id
), 

posts_read_by_user AS (
    SELECT
        uv.user_id,
        uv.visited_at::date AS visit_date,
        SUM(uv.posts_read) AS posts_read
    FROM user_visits uv
    LEFT JOIN group_users gu ON gu.user_id = uv.user_id
    LEFT JOIN groups g ON g.id = gu.group_id
    LEFT JOIN posts p ON p.user_id = uv.user_id AND p.created_at BETWEEN :start_date AND :end_date
    LEFT JOIN topics t ON t.id = p.topic_id AND t.category_id = :category_id
    WHERE uv.visited_at BETWEEN :start_date AND :end_date
    AND (:category_id IS NULL OR t.category_id IS NOT NULL)
    GROUP BY uv.user_id, visit_date
),

active_users_per_day AS (
    SELECT
        pbu.post_date,
        COUNT(DISTINCT pbu.user_id) AS active_user_count
    FROM posts_by_user pbu
    JOIN topics t ON pbu.topic_id = t.id
    WHERE posts > 0
    AND (:category_id IS NULL OR t.category_id = :category_id)
    GROUP BY post_date
),

readers_per_day AS (
    SELECT
        prbu.visit_date,
        COUNT(DISTINCT prbu.user_id) AS reader_count
    FROM posts_read_by_user prbu
    LEFT JOIN posts_by_user pbu ON prbu.user_id = pbu.user_id AND prbu.visit_date = pbu.post_date
    WHERE pbu.posts IS NULL
    AND prbu.posts_read > :reader_post_read_count
    GROUP BY prbu.visit_date
)

SELECT
    rpd.visit_date,
    rpd.reader_count,
    COALESCE(aupd.active_user_count, 0) AS active_user_count
FROM readers_per_day rpd
LEFT JOIN active_users_per_day aupd ON rpd.visit_date = aupd.post_date
ORDER BY rpd.visit_date

SQL 쿼리 설명

매개변수

  • start_date와 end_date: 분석 대상 시간 범위를 정의합니다.
  • reader_post_read_count: 사용자가 읽은 게시글 수를 기준으로 '독자’로 간주하기 위한 임계값을 설정합니다.
  • category_id: 선택 사항입니다. 지정된 경우 리포트는 단일 카테고리에 초점을 맞추어 더 정밀한 분석을 허용합니다.

CTE(공통 테이블 표현식)

  1. posts_by_user: 이 CTE는 지정된 날짜 범위 내에서 각 사용자가 하루에 생성한 게시글 수를 식별합니다. 이는 콘텐츠에 기여하는 사용자를 파악하고 이러한 활동이 시간대에 따라 어떻게 분포하는지 이해하는 데 중요합니다.
  2. posts_read_by_user: 이 CTE는 지정된 날짜 범위 내에서 각 사용자가 하루에 읽은 게시글 수를 계산합니다. 사용자 방문 데이터를 활용하여 콘텐츠 소비를 추정하며, 이는 참여도의 핵심 지표입니다.
  3. active_users_per_day: posts_by_user CTE를 기반으로, 이 CTE는 하루에 최소 1회 이상 게시한 고유 사용자 수를 집계합니다. 이는 활성 콘텐츠 생성자의 일별 스냅샷을 제공합니다.
  4. readers_per_day: posts_read_by_user CTE를 사용하여, 이 CTE는 게시글을 작성하지 않고 지정된 수(reader_post_read_count) 이상의 게시글을 읽은 사용자를 식별합니다. 이는 읽기를 통해 참여하는 침묵하는 다수를 강조합니다.

결과

최종 출력물은 사용자의 일별 참여도 개요를 제시하며, 매일의 독자 수와 활성 사용자(게시한 사용자) 수를 상세히 보여줍니다.

결과 예시

visit_date reader_count active_user_count
2024-01-02 150 25
2024-01-03 175 30
2024-01-04 160 20
2024-01-05 180 22
… … …
4개의 좋아요