Informe del panel - Me gusta

Comunidad Datos e informes
, ,
4

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

  1. 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 = true
  • moderator = true
  • trust_level = 4
  1. 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.
  2. 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.
  3. Agregar “me gusta” totales:
    Se introdujo una columna total_likes para sumar regular_likes_count y staff_likes_count.
  4. 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:

  1. 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 = true
    • moderator = true
    • trust_level = 4
  1. 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.
  1. Agregar “me gusta” totales:
  • Incluir una columna total_likes que sume regular_likes_count y staff_likes_count.
  1. Agregar publicaciones por período:
  • Agregar una columna posts_per_week que cuente el número de publicaciones creadas durante cada semana.
  1. 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.
  1. 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).
  1. Ordenar y columnas finales:
  • Asegurarse de que la salida esté ordenada por week_start y que incluya las siguientes columnas en este orden:
    1. week_start: La fecha de inicio de la semana.
    2. regular_likes_count: El recuento de “me gusta” de usuarios no personal.
    3. staff_likes_count: El recuento de “me gusta” de usuarios del personal.
    4. total_likes: La suma de regular_likes_count y staff_likes_count.
    5. posts_per_week: El número de publicaciones creadas durante la semana.