So I am trying to get a count of topics which do have a reply in the same month they were created. Here’s what I am doing:
-- string :mth = 1 -- string :yr = 2018 WITH t as ( SELECT ID FROM topics WHERE archetype <> 'private_message' AND user_id not in ('-1', '-2') AND deleted_at IS NULL AND date_part('year', created_at) = :yr AND date_part('month', created_at) = :mth ), p as ( SELECT DISTINCT(topic_id), created_at FROM posts, t WHERE topic_id IN (SELECT * FROM t) ) SELECT count(*) FROM p WHERE date_part('year', created_at) = :yr AND date_part('month', created_at) = :mth
When I do this I get a count of roughly 2-3 times the number of topics created in that month… and I can’t figure out why. Which means I am missing something.