Skip to content

Who is the most consistent poster within a Discord Guild?

Query Name

guild_author_most_days_with_messages

SQL Query


select
    authors_t.author_name,
    authors_t.nickname,
    count_msg_day_t.day_count,
    count_msg_day_t.author_guild_id,
    guilds_t.guild_name,
    guilds_t.id as guild_id
from
(
    select
        count(distinct_msg_day_t.num_days_posted) as day_count,
        distinct_msg_day_t.author_guild_id
    from 
    (
        SELECT 
            distinct ( TO_CHAR(msg_timestamp, 'YYYY-MM-DD') )
                as num_days_posted,
            author_guild_id
        from
            messages_t
        where guild_id in ( select id from guilds_t limit 1 )
    ) as distinct_msg_day_t
    group by distinct_msg_day_t.author_guild_id
    order by distinct_msg_day_t.author_guild_id desc
) as count_msg_day_t
join authors_t on count_msg_day_t.author_guild_id = authors_t.id
join guilds_t on authors_t.guild_id = guilds_t.id 
order by day_count desc;