Dashboard-Bericht - Top-Benutzer nach erhaltenen Likes von einem Benutzer mit niedrigerem Vertrauenslevel

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für Top-Benutzer nach erhaltenen Likes von einem Benutzer mit einem niedrigeren Vertrauensniveau.

Dieser Dashboard-Bericht zielt darauf ab, zu identifizieren, welche Benutzer die meisten Likes von Mitgliedern niedrigerer Vertrauensniveaus innerhalb eines bestimmten Zeitrahmens erhalten haben. Der Bericht konzentriert sich auf die Interaktion zwischen Benutzern unterschiedlicher Vertrauensniveaus und hebt Konten hervor, die sich durch Community-Engagement auszeichnen, indem sie von Personen mit geringeren Berechtigungen oder weniger Erfahrung im Forum geliked werden.

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

Erklärung der SQL-Abfrage

  • Datumsparameter:
    • Die Abfrage akzeptiert zwei Parameter, :start_date und :end_date, die den Datumsbereich für den Bericht definieren. Beide Datumsparameter akzeptieren das Datumsformat JJJJ-MM-TT.
  • Common Table Expression (CTE):
    • Die CTE user_liked_tl_lower wird am Anfang zur Wiederverwendung innerhalb der Hauptabfrage definiert.
    • Sie berechnet die Gesamtzahl der Likes, die jeder Benutzer innerhalb des durch :start_date und :end_date definierten Zeitrahmens für seine Beiträge von Benutzern mit niedrigeren Vertrauensniveaus erhalten hat.
  • Inner Joins:
    • Joins werden zwischen der users-Tabelle und den Tabellen posts und user_actions durchgeführt, um die Aktionen herauszufiltern, die „Likes“ sind (angezeigt durch action_type = 1).
    • Ein zusätzlicher Join zur users-Tabelle (u_liked) wird durchgeführt, um sicherzustellen, dass der Likende ein niedrigeres Vertrauensniveau als der Autor des Beitrags hat.
  • Rangfolge:
    • Die Ergebnisse werden nach dem Vertrauensniveau des Benutzers partitioniert und nach der Anzahl der Likes in absteigender Reihenfolge sortiert.
    • Basierend auf der Anzahl der Likes wird ein Rang zugewiesen, wobei Gleichstände den gleichen Rang erhalten und die nächste Rangnummer entsprechend übersprungen wird (dies ist eine Standardrangfolge, keine dichte Rangfolge).
  • Filtern der Ergebnisse in der CTE:
    • Es werden nur die Likes gezählt, die innerhalb des angegebenen Zeitraums vergeben wurden.
  • Finale Auswahl:
    • Die Hauptabfrage wählt alle Spalten aus der CTE aus, bei denen der rank 10 oder niedriger ist.
    • Dies beschränkt die Ergebnisse auf die Top-10-Benutzer in jeder Vertrauensniveaukategorie, die die meisten Likes von Benutzern mit niedrigeren Vertrauensniveaus erhalten haben.

Beispielergebnisse

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 „Gefällt mir“