대시보드 보고서 - 사용자 플래깅 비율

[SaraDev (Sara Devlaeminck)]

사용자 플래깅 비율 대시보드 보고서의 SQL 버전입니다.

이 대시보드 보고서는 직원(모더레이터)의 응답(동의 및 불일치) 비율을 기준으로 정렬된 사용자 목록을 생성합니다. 이 보고서는 각 사용자의 플래깅 활동에 대한 정보를 포함하며, 특히 모더레이터에 의해 동의, 불일치 또는 무시된 플래그의 수와 사용자의 플래깅 성능을 나타내는 계산된 점수에 초점을 맞춥니다.

이 보고서는 관리자가 부적절한 콘텐츠를 정확히 플래그한 사용자를 식별하고, 커뮤니티 모더레이션에 대한 사용자 참여도를 파악하며, 플래깅 관행에 대한 피드백을 제공하고, 잠재적인 모더레이터 역할을 위한 사용자 플래깅 성능을 평가하는 데 유용합니다.

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01

SELECT
  u.id AS user_id,
  u.username,
  CASE
    WHEN u.silenced_till IS NOT NULL THEN 't'
    ELSE 'f'
  END AS silenced,
  SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric AS disagreed_flags,
  SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric AS agreed_flags,
  SUM(CASE WHEN rs.status = 3 THEN 1 ELSE 0 END)::numeric AS ignored_flags,
  (
    CASE
      WHEN SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric = 0 THEN
        SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric * SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric
      ELSE
        ROUND(
          (1 - (SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric / SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END))) *
          (SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) - SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric)
        )
    END
  ) AS score
FROM
  users AS u
  INNER JOIN reviewable_scores AS rs ON rs.user_id = u.id
WHERE
  u.id > 0
  AND rs.created_at >= :start_date
  AND rs.created_at <= :end_date
GROUP BY
  u.id,
  u.username,
  u.uploaded_avatar_id,
  u.silenced_till
ORDER BY
  score DESC
LIMIT 100

SQL 쿼리 설명

쿼리의 세부 내용은 다음과 같습니다:

• 매개변수: 이 쿼리는 :start_date와 :end_date라는 두 가지 매개변수를 받아들이며, 사용자는 이를 통해 보고서의 날짜 범위를 지정할 수 있습니다. 두 날짜 매개변수 모두 YYYY-MM-DD 형식의 날짜를 받습니다.
• SELECT 절: 이 쿼리는 다음 컬럼을 선택합니다:
  • user_id: 사용자의 고유 식별자.
  • username: 사용자의 사용자 이름.
  • silenced: 사용자가 현재 무음 상태(silenced)인지 여부를 나타내는 불리언 값.
  • disagreed_flags: 모더레이터에 의해 불일치로 처리된 사용자 플래그의 총 수.
  • agreed_flags: 모더레이터에 의해 동의로 처리된 사용자 플래그의 총 수.
  • ignored_flags: 모더레이터에 의해 무시된 사용자 플래그의 총 수.
  • score: 사용자의 플래깅 성능을 나타내는 계산된 점수.
• FROM 절: 이 쿼리는 user_id를 기준으로 users 테이블과 reviewable_scores 테이블을 조인하여 각 사용자와 관련된 플래깅 정보를 가져옵니다.
• WHERE 절: 이 쿼리는 id가 0보다 크고, reviewable_scores의 created_at 날짜가 지정된 날짜 범위 내에 있는 사용자의 레코드만 포함하도록 데이터를 필터링합니다.
• GROUP BY 절: 이 쿼리는 각 사용자의 플래깅 데이터를 집계하기 위해 결과를 user_id, username, uploaded_avatar_id, silenced_till별로 그룹화합니다.
• ORDER BY 절: 이 쿼리는 계산된 score를 내림차순으로 정렬하여 가장 높은 플래깅 성능을 가진 사용자를 먼저 표시합니다.
• LIMIT 절: 이 쿼리는 계산된 점수에 기반하여 상위 100명의 사용자로 결과를 제한합니다.

결과 예시

user	username	silenced	disagreed_flags	agreed_flags	ignored_flags	score
user_1_id	user_1	f	0.0	10.0	3.0	100.0
user_2_id	user_2	f	0.0	6.0	3.0	36.0
user_3_id	user_3	f	0.0	4.0	0.0	16.0
…	…	…	…	…	…	…