Only return results for the current user

data & reporting
1

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
2

I’m pretty sure that’s a feature request.

3 Likes