Skip to content

How many messages per month with matching test in specific discord guild?

Query Name

guild_activity_per_month_search_text

SQL Query


      select distinct guilds_t.id , guilds_t.guild_name, month_timestamp, msg_count from (
select
  distinct DATE_TRUNC('month', msg_timestamp)
            AS  month_timestamp,
    COUNT(guild_id) AS msg_count,
    guild_id 
FROM messages_t
WHERE
    guild_id = '{}'
    and msg_content ILIKE '%{}%'
GROUP BY guild_id, month_timestamp
) as month_messages_t
join guilds_t on month_messages_t.guild_id = guilds_t.id
order by guilds_t.id, month_timestamp
limit 100;