LeetCode SQL - Relationships

Posted on Edited on

Medium

1949. Strong Friendship

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
DROP TABLE if EXISTS Friendship;
CREATE TABLE
    If NOT EXISTS Friendship (user1_id INT, user2_id INT);
TRUNCATE TABLE Friendship;
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '2');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '3');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('2', '3');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '4');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('2', '4');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '5');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('2', '5');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '7');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('3', '7');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '6');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('3', '6');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('2', '6');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
WITH
    friends AS (
        SELECT
            user1_id,
            user2_id
        FROM
            Friendship
        UNION
        SELECT
            user2_id,
            user1_id
        FROM
            Friendship
    )
SELECT
    f1.user1_id,
    f1.user2_id,
    COUNT(f3.user2_id) AS common_friend
FROM
    Friendship f1
    INNER JOIN friends f2 ON f1.user1_id = f2.user1_id -- user1_id's friends
    INNER JOIN friends f3 ON f1.user2_id = f3.user1_id -- user2_id's friends
    AND f2.user2_id = f3.user2_id -- common friends
GROUP BY
    1,
    2
HAVING
    common_friend >= 3;

1264. Page Recommendations

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
DROP TABLE if EXISTS Friendship;
DROP TABLE if EXISTS Likes;
CREATE TABLE
    If NOT EXISTS Friendship (user1_id INT, user2_id INT);
CREATE TABLE
    If NOT EXISTS Likes (user_id INT, page_id INT);
TRUNCATE TABLE Friendship;
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '2');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '3');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '4');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('2', '3');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('2', '4');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('2', '5');
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('6', '1');
TRUNCATE TABLE Likes;
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('1', '88');
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('2', '23');
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('3', '24');
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('4', '56');
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('5', '11');
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('6', '33');
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('2', '77');
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('3', '77');
INSERT INTO
    Likes (user_id, page_id)
VALUES
    ('6', '88');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
WITH
    cte AS (
        SELECT
            user1_id,
            user2_id
        FROM
            Friendship
        UNION ALL
        SELECT
            user2_id,
            user1_id
        FROM
            Friendship
    )
SELECT DISTINCT
    (page_id) AS recommended_page
FROM
    cte
    INNER JOIN Likes ON user2_id = user_id
WHERE
    user1_id = 1
    AND page_id NOT IN (
        SELECT
            page_id
        FROM
            Likes
        WHERE
            user_id = 1
    );

Hard

1917. Leetcodify Friends Recommendations

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
DROP TABLE if EXISTS Listens;
DROP TABLE if EXISTS Friendship;
CREATE TABLE
    If NOT EXISTS Listens (user_id INT, song_id INT, DAY DATE);
CREATE TABLE
    If NOT EXISTS Friendship (user1_id INT, user2_id INT);
TRUNCATE TABLE Listens;
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('1', '10', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('1', '11', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('1', '12', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('2', '10', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('2', '11', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('2', '12', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('3', '10', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('3', '11', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('3', '12', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('4', '10', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('4', '11', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('4', '13', '2021-03-15');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('5', '10', '2021-03-16');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('5', '11', '2021-03-16');
INSERT INTO
    Listens (user_id, song_id, DAY)
VALUES
    ('5', '12', '2021-03-16');
TRUNCATE TABLE Friendship;
INSERT INTO
    Friendship (user1_id, user2_id)
VALUES
    ('1', '2');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
WITH
    cte AS (
        SELECT
            l1.user_id AS uid1,
            l2.user_id AS uid2
        FROM
            Listens l1
            INNER JOIN Listens l2 ON l1.day = l2.day
            AND l1.song_id = l2.song_id
            AND l1.user_id != l2.user_id
        GROUP BY
            l1.user_id,
            l2.user_id,
            l1.day
        HAVING
            COUNT(DISTINCT l1.song_id) >= 3
    ),
    f (uid1, uid2) AS (
        SELECT
            user1_id,
            user2_id
        FROM
            Friendship
        UNION
        SELECT
            user2_id,
            user1_id
        FROM
            Friendship
    )
SELECT
    uid1 AS user_id,
    uid2 AS recommended_id
FROM
    cte
WHERE
    (uid1, uid2) NOT IN (
        SELECT
            uid1,
            uid2
        FROM
            f
    )
GROUP BY
    uid1,
    uid2;