Dashboard Report - Primi Utenti per Mi piace Ricevuti da un Utente con un Trust Level Inferiore

Dati e reportistica
, ,
1

Questa è una versione SQL del report della dashboard per i migliori utenti per Mi piace ricevuti da un utente con un livello di fiducia inferiore.

Questo report della dashboard mira a identificare quali utenti hanno ricevuto il maggior numero di Mi piace da membri di livelli di fiducia inferiori entro un periodo di tempo specificato. Il report si concentra sull’interazione tra utenti di diversi livelli di fiducia ed evidenzia gli account che si distinguono nell’impegno della community essendo apprezzati da coloro che hanno minori permessi o esperienza nel forum.

--[params]
--date :start_date = 2024-01-01
--date :end_date = 2025-01-01

WITH user_liked_tl_lower AS (
    SELECT
        users.id AS user_id,
        users.username,
        users.trust_level,
        COUNT(*) AS likes,
        rank() OVER (
            PARTITION BY users.trust_level
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM users
    INNER JOIN posts p ON p.user_id = users.id
    INNER JOIN user_actions ua ON ua.target_post_id = p.id AND ua.action_type = 1
    INNER JOIN users u_liked ON ua.user_id = u_liked.id AND u_liked.trust_level < users.trust_level
    WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY users.id
    ORDER BY trust_level DESC, likes DESC
)

SELECT * FROM user_liked_tl_lower
WHERE rank <= 10

Spiegazione della query SQL

  • 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.
  • Common Table Expression (CTE):
    • La CTE user_liked_tl_lower è definita all’inizio per il riutilizzo all’interno della query principale.
    • Calcola il numero totale di Mi piace che ciascun utente ha ricevuto sui propri post da utenti con livelli di fiducia inferiori, entro il periodo di tempo specificato definito da :start_date e :end_date.
  • Inner Join:
    • Vengono eseguiti join tra la tabella users e le tabelle posts e user_actions per filtrare le azioni che sono “Mi piace” (indicate da action_type = 1).
    • Viene aggiunto un ulteriore join alla tabella users (u_liked) per garantire che chi ha messo “Mi piace” abbia un livello di fiducia inferiore rispetto all’autore del post.
  • Classificazione:
    • I risultati sono partizionati per il livello di fiducia dell’utente e ordinati per conteggio dei Mi piace in ordine decrescente.
    • Viene assegnata una classifica in base al numero di Mi piace, con i pareggi che ricevono la stessa classifica e la classifica successiva che salta i numeri di conseguenza (questa è una classifica standard, non una classifica densa).
  • Filtraggio dei risultati nella CTE:
    • Vengono conteggiati solo i Mi piace che sono stati dati entro il periodo di tempo specificato.
  • Selezione Finale:
    • La query principale seleziona tutte le colonne dalla CTE in cui la rank è 10 o inferiore.
    • Ciò limita i risultati ai primi 10 utenti in ciascuna categoria di livello di fiducia che hanno ricevuto il maggior numero di Mi piace da utenti con livelli di fiducia inferiori.

Risultati di esempio

user username trust_level likes rank
user1 user1 4 323 1
user2 user2 4 164 2
user3 user3 4 143 3
… … … …
user11 user11 3 45 1
user12 user12 3 34 2
… … … …
3 Mi Piace