Relatório do Painel - Usuário para Usuário

Dados e relatórios
, ,
1

Este é um relatório de painel em SQL para Usuário para Usuário (com respostas) e Usuário para Usuário (excluindo respostas).

Este relatório conta o número de mensagens pessoais enviadas por usuários a cada dia, dentro de um intervalo de datas especificado, fornecendo insights sobre a atividade de engajamento do usuário na comunicação privada.

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

Explicação da Consulta SQL

Esta consulta SQL executa as seguintes ações:

  • Parâmetros:
    • :start_date e :end_date, que são usados para filtrar os dados dentro do intervalo de datas especificado. Ambos os parâmetros de data aceitam o formato AAAA-MM-DD.
    • :include_replies para determinar se as respostas às mensagens iniciais devem ser incluídas na contagem. Definido como false por padrão para espelhar o comportamento do relatório Usuário para Usuário (excluindo respostas). Definir este parâmetro como true espelhará o comportamento do relatório Usuário para Usuário (com respostas).
  • Seleção de Dados:
    • A consulta seleciona a data created_at da tabela posts, que representa a data em que cada mensagem foi criada.
  • Operação de Junção:
    • A consulta executa um INNER JOIN entre as tabelas posts e topics em seus campos id. Essa junção é crucial, pois vincula cada postagem ao seu tópico correspondente, permitindo-nos filtrar apenas as postagens que fazem parte de uma conversa de mensagem pessoal.
  • Critérios de Filtragem: A consulta aplica vários filtros:
    • Ela considera apenas postagens que fazem parte de um tópico com um archetype de ‘private_message’, garantindo que apenas mensagens pessoais sejam contadas.
    • Ela filtra quaisquer postagens que não estejam associadas a um usuário, garantindo que user_id seja maior que 0.
    • Ela refina ainda mais os resultados para tópicos com um subtype de ‘user_to_user’, que indica uma mensagem pessoal entre usuários individuais.
    • Ela exclui quaisquer postagens ou tópicos que foram excluídos, verificando se deleted_at é NULL para ambas as tabelas.
    • Ela aplica o filtro de intervalo de datas para incluir apenas postagens criadas entre :start_date e :end_date.
    • Se :include_replies for falso, apenas a primeira postagem em cada tópico é contada (p.post_number = 1), excluindo respostas.
  • Agregação: A consulta agrupa os resultados pela data em que as postagens foram criadas, o que permite contar o número de mensagens enviadas em cada data.
  • Ordenação: Finalmente, a consulta ordena os resultados por data em ordem crescente, fornecendo uma sequência cronológica da atividade de mensagens pessoais de usuário para usuário.

Resultados de Exemplo

day personal_messages
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
4 curtidas