# Most DM channels created (interval)

**URL:** https://meta.discourse.org/t/most-dm-channels-created-interval/277363
**Category:** Data & reporting
**Tags:** chat, sql-query
**Created:** [August 31, 2023, 2:38pm UTC](https://meta.discourse.org/t/most-dm-channels-created-interval/277363 "2023-08-31T14:38:58Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [August 31, 2023, 2:38pm UTC](https://meta.discourse.org/t/most-dm-channels-created-interval/277363/1 "2023-08-31T14:38:58Z")

</div>

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

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

```