Skip to content

Affiche les users à supprimer qui possèdent un doublon

Dans la requête on a un filtre qui récupère seulement les dupliqués sans aucune user_activity.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH user_duplicated AS (
    SELECT
        user.id,
        user.email,
        COUNT(user_activity.id) AS ua_count,
        LAST (user.id, user.updated_at) AS user_id_to_keep
    FROM
        user
        LEFT JOIN user_activity ON user_activity.user_id = user.id
    WHERE
        user.deleted_at IS NULL
    GROUP BY
        user.email
    HAVING
        COUNT(*) > 1
        AND ua_count = 0
)
SELECT
    user.id,
    user.email
FROM
    user
    JOIN user_duplicated ON user_duplicated.email = user.email
    AND user_duplicated.user_id_to_keep != user.id;

Keywords

user select duplicated cte