Skip to content

It is not fail to calculate the average message count per day of a Guild, we should do average of the most active 30 days

How to get message count by day for a particular discord guild?

All Guilds SQL Query

TODO Practice SQL challenge

Single Guild SQL Query


select 
    guilds_t.id,
    guilds_t.guild_name,
    avg_msg_count,
    difference_between_most_active_days
from (
    select 
        msg_count_per_day_t.guild_id,
        avg(msg_count_per_day_t.msg_count) as avg_msg_count,
        max(day_timestamp) - min(day_timestamp) as difference_between_most_active_days
    FROM
    (
        select distinct 
            month_messages_t.guild_id as guild_id, 
            day_timestamp, 
            msg_count
        from (
            select
                distinct DATE_TRUNC('day', msg_timestamp)
                             AS  day_timestamp,
                COUNT(guild_id) AS msg_count,
                guild_id 
            FROM messages_t
            WHERE messages_t.guild_id = (SELECT id from guilds_t LIMIT 1 offset 0)
            GROUP BY guild_id, day_timestamp
        ) as month_messages_t
        order by msg_count desc
        limit 30 -- set_day_limit
    ) as msg_count_per_day_t
    group by guild_id
) as msg_count_per_day_t
join guilds_t on msg_count_per_day_t.guild_id = guilds_t.id ;