Most DM channels created (interval)

data & reporting
,
1

A list of how many Personal Chats someone has started within the given timeframe.

-- [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