For an hour tried to solve it with Claude, 4o, Perplexity, but could not.
Then found similar example SQL distinct on 2 columns , with 3 options how to solve – first option gave error “rn” column not found, second option ( /a/45950364/7284577 ) gave me 6 of 7 rows, and last one I could not run.
That is why will ask here.
Have table time_user
id;user_id_1;user_id_2
51353;21014;21013
51352;21012;21013
51351;21011;21013
51350;21010;21011
51349;21015;21010
51348;21014;21010
51347;21013;21010
If we will sort by desc ‘id’ column and will start to collect rows with distinct user_id_ in both columns (user_id_1, user_id_2), then we should take 51353 (with 21014 and 21013), then we ignore 51352 and 51351, becuse they have user_id_2==21013 which is already was in ID 51353, then we take id 51350 cuz it have user_ids 21010 and 21011. 51349-51347 should be ignored, because user_ids from these rows where in 51353 and 51350.
The closest solve was given bt Perplexity, and it returned 51353 only:
WITH UniqueUsers AS (
SELECT
id,
user_id_1,
user_id_2,
ROW_NUMBER() OVER (ORDER BY id DESC) AS rn
FROM time_user
),
Filtered AS (
SELECT
u.id,
u.user_id_1,
u.user_id_2,
(SELECT COUNT(*)
FROM UniqueUsers f
WHERE f.rn < u.rn
AND (f.user_id_1 IN (u.user_id_1, u.user_id_2)
OR f.user_id_2 IN (u.user_id_1, u.user_id_2))) AS duplicate_count
FROM UniqueUsers u
)
SELECT id
FROM Filtered
WHERE duplicate_count = 0 ORDER BY id DESC;
I want to receive 51353 and 51350