Dashboard-Bericht - Likes

Gemeinschaft Daten & Berichterstattung
, ,
4

Mein Forum ist nicht sehr groß, und die meisten unserer Reaktionen stammen von „Mitarbeitern“ (Admins, Moderatoren, TL=4). Ich wollte sehen, wie sich die Likes von normalen Benutzern im Vergleich zu „Mitarbeitern“ verhalten, und die Anzahl der Beiträge pro Tag auflisten, um besser zu verstehen, was vor sich geht und wo wir unsere Bemühungen konzentrieren müssen, um die Nutzung von Reaktionen zu verbessern.

Mein Kumpel ChatGPT und ich haben uns das ausgedacht:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31

WITH date_range AS (
  SELECT date_trunc('day', series) AS date
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.date::date,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', p.created_at) AS post_date,
    COUNT(*) AS posts_per_day
  FROM posts p
  WHERE p.created_at >= :start_date
    AND p.created_at <= (:end_date::date + 1)
  GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date

Änderungen an @SaraDevs ursprünglicher Abfrage (danke, Sara!):
Zusammenfassung der SQL-Änderungen

  1. Mitarbeitergruppe generieren:
    Eine staff_users-CTE wurde hinzugefügt, um Mitarbeiterbenutzer aus der users-Tabelle zu identifizieren. Ein Mitarbeiterbenutzer wird als einer der folgenden definiert:
    • admin = true
    • moderator = true
    • trust_level = 4
  2. Mitarbeiter-Likes separat erfassen:
    Eine Unterabfrage wurde hinzugefügt, um die Anzahl der Likes von Mitarbeitern (staff_likes_count) zu berechnen, indem post_actions nach user_id in der staff_users-Gruppe gefiltert wird.
  3. Spalte für Nicht-Mitarbeiter-Likes umbenennen:
    Die Ausgabekennzeichnung für Nicht-Mitarbeiter-Likes wurde von likes_count in regular_likes_count geändert.
  4. Gesamt-Likes hinzufügen:
    Eine Spalte total_likes wurde eingeführt, um regular_likes_count und staff_likes_count zu summieren.
  5. Beiträge pro Tag hinzufügen:
    Eine Unterabfrage wurde hinzugefügt, um die Anzahl der Beiträge pro Tag (posts_per_day) zu berechnen und sie mit dem Datumsbereich zu verknüpfen.
    (Ja, ChatGPT hat diese Liste der Änderungen auch für mich erstellt.)

Beispielergebnisse:

Datum regular_likes_count staff_likes_count posts_per_day
01.01.24 0 6 7
02.01.24 0 5 3
03.01.24 1 0 4
04.01.24 1 2 5
05.01.24 9 9 30
06.01.24 0 1 11
07.01.24 2 4 11
08.01.24 0 5 18
09.01.24 0 0 2
10.01.24 0 0 7
11.01.24 0 4 5
12.01.24 4 0 4
13.01.24 6 0 10
14.01.24 1 7 18
15.01.24 2 4 7
Gleiche Abfrage wochenweise gemeldet zur Glättung 
-- [params]
-- integer :weeks_ago = 52

WITH date_range AS (
  SELECT date_trunc('week', series) AS week_start
  FROM generate_series(
    date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
    date_trunc('week', now()),
    '1 week'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.week_start::date AS week_start,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_non_staff ON dr.week_start = pa_non_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_staff ON dr.week_start = pa_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', p.created_at) AS post_week,
    COUNT(*) AS posts_per_week
  FROM posts p
  WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND p.created_at <= date_trunc('week', now())
  GROUP BY post_week
) posts_count ON dr.week_start = posts_count.post_week
ORDER BY dr.week_start
Falls es interessant ist, hier sind die endgültigen Prompts, die Saras Abfrage modifiziert haben:

Ich habe eine SQL-Abfrage, die die tägliche Anzahl von Likes (likes_count) zwischen zwei Daten meldet, aber ich muss die folgenden Verbesserungen vornehmen, um eine endgültige Ausgabe zu erstellen, die Daten nach Wochen aggregiert und zusätzliche Details enthält:

  1. Definieren einer Mitarbeitergruppe:
    • Erstellen Sie eine staff_users-Gruppe aus der users-Tabelle. Ein Benutzer sollte als Mitarbeiter betrachtet werden, wenn er eine der folgenden Kriterien erfüllt:
      • admin = true
      • moderator = true
      • trust_level = 4
  2. Likes nach Mitarbeitern und Nicht-Mitarbeitern trennen:
    • Fügen Sie zwei separate Spalten hinzu:
      • regular_likes_count: Anzahl der Likes von Nicht-Mitarbeitern.
      • staff_likes_count: Anzahl der Likes von Mitarbeitern.
    • Stellen Sie sicher, dass die Spalte regular_likes_count Likes von Mitarbeitern ausschließt.
  3. Gesamt-Likes hinzufügen:
    • Fügen Sie eine Spalte total_likes hinzu, die regular_likes_count und staff_likes_count summiert.
  4. Beiträge pro Zeitraum hinzufügen:
    • Fügen Sie eine Spalte posts_per_week hinzu, die die Anzahl der Beiträge zählt, die während jeder Woche erstellt wurden.
  5. Nach Wochen aggregieren:
    • Ändern Sie die Abfrage so, dass alle Daten nach wöchentlichen Intervallen anstatt täglich gruppiert werden.
    • Fügen Sie eine Spalte week_start hinzu, die das Startdatum jeder Woche darstellt.
  6. Nach Wochen begrenzen:
    • Führen Sie einen Parameter :weeks_ago ein, um die Ergebnisse auf die letzten N Wochen zu beschränken. Der Standardwert sollte 52 Wochen (1 Jahr) sein.
  7. Sortieren und endgültige Spalten:
    • Stellen Sie sicher, dass die Ausgabe nach week_start sortiert ist und die folgenden Spalten in dieser Reihenfolge enthält:
      1. week_start: Das Startdatum der Woche.
      2. regular_likes_count: Die Anzahl der Likes von Nicht-Mitarbeitern.
      3. staff_likes_count: Die Anzahl der Likes von Mitarbeitern.
      4. total_likes: Die Summe von regular_likes_count und staff_likes_count.
      5. posts_per_week: Die Anzahl der Beiträge, die während der Woche erstellt wurden.