Skip to content

What discord channels have the most messages?

Query Name

guild_channel_most_messages

SQL Query


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 
) as guild_lookup_t
on guild_lookup_t.id = channels_grouped_t.guild_id
order by msg_count desc;




select 
    guilds_t.guild_name,
    channel_name,
    msg_count, 
    channels_t.guild_id,
    channels_grouped_t.channel_id
from
( 
    select 
        count(*) as msg_count,
        channel_id,
        guild_id
    from
        messages_t
    -- where guild_id = '{}'
    group by channel_id, guild_id
) as channels_grouped_t
join channels_t on channels_grouped_t.channel_id = channels_t.id
join guilds_t on channels_grouped_t.guild_id = guilds_t.id
order by msg_count desc;

GraphQL


query MyQuery {
  allChannelsTs {
    edges {
      node {
        messagesTsByChannelId {
          aggregates {
            distinctCount {
              id
            }
          }
        }
      }
    }
  }
}