Отчет по панели управления: соотношение флагов пользователей

[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, которые позволяют пользователю указать диапазон дат для отчета. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
• Пункт SELECT: Запрос выбирает следующие столбцы:
  • user_id: Уникальный идентификатор пользователя.
  • username: Имя пользователя.
  • silenced: Булево значение, указывающее, находится ли пользователь в текущем состоянии молчания.
  • disagreed_flags: Общее количество флагов пользователя, с которыми модераторы не согласились.
  • agreed_flags: Общее количество флагов пользователя, с которыми модераторы согласились.
  • ignored_flags: Общее количество флагов пользователя, проигнорированных модераторами.
  • score: Рассчитанный показатель, отражающий эффективность выставления флагов пользователем.
• Пункт FROM: Запрос соединяет таблицу users с таблицей reviewable_scores по полю user_id для получения информации о выставлении флагов, относящейся к каждому пользователю.
• Пункт WHERE: Запрос фильтрует данные, включая только записи для пользователей с id больше 0 и где дата created_at оценок reviewable находится в указанном диапазоне дат.
• Пункт 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
…	…	…	…	…	…	…