From f7117646afddb2676e9275d8efe90c3a20c59021 Mon Sep 17 00:00:00 2001
From: Eugen Rochko <eugen@zeonfederated.com>
Date: Mon, 12 Apr 2021 12:37:14 +0200
Subject: Add cold-start follow recommendations (#15945)

---
 db/views/account_summaries_v01.sql      | 22 +++++++++++++++++++
 db/views/follow_recommendations_v01.sql | 38 +++++++++++++++++++++++++++++++++
 2 files changed, 60 insertions(+)
 create mode 100644 db/views/account_summaries_v01.sql
 create mode 100644 db/views/follow_recommendations_v01.sql

(limited to 'db/views')

diff --git a/db/views/account_summaries_v01.sql b/db/views/account_summaries_v01.sql
new file mode 100644
index 000000000..5a632b622
--- /dev/null
+++ b/db/views/account_summaries_v01.sql
@@ -0,0 +1,22 @@
+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
+  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
new file mode 100644
index 000000000..799abeaee
--- /dev/null
+++ b/db/views/follow_recommendations_v01.sql
@@ -0,0 +1,38 @@
+SELECT
+  account_id,
+  sum(rank) AS rank,
+  array_agg(reason) AS reason
+FROM (
+  SELECT
+    accounts.id AS account_id,
+    count(follows.id) / (1.0 + count(follows.id)) AS rank,
+    'most_followed' AS reason
+  FROM follows
+  INNER JOIN accounts ON accounts.id = follows.target_account_id
+  INNER JOIN users ON users.account_id = follows.account_id
+  WHERE users.current_sign_in_at >= (now() - interval '30 days')
+    AND accounts.suspended_at IS NULL
+    AND accounts.moved_to_account_id IS NULL
+    AND accounts.silenced_at IS NULL
+    AND accounts.locked = 'f'
+    AND accounts.discoverable = 't'
+  GROUP BY accounts.id
+  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,
+         'most_interactions' AS reason
+  FROM status_stats
+  INNER JOIN statuses ON statuses.id = status_stats.status_id
+  INNER JOIN accounts ON accounts.id = statuses.account_id
+  WHERE statuses.id >= ((date_part('epoch', now() - interval '30 days') * 1000)::bigint << 16)
+    AND accounts.suspended_at IS NULL
+    AND accounts.moved_to_account_id IS NULL
+    AND accounts.silenced_at IS NULL
+    AND accounts.locked = 'f'
+    AND accounts.discoverable = 't'
+  GROUP BY accounts.id
+  HAVING sum(reblogs_count + favourites_count) >= 5
+) t0
+GROUP BY account_id
+ORDER BY rank DESC
-- 
cgit