Identify private message interactions between users

Data & reporting
1

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
1 Like
2

I added a notification_type of 6 (private_message) to get this to work:

AND n.notification_type = 6

1 Like