Relatório do Painel - Curtidas

Comunidade Dados e relatórios
, ,
4

Meu fórum não é muito grande, e a maioria das nossas reações de “like” vêm de “staff” (administradores, moderadores, TL=4). Eu queria ver como os likes de usuários regulares versus “staff” se comparavam, e listar o número de posts/dia para ter uma melhor noção do que está acontecendo e onde precisamos concentrar esforços para melhorar o uso das reações.

Eu e meu amigo ChatGPT criamos isto:

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

Alterações na consulta original de @SaraDev (obrigado, Sara!):
Resumo das Alterações no SQL

  1. Gerar Grupo de Staff:
    Adicionada uma CTE staff_users para identificar usuários de staff a partir da tabela users. Um usuário de staff é definido como qualquer um dos seguintes:
  • admin = true
  • moderator = true
  • trust_level = 4
  1. Separar Likes de Staff:
    Adicionada uma subconsulta para calcular a contagem de likes de usuários de staff (staff_likes_count) filtrando post_actions para user_id no grupo staff_users.
  2. Renomear Coluna de Likes de Não-Staff:
    Alterado o rótulo de saída para likes de não-staff de likes_count para regular_likes_count.
  3. Adicionar Total de Likes:
    Introduzida uma coluna total_likes para somar regular_likes_count e staff_likes_count.
  4. Adicionar Posts por Dia:
    Adicionada uma subconsulta para calcular o número de posts por dia (posts_per_day) e juntada ao intervalo de datas.
    (Sim, o ChatGPT fez essa lista de alterações para mim também.)

Exemplo de resultados:
|data|regular_likes_count|staff_likes_count|posts_per_day|
|:—:|:—:|:—:|:—:expressionless:
|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|\

Mesma 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
Caso seja interessante, aqui estão os prompts finais que modificaram a consulta de Sara:

Tenho uma consulta SQL que reporta a contagem diária de likes (likes_count) entre duas datas, mas preciso fazer as seguintes melhorias para produzir um resultado final que agregue dados por semanas e inclua detalhes adicionais:

  1. Definir um Grupo de Staff:
  • Criar um grupo staff_users a partir da tabela users. Um usuário deve ser considerado staff se atender a qualquer um dos seguintes critérios:
    • admin = true
    • moderator = true
    • trust_level = 4
  1. Separar Likes por Staff e Não-Staff:
  • Adicionar duas colunas separadas:
    • regular_likes_count: Contagem de likes de usuários não-staff.
    • staff_likes_count: Contagem de likes de usuários staff.
  • Garantir que a coluna regular_likes_count exclua os likes gerados por usuários staff.
  1. Adicionar Total de Likes:
  • Incluir uma coluna total_likes que some regular_likes_count e staff_likes_count.
  1. Adicionar Posts por Período:
  • Adicionar uma coluna posts_per_week que conte o número de posts criados durante cada semana.
  1. Agregar por Semanas:
  • Modificar a consulta para agrupar todos os dados por intervalos semanais em vez de diários.
  • Incluir uma coluna week_start que represente a data de início de cada semana.
  1. Limitar por Semanas Atrás:
  • Introduzir um parâmetro :weeks_ago para limitar os resultados às últimas N semanas. O valor padrão deve ser 52 semanas (1 ano).
  1. Ordenar e Colunas Finais:
  • Garantir que a saída seja ordenada por week_start e inclua as seguintes colunas nesta ordem:
    1. week_start: A data de início da semana.
    2. regular_likes_count: A contagem de likes de usuários não-staff.
    3. staff_likes_count: A contagem de likes de usuários staff.
    4. total_likes: A soma de regular_likes_count e staff_likes_count.
    5. posts_per_week: O número de posts criados durante a semana.