最多创建的DM channels (间隔)

数据与报告
,
1

列出在给定时间范围内某人发起的个人聊天数量。

-- [params]
-- string :interval = 1 WEEK

WITH first_message AS ( 

SELECT 
    MIN(cm.id) AS id
FROM chat_messages cm
WHERE cm.chat_channel_id = (
    SELECT DISTINCT id
    FROM chat_channels 
    WHERE id = cm.chat_channel_id
      AND created_at::date > CURRENT_DATE - INTERVAL :interval
      AND chatable_type = 'DirectMessage'
    )
GROUP BY cm.chat_channel_id

)

SELECT 
    cm.user_id, 
    COUNT(*)
FROM chat_messages cm
WHERE cm.id IN (SELECT id FROM first_message)
GROUP BY cm.user_id
ORDER BY COUNT (*) DESC