I can’t find a solution.

I wrote the following code, but it doesn’t work quite right:

WITH Dublicates AS (
SELECT player_id
FROM player_agg
GROUP BY player_id
HAVING SUM(reg_cnt) = 2
AND COUNT(DISTINCT license) = 2
),
FilteredRows AS (
SELECT m.*
FROM player_agg m
JOIN Dublicates ip
ON m.player_id = ip.player_id
WHERE (m.reg_cnt = 1 AND m.license = 'COM')
OR m.reg_cnt <> 1
)
SELECT *
FROM FilteredRows

I have a table where some users have two licenses: WL and COM. These users have a reg_cnt sum of 2, although it should be 1.

Therefore, I want to keep only the record with the COM license for such users. But for other users, everything should remain unchanged.

This filtering should apply only to users with two licenses and two registrations.

Please help me fix my query