이것은 새로운 기여자를 위한 대시보드 보고서의 SQL 버전입니다.
이 SQL 대시보드 보고서는 지정된 날짜 범위 내에서 각 날짜에 첫 게시글을 작성한 사용자 수를 파악하여 관리자가 Discourse 포럼의 사용자 참여도에 대한 명확한 개요를 제공하도록 설계되었습니다.
-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16
SELECT
date_trunc('day', p.created_at)::date AS day,
COUNT(DISTINCT p.user_id) AS new_contributors
FROM
posts p
INNER JOIN (
SELECT
user_id,
MIN(created_at) as first_post_date
FROM
posts
WHERE deleted_at IS NULL
GROUP BY
user_id
) fp ON p.user_id = fp.user_id
WHERE
p.created_at = fp.first_post_date
AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
day
ORDER BY
day
SQL 쿼리 설명
이 보고서는 지정된 날짜 범위 내에서 각 날짜에 첫 게시글을 작성한 고유 사용자 수를 가져옵니다. 이를 위해 다음과 같은 단계를 수행합니다:
매개변수:
- 쿼리는
:start_date와:end_date라는 두 개의 매개변수를 받아 보고서를 위한 날짜 범위를 정의합니다. 두 날짜 매개변수 모두YYYY-MM-DD형식의 날짜를 허용합니다.
내부 쿼리: 각 사용자의 첫 게시글 날짜 결정
이 보고서는 posts 테이블에서 user_id와 가장 초기의 created_at 타임스탬프(별칭 first_post_date)라는 두 열을 선택하는 서브쿼리를 포함합니다. 가장 초기의 created_at 타임스탬프는 사용자의 첫 게시글을 나타냅니다. 이 서브쿼리는 삭제되지 않은 게시글만 고려하도록 하는 WHERE 조건(deleted_at IS NULL)을 포함합니다. 마지막으로, 각 사용자의 첫 게시글만 고려하도록 결과를 user_id별로 그룹화합니다.
메인 쿼리: 첫 게시글을 작성한 사용자 수 집계
메인 쿼리는 다음 작업을 수행합니다:
- JOIN: 메인
posts테이블(별칭p)은user_id를 기준으로 서브쿼리 결과(별칭fp)와 조인되어 각 게시글을 해당 사용자의 첫 게시글과 일치시킵니다. - 날짜별 필터링:
WHERE절에는 두 가지 조건이 포함됩니다. 각 게시글의created_at타임스탬프를 서브쿼리의first_post_date와 비교하여 첫 게시글만 다루도록 하고,created_at타임스탬프가 지정된 날짜 범위 내에 있는지 확인합니다(끝 날짜를 완전히 포함하기 위해 +1일을 추가하여 끝 날짜를 포함합니다). - 집계: 게시글은 시간 요소 없이 날짜 단위로 잘라낸 날짜(
date_trunc('day', p.created_at)::date)별로 그룹화되며, 이를 통해 각 날짜에 처음 게시글을 작성한 고유 사용자를 집계할 수 있습니다. - 계수:
COUNT(DISTINCT p.user_id)를 사용하여 각 날짜에 첫 게시글을 작성한 고유 사용자의 수를 얻습니다. - 정렬: 결과는 날짜를 오름차순으로 정렬(
ORDER BY day)하여 사용자 참여도의 시간적 개요를 제공합니다.
최종 출력
최종 보고서는 두 개의 열로 구성됩니다:
day: 시간 요소가 없는 사용자 참여 날짜.new_contributors: 각 날짜에 포럼에서 첫 게시글을 작성한 고유 사용자의 수.
결과 예시
| day | new_contributors |
|---|---|
| 2023-12-15 | 16 |
| 2023-12-16 | 8 |
| 2023-12-17 | 7 |
| 2023-12-18 | 19 |
| 2023-12-19 | 15 |