– [Parameter]
– Zeichenfolge :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 year,
date_part(‘month’, created_at) AS month,
COUNT(*) AS new_users_month
FROM users
GROUP BY 1, 2
),
monthly_posts AS (
SELECT
date_part(‘year’, created_at) AS year,
date_part(‘month’, created_at) AS month,
COUNT(*) AS posts_count
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 year,
date_part(‘month’, visited_at) AS month,
COUNT(DISTINCT user_id) AS active_users_count
FROM user_visits uv
GROUP BY 1, 2
),
monthly_solutions AS (
SELECT
date_part(‘year’, created_at) AS year,
date_part(‘month’, created_at) AS month,
COUNT(*) AS solutions_count
FROM user_actions ua
WHERE ua.action_type = 15
GROUP BY 1, 2
),
monthly_likes AS (
SELECT
date_part(‘year’, created_at) AS year,
date_part(‘month’, created_at) AS month,
COUNT(*) AS likes_count
FROM user_actions ua
WHERE ua.action_type = 2
GROUP BY 1, 2
),
monthly_topics AS (
SELECT
date_part(‘year’, t.created_at) AS year,
date_part(‘month’, t.created_at) AS month,
COUNT(*) AS topics_count
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.year,
mu.month,
SUM(mu.new_users_month) OVER (ORDER BY mu.year, mu.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_users,
mp.posts_count,
COALESCE(mau.active_users_count, 0) AS active_users_count,
COALESCE(ms.solutions_count, 0) AS solutions_count,
COALESCE(ml.likes_count, 0) AS likes_count,
COALESCE(mt.topics_count, 0) AS topics_count
FROM monthly_users mu
LEFT JOIN monthly_posts mp ON mp.year = mu.year AND mp.month = mu.month
LEFT JOIN monthly_active_users mau ON mau.year = mu.year AND mau.month = mu.month
LEFT JOIN monthly_solutions ms ON ms.year = mu.year AND ms.month = mu.month
LEFT JOIN monthly_likes ml ON ml.year = mu.year AND ml.month = mu.month
LEFT JOIN monthly_topics mt ON mt.year = mu.year AND mt.month = mu.month
ORDER BY mu.year, mu.month
