데이터 탐색기 쿼리에 파라미터를 추가하여 사용자가 카테고리 이름을 입력하고 결과를 필터링할 수 있도록 하려고 합니다.
다음은 제가 사용한 코드입니다:
-- [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 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
이 코드가 작동하지 않는 것 같고, 그 이유를 파악하는 데 어려움을 겪고 있습니다. 도움을 주시면 감사하겠습니다. (참고로, 사용자는 카테고리 ID를 알지 못할 수 있으므로 이름으로 필터링하는 것이 필요합니다)
