How to filter topics by user group

data & reporting
7

This sounds like you need something a little different. :slight_smile: Every forum is slightly different, so solutions may need to be adapted, but I would describe this brief as “topics created by users in the ‘client’ group, with no reply from a user in the ‘team’ group”. If that’s the case then you could adapt this query with the correct group_ids and it should do what you want:

-- [params]
-- date :start_date = 01/01/2023
-- date :end_date = 01/06/2023

WITH team_replies AS (

SELECT topic_id
FROM posts
WHERE user_id IN (SELECT user_id FROM group_users WHERE group_id = 3) --group_id of your 'team' group
  AND post_number <> 1
  AND deleted_at ISNULL
  AND post_type = 1
GROUP BY topic_id
)

 
SELECT t.created_at::date,
       t.id AS topic_id,
       MIN(p.id) AS first_post_id
FROM topics t
JOIN posts p ON p.topic_id = t.id
WHERE t.user_id IN (SELECT user_id FROM group_users WHERE group_id = 45) -- group_id of your 'client' group
  AND t.id NOT IN (SELECT topic_id FROM team_replies)
  AND t.created_at BETWEEN :start_date AND :end_date
  AND t.deleted_at ISNULL
  AND t.archetype = 'regular'
GROUP BY t.id
ORDER BY 1 ASC

This does work smoother if you know the group_ids rather than using group names. You can find them in the group JSON (eg. /g/admins.json), though you can also use something like this as a cheeky lookup:


-- [params]
-- string :group_name

SELECT name,
       id
FROM groups
WHERE name LIKE LOWER (:group_name)

And you’ll also need to refresh the page the very first time you copy paste the queries into your data explorer to pick up the parameter input boxes. :+1: