Rapport du tableau de bord - J'aime

NiceOldGuy · Janvier 22, 2025, 10:03

Mon forum n’est pas très grand, et la plupart de nos réactions “like” proviennent du “staff” (administrateurs, modérateurs, TL=4). Je voulais voir comment les likes des utilisateurs réguliers par rapport au “staff” se comparaient, et lister le nombre de messages/jour pour mieux comprendre ce qui se passe et où nous devons concentrer nos efforts pour améliorer l’utilisation des réactions.

Mon ami ChatGPT et moi avons conçu ceci :

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

Modifications apportées à la requête originale de @SaraDev (merci, Sara !) :
Résumé des modifications SQL

Génération du groupe de staff :
Ajout d’une CTE staff_users pour identifier les utilisateurs du staff à partir de la table users. Un utilisateur du staff est défini comme tout utilisateur répondant à l’un des critères suivants :

admin = true
moderator = true
trust_level = 4

Séparation des likes du staff :
Ajout d’une sous-requête pour calculer le nombre de likes provenant des utilisateurs du staff (staff_likes_count) en filtrant post_actions pour les user_id du groupe staff_users.
Renommage de la colonne de likes des non-staff :
Modification de l’étiquette de sortie pour les likes des non-staff de likes_count à regular_likes_count.
Ajout du total des likes :
Introduction d’une colonne total_likes pour additionner regular_likes_count et staff_likes_count.
Ajout des posts par jour :
Ajout d’une sous-requête pour calculer le nombre de posts par jour (posts_per_day) et jointure avec la plage de dates.
(Oui, ChatGPT a également fait cette liste de changements pour moi.)

Exemple de résultats :

date	regular_likes_count	staff_likes_count	posts_per_day
1/1/24	0	6	7
1/2/24	0	5	3
1/3/24	1	0	4
1/4/24	1	2	5
1/5/24	9	9	30
1/6/24	0	1	11
1/7/24	2	4	11
1/8/24	0	5	18
1/9/24	0	0	2
1/10/24	0	0	7
1/11/24	0	4	5
1/12/24	4	0	4
1/13/24	6	0	10
1/14/24	1	7	18
1/15/24	2	4	7

Même requête rapportée par semaines pour lisser

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

Au cas où ce serait intéressant, voici les invites finales qui ont modifié la requête de Sara :

J’ai une requête SQL qui rapporte le nombre quotidien de likes (likes_count) entre deux dates, mais je dois apporter les améliorations suivantes pour produire un résultat final qui agrège les données par semaines et inclut des détails supplémentaires :

Définir un groupe de staff :

Créer un groupe staff_users à partir de la table users. Un utilisateur doit être considéré comme faisant partie du staff s’il remplit l’un des critères suivants :
- admin = true
- moderator = true
- trust_level = 4

Séparer les likes par staff et non-staff :

Ajouter deux colonnes distinctes :
- regular_likes_count : Compte les likes des utilisateurs non-staff.
- staff_likes_count : Compte les likes des utilisateurs du staff.
Assurez-vous que la colonne regular_likes_count exclut les likes générés par les utilisateurs du staff.

Ajouter le total des likes :

Inclure une colonne total_likes qui additionne regular_likes_count et staff_likes_count.

Ajouter les posts par période :

Ajouter une colonne posts_per_week qui compte le nombre de posts créés chaque semaine.

Agrégation par semaines :

Modifier la requête pour regrouper toutes les données par intervalles hebdomadaires au lieu de quotidiens.
Inclure une colonne week_start qui représente la date de début de chaque semaine.

Limiter par semaines précédentes :

Introduire un paramètre :weeks_ago pour limiter les résultats aux N dernières semaines. La valeur par défaut doit être 52 semaines (1 an).

Ordre et colonnes finales :

Assurez-vous que le résultat est trié par week_start et inclut les colonnes suivantes dans cet ordre :
1. week_start : La date de début de la semaine.
2. regular_likes_count : Le nombre de likes des utilisateurs non-staff.
3. staff_likes_count : Le nombre de likes des utilisateurs du staff.
4. total_likes : La somme de regular_likes_count et staff_likes_count.
5. posts_per_week : Le nombre de posts créés pendant la semaine.

Sujet		Réponses	Vues
Daily, weekly, or total stats by user over a specified time range Data & reporting sql-query	0	223	Décembre 31, 2019
Dashboard Report - Reactions Data & reporting reactions , sql-query , dashboard-reports , dashboard-sql	2	557	Janvier 19, 2024
Dashboard Report - Top Users by Likes Received Data & reporting sql-query , dashboard-reports , dashboard-sql	0	332	Février 1, 2024
Dashboard Report - Top Users by Likes Received From a Variety of People Data & reporting sql-query , dashboard-reports , dashboard-sql	0	310	Février 1, 2024
Top 50 likers Data & reporting sql-query	0	185	Février 12, 2018

Rapport du tableau de bord - J'aime

Sujets connexes