# Informe del panel - Me gusta

**URL:** <https://meta.discourse.org/t/dashboard-report-likes/291092>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [9 Enero, 2024 23:56 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092 "2024-01-09T23:56:53Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![NiceOldGuy](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/niceoldguy/32/326207_2.png) [@NiceOldGuy](https://meta.discourse.org/u/NiceOldGuy)\
**Post date:** [22 Enero, 2025 22:03 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092/4 "2025-01-22T22:03:34Z")

</div>

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:**

```sql
-- [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**
>
> ```sql
> -- [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.

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-likes/291092)._