Mi foro no es muy grande y la mayoría de nuestras reacciones de “me gusta” provienen del “personal” (administradores, moderadores, TL=4). Quería ver cómo se comparaban los “me gusta” de los usuarios habituales frente a los del “personal”, y listar el número de publicaciones por día para tener una mejor idea de lo que está sucediendo y dónde necesitamos concentrar los esfuerzos para mejorar el uso de las reacciones.
Mi amigo ChatGPT y yo ideamos esto:
-- [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
Cambios en la consulta original de @SaraDev (¡gracias, Sara!):
Resumen de cambios en SQL
- Generar grupo de personal:
Se agregó una CTE staff_users para identificar a los usuarios del personal de la tabla users. Un usuario del personal se define como cualquiera de:
admin = truemoderator = truetrust_level = 4
- Separar “me gusta” del personal:
Se agregó una subconsulta para calcular el recuento de “me gusta” de los usuarios del personal (staff_likes_count) filtrando post_actions por user_id en el grupo staff_users.
- Renombrar columna de “me gusta” de no personal:
Se cambió la etiqueta de salida para los “me gusta” de no personal de likes_count a regular_likes_count.
- Agregar “me gusta” totales:
Se introdujo una columna total_likes para sumar regular_likes_count y staff_likes_count.
- Agregar publicaciones por día:
Se agregó una subconsulta para calcular el número de publicaciones por día (posts_per_day) y se unió al rango de fechas.
(Sí, ChatGPT también me hizo esta lista de cambios.)
Resultados de ejemplo:
|fecha|regular_likes_count|staff_likes_count|posts_per_day|
|:—:|:—:|:—:|:—:|\n|1/1/24|0|6|7|\n|1/2/24|0|5|3|\n|1/3/24|1|0|4|\n|1/4/24|1|2|5|\n|1/5/24|9|9|30|\n|1/6/24|0|1|11|\n|1/7/24|2|4|11|\n|1/8/24|0|5|18|\n|1/9/24|0|0|2|\n|1/10/24|0|0|7|\n|1/11/24|0|4|5|\n|1/12/24|4|0|4|\n|1/13/24|6|0|10|\n|1/14/24|1|7|18|\n|1/15/24|2|4|7|
La misma consulta reportada por semanas para suavizar
-- [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
En caso de que sea interesante, aquí están las indicaciones finales que modificaron la consulta de Sara:
Tengo una consulta SQL que reporta el recuento diario de “me gusta” (likes_count) entre dos fechas, pero necesito hacer las siguientes mejoras para producir un resultado final que agrupe los datos por semanas e incluya detalles adicionales:
- Definir un grupo de personal:
- Crear un grupo
staff_users a partir de la tabla users. Un usuario se considerará personal si cumple alguno de los siguientes criterios:
admin = truemoderator = truetrust_level = 4
- Separar “me gusta” por personal y no personal:
- Agregar dos columnas separadas:
regular_likes_count: Cuenta los “me gusta” de usuarios no personal.staff_likes_count: Cuenta los “me gusta” de usuarios del personal.
- Asegurarse de que la columna
regular_likes_count excluya los “me gusta” generados por usuarios del personal.
- Agregar “me gusta” totales:
- Incluir una columna
total_likes que sume regular_likes_count y staff_likes_count.
- Agregar publicaciones por período:
- Agregar una columna
posts_per_week que cuente el número de publicaciones creadas durante cada semana.
- Agrupar por semanas:
- Modificar la consulta para agrupar todos los datos por intervalos semanales en lugar de diarios.
- Incluir una columna
week_start que represente la fecha de inicio de cada semana.
- Limitar por semanas anteriores:
- Introducir un parámetro
:weeks_ago para limitar los resultados a las últimas N semanas. El valor predeterminado debe ser 52 semanas (1 año).
- Ordenar y columnas finales:
- Asegurarse de que la salida esté ordenada por
week_start y que incluya las siguientes columnas en este orden:
week_start: La fecha de inicio de la semana.regular_likes_count: El recuento de “me gusta” de usuarios no personal.staff_likes_count: El recuento de “me gusta” de usuarios del personal.total_likes: La suma de regular_likes_count y staff_likes_count.posts_per_week: El número de publicaciones creadas durante la semana.