Dashboard-Bericht – Top-Benutzer nach erhaltenen Likes von verschiedenen Personen

Daten & Berichterstattung
, ,
1

Dies ist eine SQL-Version des Dashboard-Berichts für Top-Benutzer nach erhaltenen Likes von einer Vielzahl von Personen.

Dieser Dashboard-Bericht zeigt die Top-Benutzer, die Likes von einer großen Anzahl von Website-Benutzern über einen bestimmten Zeitraum erhalten haben. Die Abfrage soll ermitteln, welche Benutzer die meisten Likes für ihre Beiträge erhalten haben, wobei jede Like-Anzahl die Anzahl der eindeutigen Benutzer darstellt, die ihre Beiträge geliked haben.

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

SQL-Abfrageerklärung

  • 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.
  • Auswahl: Die wichtigsten Spalten, die für die Ausgabe ausgewählt werden, sind:
    • p.user_id: Die Benutzer-ID des Posters.
    • u.username: Der entsprechende Benutzername.
    • likes: Eine Zählung der eindeutigen Benutzer-IDs (ua.user_id), die die Beiträge „geliked“ haben, um sicherzustellen, dass eindeutige Likes pro Benutzer berechnet werden.
  • Joins: Es gibt zwei Inner Joins:
    • user_actions ua mit posts p: Ordnet die Aktionen ihren entsprechenden Beiträgen zu.
    • posts p mit users u: Verknüpft Beiträge mit ihren Autoren, um den Abruf von Benutzerinformationen zu erleichtern.
  • Filterung:
    • Die WHERE-Klausel wendet mehrere Filter an:
      • ua.created_at::date BETWEEN :start_date AND :end_date beschränkt die berücksichtigten Aktionen auf diejenigen innerhalb der Parameter.
      • ua.action_type = 1 isoliert „Like“-Aktionen von anderen Arten von Benutzeraktionen.
      • p.user_id > 0 schließt alle Likes aus, die nicht registrierten Benutzern (wie System- oder anonymen Benutzern) zugeordnet sind.
  • Gruppierung:
    • Die Ergebnismenge wird nach p.user_id und u.username gruppiert. Dies stellt sicher, dass die Likes pro Benutzer und dem entsprechenden Benutzernamen aggregiert werden.
  • Sortierung:
    • Die Ergebnisse werden nach der Anzahl der Likes (likes DESC) sortiert, wobei die am meisten geschätzten Benutzer zuerst angezeigt werden.

Beispielergebnisse

user username likes
user1 user1 100
user2 user2 90
user3 user3 80
… … …
3 „Gefällt mir“