Add route to get a member's data for helper review

Added route for getting a user's join date, total messages, and top 3 channels by activity.

This information will be used to auto-review nominees.

1 files changed, 39 insertions, 0 deletions

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index cae630f1..af5e1f3b 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -89,3 +89,42 @@ class Metricity:
             raise NotFound()
 
         return values[0]
+
+    def top_channel_activity(self, user_id: str) -> int:
+        """
+        Query the top three channels in which the user is most active.
+
+        Help channels are grouped under "the help channels",
+        and off-topic channels are grouped under "off-topic".
+        """
+        self.cursor.execute(
+            """
+            SELECT
+                CASE
+                    WHEN channels.name ILIKE 'help-%%' THEN 'the help channels'
+                    WHEN channels.name ILIKE 'ot%%' THEN 'off-topic'
+                    ELSE channels.name
+                END,
+                COUNT(1)
+            FROM
+                messages
+                LEFT JOIN channels ON channels.id = messages.channel_id
+            WHERE
+                author_id = '%s'
+            GROUP BY
+                1
+            ORDER BY
+                2 DESC
+            LIMIT
+                3;
+            """,
+            [user_id]
+        )
+
+        values = self.cursor.fetchall()
+        print(values)
+
+        if not values:
+            raise NotFound()
+
+        return values