Report Dashboard - Rapporto Segnalazioni Utenti

Questa è una versione SQL del report della dashboard per il rapporto di segnalazione degli utenti.

Questo report della dashboard genera un elenco di utenti ordinati in base al rapporto tra le risposte dello staff alle loro segnalazioni (concordate e discordate). Il report include informazioni sull’attività di segnalazione di ciascun utente, concentrandosi in particolare sul numero di segnalazioni con cui i moderatori sono stati d’accordo, in disaccordo o che sono state ignorate, nonché su un punteggio calcolato che rappresenta le prestazioni di segnalazione dell’utente.

Il report è utile per gli amministratori per identificare gli utenti che segnalano accuratamente contenuti inappropriati, valutare il coinvolgimento degli utenti nella moderazione della community, fornire feedback sulle pratiche di segnalazione e valutare le prestazioni di segnalazione degli utenti per potenziali ruoli di moderatore.

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

Spiegazione della query SQL

Ecco una ripartizione della query:

• Parametri: La query accetta due parametri, :start_date e :end_date, che consentono all’utente di specificare l’intervallo di date per il report. Entrambi i parametri di data accettano il formato data AAAA-MM-GG.
• Clausola SELECT: La query seleziona le seguenti colonne:
  • user_id: L’identificatore univoco dell’utente.
  • username: Il nome utente dell’utente.
  • silenced: Un valore booleano che indica se l’utente è attualmente silenziato.
  • disagreed_flags: Il numero totale di segnalazioni dell’utente con cui i moderatori non sono stati d’accordo.
  • agreed_flags: Il numero totale di segnalazioni dell’utente con cui i moderatori sono stati d’accordo.
  • ignored_flags: Il numero totale di segnalazioni dell’utente che sono state ignorate dai moderatori.
  • score: Un punteggio calcolato che rappresenta le prestazioni di segnalazione dell’utente.
• Clausola FROM: La query unisce la tabella users con la tabella reviewable_scores sull’user_id per ottenere informazioni di segnalazione relative a ciascun utente.
• Clausola WHERE: La query filtra i dati per includere solo i record degli utenti con un id maggiore di 0 e dove la data created_at dei punteggi revisionabili rientra nell’intervallo di date specificato.
• Clausola GROUP BY: La query raggruppa i risultati per user_id, username, uploaded_avatar_id e silenced_till per aggregare i dati di segnalazione per ciascun utente.
• Clausola ORDER BY: La query ordina i risultati in base allo score calcolato in ordine decrescente per mostrare prima gli utenti con le migliori prestazioni di segnalazione.
• Clausola LIMIT: La query limita i risultati ai primi 100 utenti in base allo score calcolato.

Esempio di risultati