What are the most active channels in a specific discord guild?
Query Name
guild_channels_most_active
SQL Query
select
guilds_t.id as guild_id,
guilds_t.guild_name ,
channels_t.id,
channels_t.channel_name,
message_count
from
(
select
guild_id,
channel_id,
COUNT(id) as message_count
from messages_t
where
guild_id = (select distinct id from guilds_t limit 1)
group by guild_id, channel_id
) as messages_channel_agg_t
join channels_t on messages_channel_agg_t.channel_id = channels_t.id
join guilds_t on channels_t.guild_id = guilds_t.id
order by message_count desc;
select * from (
select
count(*) as msg_count,
channel_id,
guild_id
from
messages_t
group by channel_id, guild_id
) as channels_grouped_t
join
(
select
id,
channel_name
from
channels_t
) as channel_lookup_t
on channel_lookup_t.id = channels_grouped_t.channel_id
join
(
select
id,
guild_name
from
guilds_t gt
where id in ( (select id from guilds_t limit 1) )
) as guild_lookup_t
on guild_lookup_t.id = channels_grouped_t.guild_id
order by msg_count desc;