LC 1919 & LC 1972 & LC 2004 & LC 2010 & LC 2118

The following advanced-level SQL problems.

LeetCode 1919

Leetcodify Similar Friends (Hard) [link]

SELECT DISTINCT l1.user_id AS user1_id, l2.user_id AS user2_id
FROM Friendship f, Listens l1, Listens l2
WHERE f.user1_id = l1.user_id
    AND f.user2_id = l2.user_id
    AND l1.song_id = l2.song_id
    AND l1.day = l2.day
GROUP BY l1.user_id, l2.user_id, l1.day
HAVING COUNT(DISTINCT l1.song_id) >= 3;

LeetCode 1972

First and Last Call On the Same Day (Hard) [link]

Use window function dense_rank() to find the first and the last caller. Note that when ranking the call time, we rank by day, so DATE_FORMAT(call_time, '%Y-%m-%d').

SELECT DISTINCT caller_id AS user_id
FROM(
    SELECT *,
        dense_rank() OVER (PARTITION BY caller_id, DATE_FORMAT(call_time, '%Y-%m-%d') ORDER BY call_time) AS asc_rank,
        dense_rank() OVER (PARTITION BY caller_id, DATE_FORMAT(call_time, '%Y-%m-%d') ORDER BY call_time DESC) AS desc_rank
    FROM(
        SELECT caller_id, recipient_id, call_time
        FROM Calls
        UNION ALL
        SELECT recipient_id, caller_id, call_time
        FROM Calls
    ) t
) tt
WHERE (asc_rank = 1 OR desc_rank = 1)
GROUP BY caller_id, DAY(call_time)
HAVING COUNT(DISTINCT recipient_id) = 1;

LeetCode 2004

The number of Seniors and Juniors to Join the Company (Hard) [link]

Note that it is possible that all of the senior’s salaries are above 70000, where we could get null value for max_accum in table SeniorsMax. In this case we should add IFNULL(,0) , otherwise, the result is not correct.

with Juniors as (
    SELECT *,
        SUM(salary) OVER(ORDER BY salary) AS accum
    FROM Candidates
    WHERE experience = 'Junior'
)

,Seniors as (
    SELECT *,
        SUM(salary) OVER(ORDER BY salary) AS accum
    FROM Candidates
    WHERE experience = 'Senior'
)

,SeniorsMax as (
    SELECT MAX(accum) as max_accum
    FROM Seniors
    WHERE accum <= 70000
)

SELECT 'Senior' AS experience, COUNT(employee_id) AS accepted_candidates
FROM Seniors
WHERE accum <= 70000
UNION ALL
SELECT 'Junior' AS experience, COUNT(employee_id) AS accepted_candidates
FROM Juniors
WHERE accum <= (70000 - IFNULL((
    SELECT max_accum 
    FROM SeniorsMax
),0));

LeetCode 2010

The number of Seniors and Juniors to Join the Company II (Hard) [link]

Very similar to LC 2004.

with Juniors as (
    SELECT *,
        SUM(salary) OVER(ORDER BY salary) AS accum
    FROM Candidates
    WHERE experience = 'Junior'
)

,Seniors as (
    SELECT *,
        SUM(salary) OVER(ORDER BY salary) AS accum
    FROM Candidates
    WHERE experience = 'Senior'
)

,SeniorsMax as (
    SELECT MAX(accum) as max_accum
    FROM Seniors
    WHERE accum <= 70000
)

SELECT employee_id
FROM Seniors
WHERE accum <= 70000
UNION ALL
SELECT employee_id
FROM Juniors
WHERE accum <= (70000 - IFNULL((
    SELECT max_accum 
    FROM SeniorsMax
),0));

LeetCode 2118

Build the Equation (Hard) [link]

Idea: Use GROUP_CONCAT to concat values in a column.

with tmp as(
    SELECT power,
        CASE power 
            WHEN 0 THEN IF(factor < 0, factor, CONCAT('+', factor))
            WHEN 1 THEN CONCAT(IF(factor < 0, factor, CONCAT('+', factor)), 'X') 
            ELSE CONCAT(IF(factor < 0, factor, CONCAT('+',factor)), "X^", power) 
            END AS equation
    FROM Terms    
)

SELECT CONCAT(GROUP_CONCAT(equation ORDER BY power DESC SEPARATOR ''), '=0') AS equation
FROM tmp;