Skip to content

What percentage of users on each Discord Guild posted less than 1, 3, 10, or 100 messages?

Data Visualization

  • Bar Chart

Single Guild Query


SELECT 
    author_count_t.guild_id,
    author_count_t.guild_name,
    users_more_10_messages,
    author_count
FROM 
(
    select 
        guild_message_count_t.guild_id as guild_id,
        guilds_t.guild_name,
        count(*) as users_more_10_messages
    from 
    (
        select * FROM
            (
                select 
                    guild_id,
                    author_id,
                    count(content) as msg_count
                from messages_t
                group by guild_id, author_id
            ) as raw_author_message_count
        where msg_count > 20
    ) as guild_message_count_t
    join guilds_t on guild_message_count_t.guild_id = guilds_t.id
    group by guild_message_count_t.guild_id, guilds_t.guild_name
) as author_messages_threshold_t 
JOIN 
(
    select 
        guilds_t.id as guild_id, 
        guilds_t.guild_name, 
        guild_author_count_t.author_count 
    FROM (
        select distinct guild_id, COUNT(distinct(author_id)) as author_count
        from messages_t mt 
        group by guild_id
    ) as guild_author_count_t
    join guilds_t on guild_author_count_t.guild_id = guilds_t.id
    order by guild_author_count_t.author_count asc
) as author_count_t
ON author_messages_threshold_t.guild_id = author_count_t.guild_id;

Full SQL Query



select 
    guild_id,
    guild_name,
    users_more_x_messages,
    author_raw_count,
    CAST(users_more_x_messages AS FLOAT) / CAST(author_raw_count AS FLOAT) * 100 as author_num_percentage
from 
(
    SELECT 
        author_messages_threshold_t.guild_id as guild_id,
        author_messages_threshold_t.guild_name as guild_name,
        author_messages_threshold_t.users_more_x_messages as users_more_x_messages,
        author_count_t.author_raw_count as author_raw_count
    FROM 
    (
        select 
            guild_message_count_t.guild_id as guild_id,
            guilds_t.guild_name,
            count(*) as users_more_x_messages
        from 
        (
            select * FROM
                (
                    select 
                        guild_id,
                        author,
                        count(content) as msg_count
                    from messages_t
                    group by guild_id, author 
                ) as raw_author_message_count
            where msg_count > 20
        ) as guild_message_count_t
        join guilds_t on guild_message_count_t.guild_id = guilds_t.id
        group by guild_message_count_t.guild_id, guilds_t.guild_name
    ) as author_messages_threshold_t 
    JOIN 
    (
        select 
            guilds_t.id as guild_id, 
            guilds_t.guild_name, 
            guild_author_count_t.author_raw_count 
        FROM (
            select distinct guild_id, COUNT(distinct(author)) as author_raw_count
            from messages_t mt 
            group by guild_id
        ) as guild_author_count_t
        join guilds_t on guild_author_count_t.guild_id = guilds_t.id
        order by guild_author_count_t.author_raw_count asc
    ) as author_count_t
    ON author_messages_threshold_t.guild_id = author_count_t.guild_id
) as raw_author_message_count_t
order by author_num_percentage asc;

What number of Authors on discord guild posted more than X messages?

To get total number of users check out What discord guild's have the most Users?

To get percentage we need to join these queries