I’m attempting to create a query that will be public to our users, but I want it to only return the results for the current user, not everyone. Below is the private query for the moderators that returns all the users stats. I can’t seem to find a way to target only the current user. Thanks!
-- [params]
-- string :interval = 1 month
SELECT
u.username,
COALESCE(t.created_topics, 0) AS created_topics,
COALESCE(p.replies_to_others, 0) AS replies_to_others,
COALESCE(ROUND(p.replies_to_others::numeric / NULLIF(t.created_topics, 0), 2), 0) AS ratio
FROM
users u
LEFT JOIN (
SELECT
user_id,
COUNT(*) AS created_topics
FROM
topics
WHERE
created_at >= CURRENT_DATE - INTERVAL :interval
AND category_id IN (
SELECT id FROM categories WHERE parent_category_id = 87 OR id = 87
)
GROUP BY
user_id
) t ON t.user_id = u.id
LEFT JOIN (
SELECT
p.user_id,
COUNT(DISTINCT p.topic_id) AS replies_to_others
FROM
posts p
JOIN topics t ON p.topic_id = t.id
WHERE
p.post_number > 1
AND p.user_id != t.user_id
AND p.created_at >= CURRENT_DATE - INTERVAL :interval
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = 87 OR id = 87
)
AND LENGTH(p.raw) >= 100
AND p.post_number = (
SELECT MIN(p2.post_number)
FROM posts p2
WHERE p2.topic_id = p.topic_id
AND p2.user_id = p.user_id
AND p2.post_number > 1
)
GROUP BY
p.user_id
) p ON p.user_id = u.id
WHERE
t.created_topics > 0 OR p.replies_to_others > 0
ORDER BY
ratio DESC