Skip to content

How to visualize the message count of each author in a specific channel?

Query Name

guild_channel_author_message_count

SQL Query


select
    authors_t.author_name,
    authors_t.nickname,
    msg_count_per_author_t.msg_count,
    msg_count_per_author_t.author_guild_id,
    guilds_t.guild_name,
    guilds_t.id as guild_id
from
(
    select
        count(*) as msg_count,
        author_guild_id
    from
        messages_t
    where 
        guild_id in (  (select id from guilds_t limit 1)  )
        and channel_id in  (  (select id from channels_t where guild_id = (select id from guilds_t limit 1) limit 1)  )
    group by author_guild_id
    order by msg_count desc
) as msg_count_per_author_t
join authors_t on msg_count_per_author_t.author_guild_id = authors_t.id
join guilds_t  on authors_t.guild_id = guilds_t.id
order by msg_count_per_author_t.msg_count desc;




select
    authors_t.author_name,
    authors_t.nickname,
    msg_count_per_author_t.msg_count,
    msg_count_per_author_t.author_guild_id,
    guilds_t.guild_name,
    guilds_t.id as guild_id
from
(
    select
        count(*) as msg_count,
        author_guild_id
    from
        messages_t
    where 
        guild_id = '{}'
        and channel_id = '{}'
    group by author_guild_id
    order by msg_count desc
) as msg_count_per_author_t
join authors_t on msg_count_per_author_t.author_guild_id = authors_t.id
join guilds_t  on authors_t.guild_id = guilds_t.id
order by msg_count_per_author_t.msg_count desc;

Similar Queries