LeetCode SQL - Relationships

Table of contents

Medium

1949. Strong Friendship

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');

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

WITH cte(friend_id) AS (
    SELECT user2_id
    FROM Friendship
    WHERE user1_id = 1
    
    UNION
    
    SELECT user1_id
    FROM Friendship
    WHERE user2_id = 1
)
SELECT DISTINCT l.page_id AS recommended_page
FROM
	cte c
	JOIN Likes l ON c.friend_id = l.user_id
WHERE l.page_id NOT IN (
    SELECT page_id
    FROM Likes
    WHERE user_id = 1
)

597. Friend Requests I: Overall Acceptance Rate

SELECT
    ROUND(
        COALESCE(
            (SELECT COUNT(DISTINCT (requester_id, accepter_id)) FROM RequestAccepted)::NUMERIC 
            / 
            NULLIF(
             (SELECT COUNT(DISTINCT (sender_id, send_to_id)) FROM FriendRequest),
            0),
        0.00),
    2) AS accept_rate;

602. Friend Requests II: Who Has the Most Friends

‘The test cases are generated so that only one person has the most friends.’

• Without this constraint we must use DENSE_RANK()

Two Cols + UNION

WITH cte (id, friend_id) AS (
    SELECT requester_id, accepter_id FROM RequestAccepted
    UNION
    SELECT accepter_id, requester_id FROM RequestAccepted
)
SELECT
    id,
    COUNT(friend_id) AS num
FROM cte
GROUP BY id
ORDER BY num DESC
LIMIT 1

One Col + UNION ALL

WITH cte(id) AS (
    SELECT requester_id
    FROM RequestAccepted
    UNION ALL
    SELECT accepter_id
    FROM RequestAccepted
)
SELECT
    id,
    COUNT(id) AS num
FROM cte
GROUP BY id
ORDER BY num DESC
LIMIT 1

1699. Number of Calls Between Two Persons

With Scalar Functions

SELECT 
    LEAST(from_id, to_id) AS person1,
    GREATEST(from_id, to_id) AS person2,
    COUNT(*) AS call_count,
    SUM(duration) AS total_duration
FROM Calls
GROUP BY
	person1,
	person2

Without Scalar Functions

WITH cte AS (
    SELECT
        from_id AS person1,
        to_id AS person2,
        duration
    FROM Calls
    UNION ALL
    SELECT
        to_id AS person1,
        from_id AS person2,
        duration
    FROM Calls
)
SELECT
    person1,
    person2,
    COUNT(*) AS call_count,
    SUM(duration) AS total_duration
FROM cte
GROUP BY
    person1,
    person2
HAVING person2 < person1

614. Second Degree Follower

SELECT
    followee AS follower,
    COUNT(follower) AS num
FROM follow
WHERE followee IN (SELECT DISTINCT follower FROM follow)
GROUP BY followee
ORDER BY follower

Hard

1917. Leetcodify Friends Recommendations

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');

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;

1270. All People Report to the Given Manager

WITH RECURSIVE cte AS (
	SELECT employee_id FROM Employees WHERE manager_id = 1
	UNION
	SELECT e.employee_id
	FROM
		Employees e
		JOIN cte c ON e.manager_id = c.employee_id
)
SELECT employee_id
FROM cte
WHERE employee_id != 1
ORDER BY employee_id