Rapport du tableau de bord - Meilleurs utilisateurs par likes reçus de diverses personnes

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour les meilleurs utilisateurs par nombre de mentions J’aime reçues d’une variété de personnes.

Ce rapport de tableau de bord montre les meilleurs utilisateurs qui ont reçu des mentions J’aime d’un large éventail d’utilisateurs d’un site, sur une période spécifiée. La requête est conçue pour déterminer quels utilisateurs ont reçu le plus de mentions J’aime sur leurs publications, chaque décompte de mentions J’aime représentant le nombre d’utilisateurs uniques qui ont aimé leurs publications.

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

Explication de la requête SQL

  • Paramètres de date :
    • La requête accepte deux paramètres, :start_date et :end_date, qui définissent la plage de dates pour le rapport. Les deux paramètres de date acceptent le format de date AAAA-MM-JJ.
  • Sélection : Les principales colonnes sélectionnées pour la sortie sont :
    • p.user_id : L’identifiant de l’utilisateur de l’auteur de la publication.
    • u.username : Le nom d’utilisateur correspondant.
    • likes : Un décompte des identifiants d’utilisateur distincts (ua.user_id) qui ont “aimé” les publications, garantissant que les mentions J’aime uniques par utilisateur sont calculées.
  • Jointures : Il y a deux jointures internes :
    • user_actions ua avec posts p : Fait correspondre les actions à leurs publications correspondantes.
    • posts p avec users u : Relie les publications à leurs auteurs, facilitant la récupération des informations utilisateur.
  • Filtrage :
    • La clause WHERE applique plusieurs filtres :
      • ua.created_at::date BETWEEN :start_date AND :end_date limite les actions considérées à celles comprises dans les paramètres.
      • ua.action_type = 1 isole les actions de “mention J’aime” des autres types d’actions utilisateur.
      • p.user_id > 0 exclut toutes les mentions J’aime associées à des utilisateurs non enregistrés (comme les utilisateurs système ou anonymes).
  • Regroupement :
    • L’ensemble de résultats est regroupé par p.user_id et u.username. Cela garantit que les mentions J’aime sont agrégées par utilisateur et par nom d’utilisateur correspondant.
  • Tri :
    • Les résultats sont triés par le nombre de mentions J’aime (likes DESC), présentant d’abord les utilisateurs les plus appréciés.

Exemple de résultats

utilisateur nom d’utilisateur mentions J’aime
user1 user1 100
user2 user2 90
user3 user3 80
… … …
3 « J'aime »