From ecc249f8829209d0427b6819b87fd3bdc0087c89 Mon Sep 17 00:00:00 2001 From: wookie184 Date: Thu, 27 Oct 2022 16:14:05 +0100 Subject: Add metricity query for messages in past n days Takes multiple users for efficiency as we may want to calculate this for many users at once. --- pydis_site/apps/api/models/bot/metricity.py | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) (limited to 'pydis_site/apps/api/models/bot') diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index abd25ef0..73bc1f0c 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -130,3 +130,31 @@ class Metricity: raise NotFoundError() return values + + def total_messages_in_past_n_days( + self, + user_ids: list[str], + days: int + ) -> list[tuple[int, int]]: + """ + Query activity by a list of users in the past `days` days. + + Returns a list of (user_id, message_count) tuples. + """ + self.cursor.execute( + """ + SELECT + author_id, COUNT(*) + FROM messages + WHERE + author_id IN %s + AND NOT is_deleted + AND channel_id NOT IN %s + AND created_at > now() - interval '%s days' + GROUP BY author_id + """, + [tuple(user_ids), EXCLUDE_CHANNELS, days] + ) + values = self.cursor.fetchall() + + return values -- cgit v1.2.3