Skip to content

What day of the week does the particular discord author post their messages?

Query Name

guild_author_messages_day_of_week

SQL Query


select
    guilds_t.guild_name,
    authors_t.author_name,
    authors_t.nickname,
    agg_days_posted_t.day_of_week,
    num_messages_on_day,
    guilds_t.id,
    agg_days_posted_t.author_guild_id
from
(
    select
        author_guild_id,
        day_message_t.day_of_week,
        count(day_message_t.day_of_week) as num_messages_on_day
    from
    (
        select
            author_guild_id,
            TO_CHAR(msg_timestamp, 'Day') as day_of_week
        from
            messages_t
        where
            author_guild_id = (  select id from authors_t limit 1 offset 0 )
    ) as day_message_t
    group by author_guild_id, day_message_t.day_of_week
) as agg_days_posted_t
join authors_t on authors_t.id = agg_days_posted_t.author_guild_id
join guilds_t  on guilds_t.id  = authors_t.guild_id
order by num_messages_on_day desc;



      select
        guilds_t.guild_name,
        authors_t.author_name,
        authors_t.nickname,
        agg_days_posted_t.day_of_week,
        agg_days_posted_t.day_of_week_number,
        num_messages_on_day,
        guilds_t.id,
        agg_days_posted_t.author_guild_id
      from
      (
        select
          author_guild_id,
          day_message_t.day_of_week,
          day_message_t.day_of_week_number,
          count(day_message_t.day_of_week) as num_messages_on_day
        from
        (
          select
            author_guild_id,
            TO_CHAR(msg_timestamp, 'Day') as day_of_week,
            EXTRACT(DOW FROM msg_timestamp) AS day_of_week_number
          from
            messages_t
          where
              guild_id = '{}'
              and author_guild_id = '{}'
        ) as day_message_t
        group by author_guild_id, day_message_t.day_of_week, day_message_t.day_of_week_number
      ) as agg_days_posted_t
      join authors_t on authors_t.id = agg_days_posted_t.author_guild_id
      join guilds_t  on guilds_t.id  = authors_t.guild_id
      order by agg_days_posted_t.day_of_week_number asc;

Similar Queries