LeetCode 1919. Leetcodify Similar Friends SQL Solution

Problem

LeetCode SQL Problem

1919. Leetcodify Similar Friends

Listens table

user_id	song_id	day
1	10	2021-03-15 00:00:00
1	11	2021-03-15 00:00:00
1	12	2021-03-15 00:00:00
2	10	2021-03-15 00:00:00
2	11	2021-03-15 00:00:00
2	12	2021-03-15 00:00:00
3	10	2021-03-15 00:00:00
3	11	2021-03-15 00:00:00
3	12	2021-03-15 00:00:00
4	10	2021-03-15 00:00:00
4	11	2021-03-15 00:00:00
4	13	2021-03-15 00:00:00
5	10	2021-03-16 00:00:00
5	11	2021-03-16 00:00:00
5	12	2021-03-16 00:00:00

Friendship table

user1_id	user2_id
1	2
2	4
2	5

Solution

MySQL

MySQL Solution to report users who listened to the same three or more different songs on the same day.

WITH song_listened_analysis
AS (
	-- Perform Self Join so we can detect user_id pairs that listened to the same song on the same day
	SELECT A.user_id AS user_x
		,B.user_id AS user_y
		-- Only count the same song once
		,count(DISTINCT A.song_id) AS same_song_count
	FROM Listens AS A
	INNER JOIN Listens AS B ON A.user_id < B.user_id
		AND A.day = B.day
		AND A.song_id = B.song_id
	GROUP BY A.user_id
		,B.user_id
	-- Only return user_id pairs that listened to the same three or more different songs on the same day.
	HAVING same_song_count >= 3
	)
-- INNER JOIN Friendship table so we only return user_id pairs that are friends
SELECT F.*
FROM song_listened_analysis AS SLA
INNER JOIN Friendship AS F ON (
		SLA.user_x = F.user1_id
		AND SLA.user_y = F.user2_id
		);

Query Output

user1_id	user2_id
1	2

TSQL

TSQL Solution to report users who listened to the same three or more different songs on the same day.

WITH song_listened_analysis
AS (
    -- Perform Self Join so we can detect user_id pairs that listened to the same song on the same day
    SELECT A.user_id AS user_x
        ,B.user_id AS user_y
        -- Only count the same song once
        ,count(DISTINCT A.song_id) AS same_song_count
    FROM Listens AS A
    INNER JOIN Listens AS B ON A.user_id < B.user_id
        AND A.day = B.day
        AND A.song_id = B.song_id
    GROUP BY A.user_id
        ,B.user_id
    -- Only return user_id pairs that listened to the same three or more different songs on the same day.
    HAVING count(DISTINCT A.song_id) >= 3
    )
-- INNER JOIN Friendship table so we only return user_id pairs that are friends
SELECT F.*
FROM song_listened_analysis AS SLA
INNER JOIN Friendship AS F ON (
        SLA.user_x = F.user1_id
        AND SLA.user_y = F.user2_id
        );

Query Output

user1_id	user2_id
1	2