Dashboard Report - Top Ignored / Muted Users

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Top Ignored / Muted Users.

This dashboard report provides administrators with insights into which users on the platform have been ignored or muted by other members between specified start and end dates. An ignored user is one that has been selectively screened out by a member so that their posts are not visible to them, while a muted user is one that a member has chosen to receive no notifications from.

This report can identify users who are causing friction within a community, or whose behavior is leading others to not want to engage with them. The report can help in making moderation decisions like reaching out to frequently ignored or muted users to address their behavior, or understanding more broadly if there are issues in the community that require attention.

-- [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 Query Explanation

This SQL query works in several steps:

  • Date Parameters:
    • The query accepts two parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.
  • Common Table Expressions (CTEs):
    • ignored_users CTE selects the ignored_user_id and counts how many times that ID appears in the ignored_users table (indicating how many times the user has been ignored), filtering the results between :start_date and :end_date.
    • muted_users CTE is similar but selects the muted_user_id from the muted_users table and counts the instances within the date filters.
  • Main SELECT Statement: Selects the user details from the users table and the counts from the ignored_users and muted_users CTEs.
    • ignores_count is taken directly from the ignored_users CTE.
    • mutes_count is taken from the muted_users CTE, but if it’s NULL (meaning the user hasn’t been muted), it is replaced with 0 using the COALESCE function.
    • A total is calculated by adding ignores and mutes together.
  • JOINS: The main query leverages a JOIN on ignored_users to include all users who have been ignored at least once, and a LEFT OUTER JOIN on muted_users to also include users who may not have been muted.
  • ORDER BY: The report is ordered by the total ignores and mutes in descending order, showing the most ‘problematic’ users at the top of the report.

Example Results

user username ignores_count mutes_count total
user1 user1 4 1 5
user2 user2 3 0 3
user3 user3 1 2 3
3 Likes