From bda8e4f815708bd4deeb3c2310732e0b7a4e15e8 Mon Sep 17 00:00:00 2001
From: Takeshi Umeda <noel.yoshiba@gmail.com>
Date: Tue, 14 Dec 2021 07:21:14 +0900
Subject: Fix follow recommendation biased towards older accounts (#17126)

---
 db/views/account_summaries_v02.sql | 23 +++++++++++++++++++++++
 1 file changed, 23 insertions(+)
 create mode 100644 db/views/account_summaries_v02.sql

(limited to 'db/views/account_summaries_v02.sql')

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
-- 
cgit