チャットのモデレーションツール？

[JammyDodger]

Data & reporting カテゴリに、さらに適応させることができるものがいくつかあります。

Most DM channels created (interval)

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 ch…

Most Direct Messages (interval)

A top ten list of users who have sent the most messages in the given timeframe. -- [params] -- string :interval = 1 WEEK SELECT user_id, COUNT(*) FROM chat_messages WHERE created_at::date > CURRENT_DATE - INTERVAL :interval GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 10

Direct Message Look-Up (interval)

A deep dive into all messages sent and received by a user within the given timeframe. -- [params] -- user_id :user -- string :interval = 1 month SELECT chat_channel_id, user_id, message, created_at AS reltime$time, created_at FROM chat_messages WHERE chat_channel_id IN ( SELECT cc.id FROM chat_channels cc JOIN chat_messages cm ON cm.chat_channel_id = cc.id WHERE cc.chatable_type = 'DirectMessage' AND cm.user_id = :user ) AND created_a…

ただし、将来的にはダッシュボードに Chat のレポートをさらに追加したいと考えています。