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

Создание сообщества Данные и отчетность
, ,
1

Это 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, которые определяют диапазон дат для отчета. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
  • Общие табличные выражения (CTE):
    • CTE ignored_users выбирает ignored_user_id и подсчитывает, сколько раз этот ID встречается в таблице ignored_users (указывая, сколько раз пользователь был игнорирован), фильтруя результаты между :start_date и :end_date.
    • CTE muted_users аналогична, но выбирает muted_user_id из таблицы muted_users и подсчитывает случаи в пределах фильтров по дате.
  • Основной оператор SELECT: Выбирает детали пользователя из таблицы users и подсчеты из CTE ignored_users и muted_users.
    • ignores_count берется непосредственно из CTE ignored_users.
    • mutes_count берется из CTE muted_users, но если он NULL (что означает, что пользователь не был заглушен), он заменяется на 0 с помощью функции COALESCE.
    • Общее количество рассчитывается путем сложения количества игнорирований и заглушений.
  • JOINS: Основной запрос использует JOIN по таблице ignored_users, чтобы включить всех пользователей, которые были игнорированы хотя бы один раз, и LEFT OUTER JOIN по таблице muted_users, чтобы также включить пользователей, которые могли не быть заглушены.
  • ORDER BY: Отчет сортируется по общему количеству игнорирований и заглушений в порядке убывания, показывая самых «проблемных» пользователей в верхней части отчета.

Пример результатов

user username ignores_count mutes_count total
user1 user1 4 1 5
user2 user2 3 0 3
user3 user3 1 2 3
3 лайка
Admin dashboard report reference guide