Rapport du tableau de bord - Meilleurs utilisateurs par likes reçus d'un utilisateur avec un niveau de confiance inférieur

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour les meilleurs utilisateurs par likes reçus d’un utilisateur avec un niveau de confiance inférieur.

Ce rapport de tableau de bord vise à identifier les utilisateurs qui ont reçu le plus de likes de la part des membres de niveaux de confiance inférieurs dans un délai spécifié. Le rapport se concentre sur l’interaction entre les utilisateurs de différents niveaux de confiance et met en évidence les comptes qui se démarquent dans l’engagement communautaire en étant likés par ceux qui ont des permissions ou une expérience inférieures sur le 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

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.
  • Expression de table commune (CTE) :
    • La CTE user_liked_tl_lower est définie au début pour être réutilisée dans la requête principale.
    • Elle calcule le nombre total de likes que chaque utilisateur a reçus sur ses publications de la part d’utilisateurs ayant des niveaux de confiance inférieurs dans la période définie par :start_date et :end_date.
  • Jointures internes :
    • Des jointures sont effectuées entre la table users et les tables posts et user_actions pour filtrer les actions qui sont des « likes » (indiqué par action_type = 1).
    • Une jointure supplémentaire à la table users (u_liked) est effectuée pour s’assurer que celui qui like a un niveau de confiance inférieur à celui de l’auteur de la publication.
  • Classement :
    • Les résultats sont partitionnés par le niveau de confiance de l’utilisateur et triés par le nombre de likes dans l’ordre décroissant.
    • Un classement est attribué en fonction du nombre de likes, les égalités recevant le même rang et le rang suivant sautant des numéros en conséquence (il s’agit d’un classement standard, pas d’un classement dense).
  • Filtrage des résultats dans la CTE :
    • Seuls les likes donnés dans la période spécifiée sont comptés.
  • Sélection finale :
    • La requête principale sélectionne toutes les colonnes de la CTE où le rank est inférieur ou égal à 10.
    • Cela limite les résultats aux 10 meilleurs utilisateurs de chaque catégorie de niveau de confiance qui ont reçu le plus de likes de la part d’utilisateurs de niveaux de confiance inférieurs.

Exemple de résultats

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 « J'aime »