/// <inheritdoc /> public async Task <SingleEmojiUsageStatistics> GetEmojiStatsAsync(ulong guildId, EphemeralEmoji emoji, TimeSpan?dateFilter = null) { var query = GetQuery(); var parameters = GetParameters(); var stats = await ModixContext.Query <SingleEmojiStatsDto>() .AsNoTracking() .FromSql(query, parameters) .FirstOrDefaultAsync(); return(SingleEmojiUsageStatistics.FromDto(stats ?? new SingleEmojiStatsDto())); NpgsqlParameter[] GetParameters() => new[]
/// <inheritdoc /> public async Task <GuildUserParticipationStatistics> GetGuildUserParticipationStatistics(ulong guildId, ulong userId) { var stats = await ModixContext.Query <GuildUserParticipationStatistics>() .AsNoTracking() .FromSql( @"with msgs as ( select msg.""AuthorId"", msg.""Id"" as ""MessageId"", msg.""GuildId"" from ""Messages"" as msg left outer join ""DesignatedChannelMappings"" as dcm on msg.""ChannelId"" = dcm.""ChannelId"" where msg.""GuildId"" = cast(:GuildId as numeric(20)) and dcm.""Type"" = 'CountsTowardsParticipation' and ""Timestamp"" >= (current_date - interval '30 day') ), user_count as ( select ""AuthorId"", count(1) as ""MessageCount"", ""GuildId"" from msgs group by ""AuthorId"", ""GuildId"" ), user_avg as ( select ""AuthorId"", ""MessageCount"", (cast(""MessageCount"" as decimal) / cast(30 as decimal)) as ""AveragePerDay"", ""GuildId"" from user_count group by ""AuthorId"", ""GuildId"", ""MessageCount"" ), ntiles as ( select ""AuthorId"", ntile(100) over (order by ""AveragePerDay"") as ""Percentile"", ""GuildId"" from user_avg group by ""AuthorId"", ""GuildId"", ""AveragePerDay"" ), ranked_users as ( select user_avg.""AuthorId"" as ""UserId"", ""AveragePerDay"", ""Percentile"", dense_rank() over (order by ""AveragePerDay"" desc) as ""Rank"", user_avg.""GuildId"" from user_avg inner join ntiles on user_avg.""AuthorId"" = ntiles.""AuthorId"" and user_avg.""GuildId"" = ntiles.""GuildId"" ) select ""AveragePerDay"", ""Percentile"", ""Rank"", ""GuildId"", ""UserId"" from ranked_users where ""UserId"" = cast(:UserId as numeric(20))", new NpgsqlParameter(":GuildId", guildId.ToString()), new NpgsqlParameter(":UserId", userId.ToString())) .OrderByDescending(x => x.AveragePerDay) .FirstOrDefaultAsync() ?? new GuildUserParticipationStatistics(); stats.GuildId = guildId; stats.UserId = userId; return(stats); }
public async Task <IReadOnlyCollection <PerUserMessageCount> > GetPerUserMessageCounts(ulong guildId, ulong userId, TimeSpan timespan, int userCount = 10) { var earliestDateTime = DateTimeOffset.UtcNow - timespan; var query = GetQuery(); var counts = await ModixContext.Query <PerUserMessageCount>() .AsNoTracking() .FromSql(query, new NpgsqlParameter(":GuildId", NpgsqlDbType.Bigint) { Value = unchecked ((long)guildId) }, new NpgsqlParameter(":UserId", NpgsqlDbType.Bigint) { Value = unchecked ((long)userId) }, new NpgsqlParameter(":StartTimestamp", NpgsqlDbType.TimestampTz) { Value = earliestDateTime }) .ToArrayAsync(); return(counts); string GetQuery() => $@" with guildCounts as ( select ""AuthorId"" as ""UserId"", row_number() over (order by count(*) desc) as ""Rank"", count(*) as ""MessageCount"", ""AuthorId"" = :UserId as ""IsCurrentUser"" from ""Messages"" where ""GuildId"" = :GuildId and ""Timestamp"" >= :StartTimestamp group by ""AuthorId"", ""GuildId"" ), currentUserCount as ( select ""UserId"", ""Rank"", ""MessageCount"", ""IsCurrentUser"" from guildCounts where ""UserId"" = :UserId ), guildCountsLimited as ( select ""UserId"", ""Rank"", ""MessageCount"", ""IsCurrentUser"" from guildCounts limit {userCount} ), unioned as ( select ""UserId"", ""Rank"", ""MessageCount"", ""IsCurrentUser"" from guildCountsLimited union select ""UserId"", ""Rank"", ""MessageCount"", ""IsCurrentUser"" from currentUserCount ), joined as ( select ""Username"", ""Discriminator"", ""Rank"", ""MessageCount"", ""IsCurrentUser"" from unioned inner join ""Users"" on ""Id"" = ""UserId"" ) select ""Username"", ""Discriminator"", ""Rank"", ""MessageCount"", ""IsCurrentUser"" from joined order by ""Rank"" asc"; }