I’d like to use the Data Explorer plugin to identify whether users are interacting with each other. I wrote a query to return the sender, recipient and a count for a provided date range, however it seems to be returning responses for topics. Any idea what I might be doing wrong?
-- [params] -- int :interval_days = 14 SELECT topics.user_id, n.user_id, count(*) as message_count FROM topics JOIN notifications n ON n.topic_id = topics.id JOIN users u ON u.id = topics.user_id WHERE archetype = 'private_message' AND subtype = 'user_to_user' AND age(n.created_at) < interval ':interval_days days' AND u.name != 'system' GROUP BY topics.user_id, n.user_id ORDER by message_count DESC