Скрипт Postgre на поиск дубликатов учетных записей

Question

Пишу скрипт на PostgreSql. Основная задача скрипта - вывод учетных записей пользователей, которые имеют более 3 дубликатов учетных записей, созданных за последние 30 дней (т.е. 1 пользователь создал учетную запись, авторизовался в ней, появился лог авторизации по ip и device. Мы обнаруживаем, что по данным ip и device уже происходили авторизации в других учетных записях (другой user_id, но ip и device те же) Соответственно, предыдущие такие учетки считаем дубликатами 1 пользователя)

select 
distinct таблица1.user_id, 
  таблица1.status, таблица1.created_at 
from 
  таблица1
  inner join таблица2 on таблица1.user_id = таблица2.user_id
where 
  таблица1.created_at >= (current_date - 2)
  and (таблица2.ip, таблица2.device) IN
(
select 
таблица2.ip, таблица2.device
from 
таблица2
having COUNT(distinct user_id) >=3
and /вот здесь никак не могу разобраться с условием, чтобы в выборку включались только дубликаты, созданные за последние 30 дней. Без этого условия выводится список тех, кто имеет более 3 дубликатов учеток, но среди которых могут быть и очень старые, что не нужно/
)

Таблица1: содержит данные о пользователях (user_id, status, created_at - дата регистрации и т.д.)

Таблица2: содержит данные о логах авторизации (ip, device, user_id, created_at - дата авторизации) В таблице2 логов по 1 пользователю может быть несколько.

Сам скрипт работает на поиск пользователей, у которых с одного ip и device были авторизации в других учетных записях (дубликаты), но нужно сузить круг, чтобы выводились только пользователи с дубликатами свежими (т.е. созданными за последние 30 дней).

Help me, please...

Answer 1

На мой взгляд, надо просто почти продублировать то условие, что я уже приводил для прошлого вопроса. Рекомендую проанализировать его детально. Если что-то сложно, вы спросите.

Мне не понятно, зачем вы DISTINCT используете в этом вложенном запросе (впрочем, и насчет начального SELECT не ясно). Вы рискуете, что он вам несколько попыток входа в одну схлопнет во вложннном запросе, так что я * поставил все же.

Итого, я советую вложенный запрос после IN написать примерно так (проверьте, я не стал на mySQL проверять уже):

select 
таблица2.ip, таблица2.device
from 
таблица2
inner join таблица1 on таблица1.user_id = таблица2.user_id
WHERE таблица1.created_at >= (current_date - 31) 
GROUP BY таблица2.ip, таблица2.device 
HAVING COUNT(*) >=3

БЛОГ НА HUSL

Скрипт Postgre на поиск дубликатов учетных записей

Ответы (1 шт):