Remove IP tracking columns from users table (#16409)

1 files changed, 26 insertions, 0 deletions

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