Report Dashboard - Utenti Top per Likes Ricevuti da Diverse Persone

Dati e reportistica
, ,
1

Questa è una versione SQL del report della dashboard per i migliori utenti per Mi piace ricevuti da una varietà di persone.

Questo report della dashboard mostra i migliori utenti che hanno ricevuto mi piace da un’ampia gamma di utenti di un sito, in un intervallo di date specificato. La query è progettata per scoprire quali utenti hanno ricevuto più mi piace ai loro post, con ogni conteggio di mi piace che rappresenta il numero di utenti univoci che hanno messo mi piace ai loro post.

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

SELECT
    p.user_id,
    u.username,
    COUNT(DISTINCT ua.user_id) AS likes
FROM
    user_actions ua
    INNER JOIN posts p ON p.id = ua.target_post_id
    INNER JOIN users u ON p.user_id = u.id
WHERE
    ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type = 1
    AND p.user_id > 0
GROUP BY
    p.user_id,
    u.username
ORDER BY
    likes DESC

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.
  • Selezione: Le colonne principali selezionate per l’output sono:
    • p.user_id: L’ID utente del poster.
    • u.username: Il nome dell’utente corrispondente.
    • likes: Un conteggio degli ID utente distinti (ua.user_id) che hanno messo “mi piace” ai post, garantendo il calcolo dei mi piace univoci per utente.
  • Join: Ci sono due join interni:
    • user_actions ua con posts p: Corrisponde alle azioni ai loro post corrispondenti.
    • posts p con users u: Collega i post ai loro autori, facilitando il recupero delle informazioni sull’utente.
  • Filtraggio:
    • La clausola WHERE applica diversi filtri:
      • ua.created_at::date BETWEEN :start_date AND :end_date limita le azioni considerate a quelle all’interno dei parametri.
      • ua.action_type = 1 isola le azioni di “mi piace” da altri tipi di azioni utente.
      • p.user_id > 0 esclude eventuali mi piace associati a utenti non registrati (come utenti di sistema o anonimi).
  • Raggruppamento:
    • Il set di risultati è raggruppato per p.user_id e u.username. Ciò garantisce che i mi piace vengano aggregati per utente e nome utente corrispondente.
  • Ordinamento:
    • I risultati sono ordinati per conteggio dei mi piace (likes DESC), presentando prima gli utenti più apprezzati.

Esempio di risultati

utente username mi piace
user1 user1 100
user2 user2 90
user3 user3 80
… … …
3 Mi Piace