Skip to content

How much activity for a specific discord guild per day of week?

Query Name

guild_activity_per_day_of_week

SQL Query


select distinct guilds_t.id , guilds_t.guild_name, month_timestamp, msg_count from (
    select
        distinct DATE_TRUNC('day', msg_timestamp)
                     AS  month_timestamp,
        COUNT(guild_id) AS msg_count,
        guild_id 
    FROM messages_t
    WHERE guild_id = '{}'
    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;



select distinct guilds_t.id , guilds_t.guild_name, month_timestamp, msg_count from (
    select
        distinct DATE_TRUNC('day', real_timestamp)
                     AS  month_timestamp,
        COUNT(guild_id) AS msg_count,
        guild_id 
    FROM messages_t
    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;

PostGraphile Query


query MyQuery {
  allMessagesTs(first: 300, condition: {guildId: "621022643344113684"}) {
    groupedAggregates(groupBy: REAL_TIMESTAMP_TRUNCATED_TO_DAY) {
      distinctCount {
        id
      }
      min {
        unixTimestamp
      }
    }
  }
}

Below is thinking reguarding requiring timestamp type in PostgreSQL

List all channels in guild with Message count

query MyQuery {
  allChannelsTs(condition: {guildId: "621022643344113684"}) {
    edges {
      node {
        id
        channelName
        channelType
        guildsTByGuildId {
          id
        }
        messagesTsByChannelId {
          totalCount
        }
      }
    }
  }
}

Select all messages from Channel


query MyQuery {
  allMessagesTs(
    condition: {channelId: "732429842720489512"}
    orderBy: TIMESTAMPEDITED_ASC
  ) {
    edges
  }
}

Seems like we need to do one query per day, unless we can increment the timestamp. The problem is that we use INT rather that timestamp datatype to store the timestamp of messages.


query MyQuery {
  allMessagesTs(condition: {channelId: "732429842720489512"}) {
    groupedAggregates(groupBy: TIMESTAMP_TRUNCATED_TO_DAY) {
      distinctCount {
        id
      }
    }
  }
}

groupedAggregates is not going to be able to do it. We need to do some processing inside the database or have the frontend do it.