Gostaria de criar um relatório baseado em SQL que possa fornecer uma compreensão sobre sobre o que nossos usuários estão postando.
Categorias, tópicos, tags - esses são os campos que determinei que me fornecerão isso, e postcount seria a métrica (acho que, essencialmente, querendo usar isso para entender a popularidade)
Existem outros campos que apoiariam isso? Existe uma consulta existente que posso usar, pois presumo que outros já solicitaram isso?
não é uma consulta SQL, mas presuma que você notou que:
/categories fornece um número de novos Tópicos/mês para cada Categoria, o que pode ser muito útil.
/tags fornece uma contagem de Tópicos por tag
Caso você não estivesse ciente (mas talvez estivesse!)
Não tenho certeza a que você está se referindo aqui.
São estes os relatórios na parte administrativa de uma comunidade?
Eu já vi isso, mas quero essencialmente tê-los todos em um só lugar - para que você possa ver quantos tópicos ou tags uma categoria tem, etc.
são caminhos de URL.
Não tenho certeza se consigo visualizar exatamente o que você tem em mente para incluir categorias, tags e tópicos, com uma contagem de posts para cada um, no mesmo relatório/consulta.
Acho que você poderia fazer isso em duas partes - uma para categorias e outra para tags, com uma contagem de novos tópicos e novos posts dentro de um determinado período para cada uma. Talvez até incluir o número de usuários postando?
Assim, daria algo como isto como tabela de resultados:
| categoria | novos tópicos | novos posts | usuários |
|---|---|---|---|
| categoria a | 9 | 15 | 4 |
| categoria b | 56 | 167 | 32 |
Ok, então eu inseri Categorias - Discourse Meta como eu assumo que é o que você quis dizer.
Essencialmente, eu quero uma saída de categorias/tags/tópicos em uma única saída por estas razões:
|categoria|tópico|tag|posts|usuários|
| — | — | — | — |—|\n|carros|amar carros|rodas|44|1\n|carros|odiar carros|rodas|32|3\n|carros|odiar carros|portas|39|4\n|carros|como os carros funcionam?|portas|32|1\n|como as coisas funcionam|como os carros funcionam?|como fazer|32|3\n\nEste é um exemplo da visão. Isso permitiria uma análise mais aprofundada para entender coisas como “qual % de usuários que postam na categoria carros falam sobre rodas”\n\nEu entendo que estaria no explorador de dados combinando posts, tópicos, tags, categorias, possivelmente outra tabela - eu apenas postei aqui para entender se isso já foi feito/onde existem consultas SQL que foram criadas anteriormente (não em nossa conta, quero dizer em geral)\n\nEspero que isso faça sentido, comecei a investigar ontem
Ah, entendi agora. Você quer uma lista de tópicos com detalhes extras em vez de uma visão geral resumida. Acho que isso é possível. 
Acho que a única peculiaridade seria que um tópico pode ter várias tags, mas vamos criar algo e ver o resultado. 
Mas para navegar por outras consultas existentes, há as disponíveis como padrão no seu painel (você pode ver as versões SQL aqui agrupadas em dashboard-sql), bem como alguns relatórios padrão incluídos no explorador de dados, e há uma série de relatórios personalizados aqui no meta agrupados sob a tag sql-query.
@SStrong - Talvez algo como isto:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH tag_names AS (
SELECT
t.id AS topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
JOIN topic_tags tt ON tt.topic_id = t.id
JOIN tags ON tags.id = tt.tag_id
WHERE t.created_at BETWEEN :start_date AND :end_date
GROUP BY t.id
),
user_count AS (
SELECT
p.topic_id,
COUNT(DISTINCT p.user_id) AS users
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
AND p.post_type = 1
AND p.user_id > 0
GROUP BY p.topic_id
)
SELECT
t.category_id,
t.id AS topic_id,
tn.tags,
t.posts_count,
uc.users
FROM topics t
JOIN tag_names tn ON tn.topic_id = t.id
JOIN user_count uc ON uc.topic_id = t.id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title
Desculpe, pensei que já tivesse respondido a isso.
É possível alterar o código para conter o nome da categoria e o nome do tópico, por favor? Não sei se “nome do post” é uma coisa ou se esse é realmente o nome do tópico?
Eu tentei alterar o código sozinho, mas não entendo o comportamento das tabelas juntas o suficiente no momento, pois o resultado foram 0 resultados em vez do mesmo volume de registros, mas com palavras em vez de IDs.
Não há problema. Ao visualizar no explorador de dados, category_id e topic_id (e muitos outros) são automaticamente convertidos em links utilizáveis no site, mas se exportar para analisar em outro lugar, você pode usar os nomes das categorias e os títulos dos tópicos em vez disso:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31
WITH tag_names AS (
SELECT
t.id AS topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
JOIN topic_tags tt ON tt.topic_id = t.id
JOIN tags ON tags.id = tt.tag_id
WHERE t.created_at BETWEEN :start_date AND :end_date
GROUP BY t.id
),
user_count AS (
SELECT
p.topic_id,
COUNT(DISTINCT p.user_id) AS users
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND t.archetype = 'regular'
AND p.post_type = 1
AND p.user_id > 0
GROUP BY p.topic_id
)
SELECT
c.name AS category_name,
t.title,
tn.tags,
t.posts_count,
uc.users
FROM topics t
JOIN tag_names tn ON tn.topic_id = t.id
JOIN user_count uc ON uc.topic_id = t.id
JOIN categories c ON c.id = t.category_id
WHERE t.created_at BETWEEN :start_date AND :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
ORDER BY t.category_id, t.title