이것은 최다 무시/음소거 사용자 대시보드 리포트의 SQL 버전입니다.
이 대시보드 리포트는 관리자가 지정된 시작일과 종료일 사이에 플랫폼 내에서 다른 멤버들에게 무시되거나 음소거된 사용자에 대한 통찰력을 제공합니다. 무시된 사용자는 특정 멤버가 의도적으로 필터링하여 해당 사용자의 게시물이 표시되지 않도록 한 사용자를 의미하며, 음소거된 사용자는 특정 멤버가 해당 사용자의 알림을 받지 않도록 선택한 사용자를 의미합니다.
이 리포트는 커뮤니티 내에서 마찰을 일으키거나, 다른 사람들의 참여를 꺼리게 만드는 행동을 하는 사용자를 식별하는 데 도움이 됩니다. 또한 이 리포트는 자주 무시되거나 음소거되는 사용자에게 연락하여 그들의 행동을 해결하거나, 커뮤니티 내에 주의를 요하는 문제가 있는지 더 광범위하게 이해하는 등 모더레이션(운영) 결정을 내리는 데 도움이 될 수 있습니다.
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-01
WITH ignored_users AS (
SELECT
ignored_user_id AS user_id,
COUNT(*) AS ignores_count
FROM ignored_users
WHERE created_at >= :start_date
AND created_at <= :end_date
GROUP BY ignored_user_id
ORDER BY COUNT(*) DESC
),
muted_users AS (
SELECT
muted_user_id AS user_id,
COUNT(*) AS mutes_count
FROM muted_users
WHERE created_at >= :start_date
AND created_at <= :end_date
GROUP BY muted_user_id
ORDER BY COUNT(*) DESC
)
SELECT
u.id AS user_id,
u.username AS username,
ig.ignores_count AS ignores_count,
COALESCE(mu.mutes_count, 0) AS mutes_count,
ig.ignores_count + COALESCE(mu.mutes_count, 0) AS total
FROM users AS u
JOIN ignored_users AS ig ON ig.user_id = u.id
LEFT OUTER JOIN muted_users AS mu ON mu.user_id = u.id
ORDER BY total DESC
SQL 쿼리 설명
이 SQL 쿼리는 여러 단계로 작동합니다:
- 날짜 매개변수:
- 쿼리는 리포트의 날짜 범위를 정의하는
:start_date와:end_date라는 두 개의 매개변수를 받아들입니다. 두 날짜 매개변수 모두YYYY-MM-DD형식의 날짜를 받아들입니다.
- 쿼리는 리포트의 날짜 범위를 정의하는
- 공통 테이블 표현식 (CTE):
ignored_usersCTE는ignored_users테이블에서ignored_user_id를 선택하고 해당 ID가 몇 번 나타나는지 계산합니다(사용자가 무시된 횟수를 나타냄). 결과는:start_date와:end_date사이에 필터링됩니다.muted_usersCTE는 유사하지만,muted_users테이블에서muted_user_id를 선택하고 날짜 필터 내에서 발생하는 횟수를 계산합니다.
- 메인 SELECT 문:
users테이블에서 사용자 상세 정보와ignored_users및muted_usersCTE에서 가져온 카운트를 선택합니다.ignores_count는ignored_usersCTE에서 직접 가져옵니다.mutes_count는muted_usersCTE에서 가져오되, 값이NULL(즉, 사용자가 음소거되지 않았음)인 경우COALESCE함수를 사용하여 0으로 대체합니다.- 무시와 음소거를 합산하여 총점을 계산합니다.
- JOIN: 메인 쿼리는
ignored_users에 대한JOIN을 사용하여 최소 한 번 이상 무시된 모든 사용자를 포함하고,muted_users에 대한LEFT OUTER JOIN을 사용하여 음소거되지 않았을 수도 있는 사용자도 포함합니다. - ORDER BY: 리포트는 무시와 음소거의 총합을 기준으로 내림차순으로 정렬되어, 가장 '문제’가 많은 사용자가 리포트 상단에 표시되도록 합니다.
결과 예시
| user | username | ignores_count | mutes_count | total |
|---|---|---|---|---|
| user1 | user1 | 4 | 1 | 5 |
| user2 | user2 | 3 | 0 | 3 |
| user3 | user3 | 1 | 2 | 3 |