Skip to content

Query Name

guild_messages_percent_total_days

All Guilds SQL Query

TODO Practice SQL challenge

Single Guild SQL Query


select 
    id,
    guild_name,
    min(day_timestamp)::DATE as earliest_date,
    max(day_timestamp)::DATE as latest_date,
    count(*) as days_with_messages,
    max(day_timestamp)::DATE - min(day_timestamp)::DATE as total_num_of_days,
    cast( count(*) as FLOAT) / cast( ( max(day_timestamp)::DATE - min(day_timestamp)::DATE ) as Float)* 100 as percentage_of_days
from
(
    select 
        distinct 
            guilds_t.id,
            guilds_t.guild_name,
            day_timestamp,
            msg_count 
    from (
        select distinct
            DATE_TRUNC('day', msg_timestamp) AS  day_timestamp,
            COUNT(guild_id) AS msg_count,
            guild_id 
        FROM messages_t
        WHERE messages_t.guild_id = (SELECT id from guilds_t LIMIT 1 offset 0)
        GROUP BY guild_id, day_timestamp
    ) as month_messages_t
    join guilds_t on month_messages_t.guild_id = guilds_t.id
    order by day_timestamp desc
) as daily_msg_stats_t
group by id, guild_name;

Similar Queries