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