From a32d2548d986f7075e8310184ce0b70c69513a02 Mon Sep 17 00:00:00 2001
From: Neil Johnson <neil@fragile.org.uk>
Date: Wed, 28 Mar 2018 10:39:13 +0100
Subject: query and call for r30 stats

---
 synapse/app/homeserver.py   |  2 ++
 synapse/storage/__init__.py | 36 ++++++++++++++++++++++++++++++++++++
 2 files changed, 38 insertions(+)

diff --git a/synapse/app/homeserver.py b/synapse/app/homeserver.py
index c00afbba28..8bce9f1ace 100755
--- a/synapse/app/homeserver.py
+++ b/synapse/app/homeserver.py
@@ -425,6 +425,8 @@ def run(hs):
         stats["daily_active_rooms"] = yield hs.get_datastore().count_daily_active_rooms()
         stats["daily_messages"] = yield hs.get_datastore().count_daily_messages()
 
+        stats["r30_users"] = yield hs.get_datastore().count_r30_users()
+
         daily_sent_messages = yield hs.get_datastore().count_daily_sent_messages()
         stats["daily_sent_messages"] = daily_sent_messages
 
diff --git a/synapse/storage/__init__.py b/synapse/storage/__init__.py
index b97e5e5ff4..10f99c3cd5 100644
--- a/synapse/storage/__init__.py
+++ b/synapse/storage/__init__.py
@@ -267,6 +267,42 @@ class DataStore(RoomMemberStore, RoomStore,
         ret = yield self.runInteraction("count_users", _count_users)
         defer.returnValue(ret)
 
+    @defer.inlineCallbacks
+    def count_r30_users(self):
+        """
+        Counts the number of 30 day retained users, defined as:-
+         * Users who have created their accounts more than 30 days
+         * Where last seen at most 30 days ago
+         * Where account creation and last_seen are > 30 days
+        """
+        def _count_r30_users(txn):
+            thirty_days_in_secs = 86400 * 30
+            now = int(self._clock.time_msec())
+            thirty_days_ago_in_secs = now - thirty_days_in_secs
+
+            sql = """
+                SELECT COALESCE(count(*), 0) FROM (
+                    SELECT users.name, users.creation_ts * 1000, MAX(user_ips.last_seen)
+                    FROM users, user_ips
+                    WHERE users.name = user_ips.user_id
+                    AND appservice_id is NULL
+                    AND users.creation_ts < ?
+                    AND user_ips.last_seen/1000 > ?
+                    AND (user_ips.last_seen/1000) - users.creation_ts > ?
+                    GROUP BY users.name, users.creation_ts
+                ) u
+            """
+
+            txn.execute(sql, (thirty_days_ago_in_secs,
+                              thirty_days_ago_in_secs,
+                              thirty_days_in_secs))
+
+            count, = txn.fetchone()
+            return count
+
+        ret = yield self.runInteraction("count_r30_users", _count_r30_users)
+        defer.returnValue(ret)
+
     def get_users(self):
         """Function to reterive a list of users in users table.
 
-- 
cgit 1.4.1