Skip to content

What is the activity per month of each discord guild measured in messages per month?

Query Name

guild_messages_month

Data Visualization

  • Data Frame or Data Grid
  • Heat Map

All Guilds SQL Query


select 
    distinct guilds_t.id, guilds_t.guild_name, month_timestamp, msg_count 
from (
    select
        distinct DATE_TRUNC('month', msg_timestamp)
                     AS  month_timestamp,
        COUNT(guild_id) AS msg_count,
        guild_id 
    FROM messages_t
    GROUP BY guild_id, month_timestamp
) as month_messages_t
join guilds_t on month_messages_t.guild_id = guilds_t.id
order by guilds_t.id, month_timestamp;

SQL Query


select distinct guilds_t.id , guilds_t.guild_name, month_timestamp, msg_count from (
    select
        distinct DATE_TRUNC('month', msg_timestamp)
                     AS  month_timestamp,
        COUNT(guild_id) AS msg_count,
        guild_id 
    FROM messages_t
    where guild_id in (  (select id from guilds_t offset 0 limit 1)  )
    GROUP BY guild_id, month_timestamp
) as month_messages_t
join guilds_t on month_messages_t.guild_id = guilds_t.id
order by id, msg_count desc;

Similar Queries