识别用户之间的私人消息交互

社区 数据与报告
1

我想使用 Data Explorer 插件 来识别用户之间是否存在互动。我编写了一个查询，旨在返回指定日期范围内的发送者、接收者及计数，但结果似乎返回的是主题相关的数据。您知道可能哪里出错了吗？

-- [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 个赞
2

我添加了 notification_type 为 6（private_message）以使此功能生效：

AND n.notification_type = 6

1 个赞