Add message block query

1 files changed, 24 insertions, 0 deletions

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index eed1deb4..afbcbad8 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -1,5 +1,7 @@
 from django.db import connections
 
+BLOCK_INTERVAL = 10 * 60  # 10 minute blocks
+
 
 class NotFound(Exception):
     """Raised when an entity cannot be found."""
@@ -43,3 +45,25 @@ class Metricity:
             raise NotFound()
 
         return values[0]
+
+    def total_message_blocks(self, user_id: str) -> int:
+        """Query number of 10 minute blocks the user has been active during."""
+        self.cursor.execute(
+            """
+            SELECT
+              COUNT(*)
+             FROM (
+               SELECT
+                 to_timestamp(floor((extract('epoch' from created_at) / 600 )) * 600)
+                 AT TIME ZONE 'UTC' AS interval
+               FROM messages
+               WHERE author_id='%s' AND NOT is_deleted GROUP BY interval) block_query;
+            """,
+            [user_id]
+        )
+        values = self.cursor.fetchone()
+
+        if not values:
+            raise NotFound()
+
+        return values[0]