Add get top tea drinkers from db

diff --git a/cbdb.py b/cbdb.py
index b78aaf5926f568a140cf245e88ce08eb72bcffe9..22e00581ae4326a79e7724c32764a61239fe5fc8 100644 (file)
--- a/cbdb.py
+++ b/cbdb.py
@@ -56,6 +56,25 @@ class Db:
         top.sort(key=lambda x: (x[1], x[0]), reverse=True)
         return top
 
+    def get_top_tea_drinkers(self):
+        """Return list of pairs of name, count for tea drinkers."""
+        q = """
+
+        SELECT count(*), users.name FROM coffees
+        INNER JOIN users ON coffees.id = users.id
+        WHERE flavor = 'tea'
+        AND coffees.time BETWEEN
+            datetime('now', 'localtime', '-7 days') AND
+            datetime('now', 'localtime')
+        GROUP BY coffees.id
+
+        """
+        top = []
+        for (cnt, un) in self.cur.execute(q):
+            top.append((un, cnt))
+        top.sort(key=lambda x: (x[1], x[0]), reverse=True)
+        return top
+
     def getDrunkSum(self, *args, **kwargs):
         """Return list of drunken ``flavor`` from ``dtf`` to ``dtt``.