Skip to content

What time of day does the particular discord author post their messages, group by hour?

Query Name

guild_author_messages_by_hour_of_day

SQL Query


select
    guilds_t.guild_name,
    authors_t.author_name,
    authors_t.nickname,
    agg_days_posted_t.hour_of_day,
    num_messages_per_hour,
    guilds_t.id,
    agg_days_posted_t.author_guild_id
from
(
    select
        author_guild_id,
        day_message_t.hour_of_day,
        count(day_message_t.hour_of_day) as num_messages_per_hour
    from
    (
        select
            author_guild_id,
            extract(hour from msg_timestamp) hour_of_day
        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.hour_of_day
) 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_per_hour desc;

"arg_order" : ["guild_id", "author_id"]


  select
    guilds_t.guild_name,
    authors_t.author_name,
    authors_t.nickname,
    agg_days_posted_t.hour_of_day,
    num_messages_per_hour,
    guilds_t.id,
    agg_days_posted_t.author_guild_id
  from
  (
    select
      author_guild_id,
      day_message_t.hour_of_day,
      count(day_message_t.hour_of_day) as num_messages_per_hour
    from
    (
      select
        author_guild_id,
        extract(hour from msg_timestamp) hour_of_day
      from
        messages_t
      where
        guild_id = '{}'
        and author_guild_id = '{}'
    ) as day_message_t
    group by author_guild_id, day_message_t.hour_of_day
  ) 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_per_hour desc;

Similar Queries