来自同一IP地址的两个用户

支持
1

大家好，
有没有办法查看两个用户是否由同一人创建（例如，它们是否来自同一个 IP 地址）？

2

前往他们的管理部分并检查 IP 地址。如果有人创建了第二个账户，第一个账户将显示在那里。

3

此数据探索查询将告诉你他们是否拥有相同的 IP 地址，但这并不等同于他们是同一个人。

WITH users_per_ip AS (
SELECT
count(1) AS user_count,
u.registration_ip_address AS ip,
max(u.created_at) AS last_create,
min(u.created_at) AS first_create,
(max(u.created_at) - min(u.created_at)) AS diff,
case when (max(u.suspended_at) is not null 
      or max(u.silenced_till) is not null )
      then 1 else 0 end AS bad
FROM users u
GROUP BY ip
)

SELECT
u.id AS user_id,
date_trunc('day',u.created_at)::date AS created,
date_trunc('day',upi.diff) AS days,
bad,
upi.ip AS ip_address
FROM users_per_ip upi
JOIN users u
ON u.registration_ip_address = upi.ip
WHERE upi.user_count > 1
ORDER BY upi.last_create DESC