Merge pull request #418 from python-discord/metricity/add-activity-blocks

Add activity blocks to Metricity data endpoint

1 files changed, 31 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..e7fc92fc 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,32 @@ class Metricity:
             raise NotFound()
 
         return values[0]
+
+    def total_message_blocks(self, user_id: str) -> int:
+        """
+        Query number of 10 minute blocks during which the user has been active.
+
+        This metric prevents users from spamming to achieve the message total threshold.
+        """
+        self.cursor.execute(
+            """
+            SELECT
+                COUNT(*)
+            FROM (
+                SELECT
+                    (floor((extract('epoch' from created_at) / %d )) * %d) AS interval
+                FROM messages
+                WHERE
+                    author_id='%s'
+                    AND NOT is_deleted
+                GROUP BY interval
+            ) block_query;
+            """,
+            [BLOCK_INTERVAL, BLOCK_INTERVAL, user_id]
+        )
+        values = self.cursor.fetchone()
+
+        if not values:
+            raise NotFound()
+
+        return values[0]