Primeiramente, feliz ano novo!! 
Gostaria de saber a consulta SQL para obter o número médio de respostas por tópico feitas por usuários (excluindo administradores) por mês.
Caso contrário, uma forma de ter uma proporção do número total de posts publicados por membros VS o número total de posts publicados pela equipe por mês.
Obrigado!
Feliz Ano Novo (um pouco atrasado 
)
Para a média, você está procurando por tópicos criados por funcionários e não funcionários, mas com a contagem de respostas apenas de postagens de não funcionários? E você quer excluir apenas administradores, ou administradores e moderadores?
Olá Jammy!
Exatamente, a consulta pode listar todos os tópicos (criados por funcionários e não funcionários), mas a contagem de respostas apenas de postagens de não funcionários.
Por enquanto, só podemos excluir administradores (já que, ao lançar minha comunidade, administradores e moderadores são a mesma coisa :))
Mas também seria bom ter facilmente a proporção entre tópicos de funcionários VS tópicos de não funcionários (excluindo administradores).
Acho que algo assim lhe daria os números que você procura:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Mês",
ms.total_topics AS "Todos os Tópicos",
ms.total_posts AS "Todos os Posts",
ms.non_staff_posts AS "Posts de não-staff",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Posts de não-staff (% do total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Média de posts de não-staff por tópico",
ms.non_staff_users AS "Usuários de não-staff que postaram",
ms.staff_posts AS "Posts de staff",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Posts de staff (% do total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Média de posts de staff por tópico",
ms.staff_users AS "Usuários de staff que postaram"
FROM month_stats ms
ORDER BY "Mês"
O que lhe daria algo como isto:
E um pouco de texto extra para completar:
Esta consulta foi projetada para fornecer um resumo estatístico mensal da atividade do fórum, focando especificamente na distinção entre as contribuições de usuários ‘staff’ e ‘não-staff’ dentro de um determinado período. As métricas calculadas incluem o número total de tópicos criados, todos os posts feitos, o número de usuários únicos não-staff que postaram, a contagem e a porcentagem de posts feitos por não-staff, a média de posts de não-staff por tópico, bem como os números correspondentes para membros da staff. A informação destina-se a fornecer insights sobre o engajamento do usuário, a geração de conteúdo e a taxa de participação de membros staff versus não-staff nas discussões do fórum. A consulta garante precisão ao considerar apenas tópicos ‘regulares’ (não-PM) e exclui quaisquer posts ou tópicos excluídos, sussurros/posts pequenos/ações de moderador e posts de usuários do sistema, dentro do intervalo de datas especificado.
Para este, o critério ‘staff’ é que eles estão no grupo automático @staff, que inclui administradores e moderadores - embora isso possa ser ajustado para atingir apenas os administradores, ou até mesmo um grupo personalizado de funcionários que tecnicamente não são ‘staff do site’ como tal. Se você deseja definitivamente excluir moderadores, pode trocar o group_id no início para ‘1’. 
É esse tipo de coisa que você está procurando?
Por alguma razão estranha, sei que o ID da equipe é três. Mas como se poderia encontrar esse ID? Primeiro, eu tinha certeza de que estava embutido nas URLs, como todos os outros IDs, mas não. Apenas o nome está em uso.
Eu sei tão pouco SQL que podemos dizer facilmente que não sei, mas isso mostra todos os IDs de grupo.
select
id,
name
from
groups
Mas certamente existe uma maneira mais comum de encontrá-lo, não é?
Pessoalmente, eu realmente gostaria de uma pesquisa de parâmetro group_id assim como a de user_id 
- Param dropdown for group_id in data explorer query
Mas até que esse sonho se realize, eu uso o json da página de grupos para descobrir, por exemplo: https://meta.discourse.org/g.json
Você pode fazer uma pesquisa de grupo dentro da própria consulta, para que funcione com nomes de grupos, o que pode ser uma maneira mais amigável de fazer isso. Algo como:
-- [params]
-- string :group_name
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = LOWER(:group_name))
(ou a versão codificada se você não quisesse um parâmetro:)
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
Muito obrigado! Talvez eu tenha perdido alguma coisa, mas quando clico em “Executar”, recebo este erro:
Ah sim, essa é uma peculiaridade que eu deveria ter mencionado. Se você atualizar sua página, as caixas de entrada de parâmetros deverão aparecer.
Aposto que você criou esse trecho na hora. Porque não funciona muito bem 
Ele mostra todo usuário do grupo desejado e afirma que todos são is_staff 
Mas obrigado! Obtive informações valiosas para administradores de nível básico, sobre json e como usar SQL (realmente, mas ainda gosto de ver como a IA-report vê isso…)
Nesse exemplo, o bit is_staff faz parte da função desta consulta específica. Ele é adicionado especificamente aqui SELECT user_id, true as is_staff em vez de vir do próprio banco de dados. Ele define qualquer pessoa do grupo que você designar como ‘staff’ para que possam ser divididas nos dois conjuntos de resultados (posts de staff versus posts de não-staff).
Portanto, se você tivesse um grupo para ‘funcionários’, que tecnicamente não seriam staff do site como o banco de dados os define, você ainda poderia adicioná-los e eles iriam para o ‘balde’ de ‘staff’ e não para o ‘balde’ de ‘não-staff’.
OMG, isso é exatamente o que eu precisava, muito obrigado!
Para ter certeza: “Posts” compila tópicos + respostas, ou conta apenas as respostas?
Obrigado novamente!
Para este, ‘posts’ não inclui o primeiro post do tópico, então são apenas as respostas.
Olá @JammyDodger
Você acha que é possível ter o mesmo, mas apenas para tópicos (= nova thread criada), por favor?
Muito obrigado!
Você quer dizer uma proporção entre tópicos criados por funcionários versus não funcionários adicionados a esta consulta?
Ah sim, na mesma consulta seria ótimo!
Acho que adicionar estas colunas deve resolver:
-- [params]
-- date :start_date
-- date :end_date
WITH staff_data AS (
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = 3
),
month_stats AS (
SELECT
date_trunc('month', p.created_at)::date AS month,
COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS NOT TRUE) AS non_staff_topics,
COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS TRUE) AS staff_topics,
COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN staff_data s ON p.user_id = s.user_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_type = 1
AND p.user_id > 0
GROUP BY month
)
SELECT
ms.month AS "Mês",
ms.total_topics AS "Todos os Tópicos",
ms.non_staff_topics AS "Tópicos não-staff",
ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Tópicos não-staff (% do total)",
ms.staff_topics AS "Tópicos staff",
ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Tópicos staff (% do total)",
ms.total_posts AS "Todos os Posts",
ms.non_staff_posts AS "Posts não-staff",
ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Posts não-staff (% do total)",
ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Posts médios não-staff por tópico",
ms.non_staff_users AS "Usuários não-staff que postaram",
ms.staff_posts AS "Posts staff",
ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Posts staff (% do total)",
ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Posts médios staff por tópico",
ms.staff_users AS "Usuários staff que postaram"
FROM month_stats ms
ORDER BY "Mês"
Muito obrigado, isso está perfeito!
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
