Skip to content

What is the rate of new users joining the specific discord guild per month?

Notes

We can assume whenever the first message is sent it counts as a new user.

The rate of new users has to be measured by a gap of time, let's use Month

Query Name

guild_new_author_per_month

SQL



select
    guilds_t.guild_name,
    count(guilds_t.guild_name) as author_count,
    TO_TIMESTAMP(min_month_timestamp, 'YYYY-MM') as month_timestamp,
    earliest_author_message.guild_id
from
(
    select
        distinct 
            guild_id,
            author_guild_id,
            TO_CHAR(    min(msg_timestamp), 'YYYY-MM') as min_month_timestamp
    from
        messages_t
    -- where guild_id = '{}'
    group by guild_id, author_guild_id
) as earliest_author_message
join guilds_t on earliest_author_message.guild_id = guilds_t.id
group by earliest_author_message.guild_id, guilds_t.id, min_month_timestamp
order by month_timestamp asc;