Skip to content

What is the number of messages from a specific author from a particular discord guild aggregated by week?

Query Name

guild_author_messages_per_week

SQL Query


select
    guilds_t.guild_name,
    count(guilds_t.guild_name),
    TO_TIMESTAMP(min_month_timestamp, 'YYYY-WW') as month_timestamp,
    earliest_author_message.guild_id
from
(
    select
        distinct 
            guild_id,
            author_guild_id,
            TO_CHAR(    min(msg_timestamp), 'YYYY-WW') as min_month_timestamp
    from
        messages_t
    -- where guild_id = '{}' and author_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;