Merge branch 'glitchsoc'

4 files changed, 53 insertions, 4 deletions

diff --git a/db/views/account_summaries_v02.sql b/db/views/account_summaries_v02.sql
new file mode 100644
index 000000000..17f5605f8
--- /dev/null
+++ b/db/views/account_summaries_v02.sql
@@ -0,0 +1,23 @@
+SELECT
+  accounts.id AS account_id,
+  mode() WITHIN GROUP (ORDER BY language ASC) AS language,
+  mode() WITHIN GROUP (ORDER BY sensitive ASC) AS sensitive
+FROM accounts
+CROSS JOIN LATERAL (
+  SELECT
+    statuses.account_id,
+    statuses.language,
+    statuses.sensitive
+  FROM statuses
+  WHERE statuses.account_id = accounts.id
+    AND statuses.deleted_at IS NULL
+    AND statuses.reblog_of_id IS NULL
+  ORDER BY statuses.id DESC
+  LIMIT 20
+) t0
+WHERE accounts.suspended_at IS NULL
+  AND accounts.silenced_at IS NULL
+  AND accounts.moved_to_account_id IS NULL
+  AND accounts.discoverable = 't'
+  AND accounts.locked = 'f'
+GROUP BY accounts.id
diff --git a/db/views/follow_recommendations_v01.sql b/db/views/follow_recommendations_v01.sql
index 799abeaee..8295bbc0f 100644
--- a/db/views/follow_recommendations_v01.sql
+++ b/db/views/follow_recommendations_v01.sql
@@ -20,7 +20,7 @@ FROM (
   HAVING count(follows.id) >= 5
   UNION ALL
   SELECT accounts.id AS account_id,
-         sum(reblogs_count + favourites_count) / (1.0 + sum(reblogs_count + favourites_count)) AS rank,
+         sum(status_stats.reblogs_count + status_stats.favourites_count) / (1.0 + sum(status_stats.reblogs_count + status_stats.favourites_count)) AS rank,
          'most_interactions' AS reason
   FROM status_stats
   INNER JOIN statuses ON statuses.id = status_stats.status_id
@@ -32,7 +32,7 @@ FROM (
     AND accounts.locked = 'f'
     AND accounts.discoverable = 't'
   GROUP BY accounts.id
-  HAVING sum(reblogs_count + favourites_count) >= 5
+  HAVING sum(status_stats.reblogs_count + status_stats.favourites_count) >= 5
 ) t0
 GROUP BY account_id
 ORDER BY rank DESC
diff --git a/db/views/follow_recommendations_v02.sql b/db/views/follow_recommendations_v02.sql
index 673c5cc85..f67c6eecf 100644
--- a/db/views/follow_recommendations_v02.sql
+++ b/db/views/follow_recommendations_v02.sql
@@ -18,7 +18,7 @@ FROM (
   HAVING count(follows.id) >= 5
   UNION ALL
   SELECT account_summaries.account_id AS account_id,
-         sum(reblogs_count + favourites_count) / (1.0 + sum(reblogs_count + favourites_count)) AS rank,
+         sum(status_stats.reblogs_count + status_stats.favourites_count) / (1.0 + sum(status_stats.reblogs_count + status_stats.favourites_count)) AS rank,
          'most_interactions' AS reason
   FROM status_stats
   INNER JOIN statuses ON statuses.id = status_stats.status_id
@@ -28,7 +28,7 @@ FROM (
     AND account_summaries.sensitive = 'f'
     AND follow_recommendation_suppressions.id IS NULL
   GROUP BY account_summaries.account_id
-  HAVING sum(reblogs_count + favourites_count) >= 5
+  HAVING sum(status_stats.reblogs_count + status_stats.favourites_count) >= 5
 ) t0
 GROUP BY account_id
 ORDER BY rank DESC
diff --git a/db/views/user_ips_v01.sql b/db/views/user_ips_v01.sql
new file mode 100644
index 000000000..50a8201cd
--- /dev/null
+++ b/db/views/user_ips_v01.sql
@@ -0,0 +1,26 @@
+SELECT
+  user_id,
+  ip,
+  max(used_at) AS used_at
+FROM (
+  SELECT
+    id AS user_id,
+    sign_up_ip AS ip,
+    created_at AS used_at
+  FROM users
+  WHERE sign_up_ip IS NOT NULL
+  UNION ALL
+  SELECT
+    user_id,
+    ip,
+    updated_at
+  FROM session_activations
+  UNION ALL
+  SELECT
+    user_id,
+    ip,
+    created_at
+  FROM login_activities
+  WHERE success = 't'
+) AS t0
+GROUP BY user_id, ip