Skip to content

Repérer les échanges de comptes (wip)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
    user.email,
    user_login.organization_id,
    organization.name,
    COUNT(DISTINCT user_activity.id) AS nb_activities,
    COUNT(DISTINCT country_code),
    COUNT(DISTINCT browser_version),
    user.created_at
FROM
    user_login
JOIN `user` ON `user`.id = user_login.user_id
JOIN organization_user_pivot ON organization_user_pivot.user_id = user_login.user_id
JOIN organization ON organization.id = organization_user_pivot.organization_id
JOIN user_activity ON user_activity.user_id = user_login.user_id AND user_activity.organization_id = user_login.organization_id
WHERE
    user_login.created_at >= '2023-01-01' 
    AND user.user_role_code IN ('USER', 'ADMIN_B2B')
    AND user_activity.ended_at > '2023-01-01'
GROUP BY
    user_login.user_id
HAVING
    COUNT(DISTINCT user_activity.id) > 500 AND COUNT(DISTINCT browser_version) > 1 AND COUNT(DISTINCT country_code) > 1

Keywords

user swap account