이것은 Notify Moderators 대시보드 리포트의 SQL 버전입니다.
이 대시보드 리포트는 notify_moderators 알림 하위 유형으로 관리자에게 전송된 개인 메시지의 일일 개수를 제공합니다. 이 인사이트는 관리자가 관리자의 주의를 필요로 하는 수요가 높은 시기를 이해하는 데 도움이 되며, 사용자가 조정(모더레이션) 도움이 필요한 문제를 경험하고 있을 수 있음을 나타낼 수 있습니다.
-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16
SELECT
DATE(created_at) AS day,
COUNT(1) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
AND subtype = 'notify_moderators'
AND created_at BETWEEN :start_date AND :end_date
AND deleted_at IS NULL
AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day
SQL 쿼리 설명
이 쿼리는 topics 테이블에서 데이터를 추출하여 작동합니다 – 구체적으로는 주어진 시간 프레임 내에서 관리자에게 보내는 개인 메시지로 분류되는 항목들입니다. 아래에서 자세히 살펴보겠습니다:
- 날짜 파라미터:
- 쿼리는 리포트의 날짜 범위를 정의하는
:start_date와:end_date라는 두 가지 파라미터를 받아들입니다. 두 날짜 파라미터 모두YYYY-MM-DD날짜 형식을 지원합니다.
- 쿼리는 리포트의 날짜 범위를 정의하는
- 선택 및 카운트:
DATE(created_at)는created_at타임스탬프를 날짜 형식으로 변환하고 이를day라고 라벨링합니다. 이를 통해 메시지가 생성된 날짜별로 그룹화하고 카운트할 수 있습니다.COUNT(1)은 각 날짜별 항목 수를 카운트하는 데 사용되며, 이 카운트를notifications_count라고 라벨링합니다.
- 필터링 기준:
- 쿼리는
archetype이'private_message'인 항목만 필터링하여, 다른 유형의 토픽이 아닌 오직 메시지만을 대상으로 하도록 합니다. subtype는'notify_moderators'로 제한되어, 사용자가 관리자에게 알림을 보내거나 도움을 요청하는 경우를 분리합니다.- 메시지의
created_at타임스탬프가 지정된:start_date와:end_date사이에 있는지 확인합니다. - 삭제된 메시지(
deleted_at IS NULL)는 카운트에서 제외되어 리포트가 활성 통신만 반영하도록 합니다. - 마지막으로,
user_id > 0을 확인하여 메시지가 유효한 사용자와 연관되어 있는지 보장합니다.
- 쿼리는
- 그룹화 및 정렬:
- 쿼리는
GROUP BY DATE(created_at)을 사용하여 지정된 날짜 범위 내의 각 날짜별로 결과를 집계합니다. - 그런 다음
ORDER BY day를 사용하여 결과를 시간 순으로 정렬하여 리포트를 읽기 쉽고 해석하기 쉽게 만듭니다.
- 쿼리는
결과 예시
| day | notifications_count |
|---|---|
| 2023-12-17 | 3 |
| 2023-12-18 | 1 |
| 2023-12-21 | 1 |
| 2023-12-23 | 2 |
| 2023-12-26 | 1 |