Skip to content

How many authors posted in each specific channel of a specific discord guild?

Query Name

guild_channel_author_count

SQL Query


select
    guild_name,
    channel_name,
    count(distinct(author_guild_id)) as author_count,
    channel_id
from
(
    select
        distinct(channel_id) as channel_id,
        author_guild_id
    from
        messages_t
    where
        messages_t.is_bot = 'F'
        -- and guild_id = '{}'
    group by channel_id, author_guild_id
) as author_in_channel_count_t
join channels_t on author_in_channel_count_t.channel_id = channels_t.id
join guilds_t on channels_t.guild_id = guilds_t.id
group by guild_name, channel_name, channel_id
order by author_count desc

GraphQL Query

query MyQuery {
  allChannelsTs(condition: {guildId: "621022643344113684"}) {
    edges {
      node {
        id
        channelName
        channelType
        guildsTByGuildId {
          id
        }
        messagesTsByChannelId {
          aggregates {
            distinctCount {
              author
            }
          }
        }
      }
    }
  }
}