Report Dashboard - Principali Utenti Ignorati / Silenziati

Dati e reportistica
, ,
1

Questa è una versione SQL del report della dashboard per gli utenti più ignorati/silenziosi.

Questo report della dashboard fornisce agli amministratori informazioni su quali utenti sulla piattaforma sono stati ignorati o silenziati da altri membri tra date di inizio e fine specificate. Un utente ignorato è qualcuno che è stato selettivamente escluso da un membro in modo che i suoi post non siano visibili a lui, mentre un utente silenziato è qualcuno da cui un membro ha scelto di non ricevere notifiche.

Questo report può identificare gli utenti che causano attriti all’interno di una community o il cui comportamento porta gli altri a non voler interagire con loro. Il report può aiutare a prendere decisioni di moderazione come contattare gli utenti frequentemente ignorati o silenziati per affrontare il loro comportamento, o comprendere più ampiamente se ci sono problemi nella community che richiedono attenzione.

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

Spiegazione della query SQL

Questa query SQL funziona in diversi passaggi:

  • Parametri Data:
    • La query accetta due parametri, :start_date e :end_date, che definiscono l’intervallo di date per il report. Entrambi i parametri data accettano il formato data AAAA-MM-GG.
  • Espressioni Tabellari Comuni (CTE):
    • La CTE ignored_users seleziona ignored_user_id e conta quante volte quell’ID appare nella tabella ignored_users (indicando quante volte l’utente è stato ignorato), filtrando i risultati tra :start_date e :end_date.
    • La CTE muted_users è simile ma seleziona muted_user_id dalla tabella muted_users e conta le istanze all’interno dei filtri di data.
  • Istruzione SELECT Principale: Seleziona i dettagli dell’utente dalla tabella users e i conteggi dalle CTE ignored_users e muted_users.
    • ignores_count viene preso direttamente dalla CTE ignored_users.
    • mutes_count viene preso dalla CTE muted_users, ma se è NULL (significa che l’utente non è stato silenziato), viene sostituito con 0 utilizzando la funzione COALESCE.
    • Viene calcolato un totale sommando gli ignorati e i silenziati.
  • JOIN: La query principale sfrutta un JOIN su ignored_users per includere tutti gli utenti che sono stati ignorati almeno una volta, e un LEFT OUTER JOIN su muted_users per includere anche gli utenti che potrebbero non essere stati silenziati.
  • ORDER BY: Il report è ordinato per il totale di ignorati e silenziati in ordine decrescente, mostrando gli utenti più “problematici” in cima al report.

Esempio di Risultati

utente username ignores_count mutes_count totale
user1 user1 4 1 5
user2 user2 3 0 3
user3 user3 1 2 3
3 Mi Piace