Category name params support

I’m trying to add params into a data explorer query that allows a user to input the Category name and filter the results.

This is the code I have used:

-- [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

This doesn’t seem to work and struggling to understand why - any support would be appreciated (note I do want the name, users won’t necessarily know their Category ID)

I think the category_id parameter type is the most user-friendly one. Eg:

-- [params]
-- category_id :category

SELECT *
FROM categories
WHERE id = :category

That gives you a category selector box for the input:


Though looking at some of the data you’re trying to pull out, I’m not sure each metric is applicable to filter by category. Monthly Users and Monthly Active Users don’t seem like they fit into that filter.

3 Likes