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 = truemoderator = truetrust_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 = truemoderator = truetrust_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 :
week_start : La date de début de la semaine.regular_likes_count : Le nombre de likes des utilisateurs non-staff.staff_likes_count : Le nombre de likes des utilisateurs du staff.total_likes : La somme de regular_likes_count et staff_likes_count.posts_per_week : Le nombre de posts créés pendant la semaine.