– [params]
– string :category_name
WITH selected_category AS (
SELECT id
FROM categories
WHERE name = :category_name
LIMIT 1
),
monthly_users AS (
SELECT
date_part(‘year’, created_at) AS ano,
date_part(‘month’, created_at) AS mes,
COUNT(*) AS novos_usuarios_mes
FROM users
GROUP BY 1, 2
),
monthly_posts AS (
SELECT
date_part(‘year’, created_at) AS ano,
date_part(‘month’, created_at) AS mes,
COUNT(*) AS contagem_posts
FROM posts p
WHERE p.deleted_at IS NULL
AND p.post_type = 1
GROUP BY 1, 2
),
monthly_active_users AS (
SELECT
date_part(‘year’, visited_at) AS ano,
date_part(‘month’, visited_at) AS mes,
COUNT(DISTINCT user_id) AS usuarios_ativos_mes
FROM user_visits uv
GROUP BY 1, 2
),
monthly_solutions AS (
SELECT
date_part(‘year’, created_at) AS ano,
date_part(‘month’, created_at) AS mes,
COUNT(*) AS contagem_solucoes
FROM user_actions ua
WHERE ua.action_type = 15
GROUP BY 1, 2
),
monthly_likes AS (
SELECT
date_part(‘year’, created_at) AS ano,
date_part(‘month’, created_at) AS mes,
COUNT(*) AS contagem_likes
FROM user_actions ua
WHERE ua.action_type = 2
GROUP BY 1, 2
),
monthly_topics AS (
SELECT
date_part(‘year’, t.created_at) AS ano,
date_part(‘month’, t.created_at) AS mes,
COUNT(*) AS contagem_topicos
FROM topics t
JOIN users us ON us.id = t.user_id
JOIN selected_category sc ON t.category_id = sc.id
WHERE t.deleted_at IS NULL
AND t.archetype = ‘regular’
AND t.closed = false
AND t.archived = false
AND t.visible = true
AND us.username_lower != ‘system’
GROUP BY 1, 2
)
SELECT
mu.ano,
mu.mes,
SUM(mu.novos_usuarios_mes) OVER (ORDER BY mu.ano, mu.mes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_usuarios,
mp.contagem_posts,
COALESCE(mau.usuarios_ativos_mes, 0) AS usuarios_ativos_mes,
COALESCE(ms.contagem_solucoes, 0) AS contagem_solucoes,
COALESCE(ml.contagem_likes, 0) AS contagem_likes,
COALESCE(mt.contagem_topicos, 0) AS contagem_topicos
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.ano = mu.ano AND mp.mes = mu.mes
LEFT JOIN monthly_active_users mau ON mau.ano = mu.ano AND mau.mes = mu.mes
LEFT JOIN monthly_solutions ms ON ms.ano = mu.ano AND ms.mes = mu.mes
LEFT JOIN monthly_likes ml ON ml.ano = mu.ano AND ml.mes = mu.mes
LEFT JOIN monthly_topics mt ON mt.ano = mu.ano AND mt.mes = mu.mes
ORDER BY mu.ano, mu.mes
