Skip to content

What is the name of the most active channel in each discord guild?

How do we do this while avoiding a loop?

SQL Query


select 
    channels_t.channel_name,
    guilds_t.guild_name,
    channel_message_count_t.message_count,
    channel_message_count_t.guild_id,
    channel_message_count_t.channel_id
from (
    select guild_id, channel_id, COUNT(id) as message_count from messages_t
    group by guild_id, channel_id
) as channel_message_count_t
join (
    select distinct guild_id, max(message_count) max_messages from
    (
        select guild_id, channel_id, COUNT(id) as message_count from messages_t
        group by guild_id, channel_id
    ) as count_channel_messages_t
    group by guild_id
) as channel_message_max_t on channel_message_max_t.max_messages = channel_message_count_t.message_count
join guilds_t on channel_message_count_t.guild_id = guilds_t.id
join channels_t on channel_message_count_t.channel_id = channels_t.id
order by channel_message_count_t.message_count desc;