LC 577 & LC 585 & LC 597 & LC 602

SQL-Basic
SQL
Publish Date:   2022-04-07

The following basic-level SQL problems.

LeetCode 577

Employee Bonus (Easy) [link]

SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b
ON e.empId = b.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL;

LeetCode 585

Investments in 2016 (Medium) [link]

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM insurance
WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM insurance
    GROUP BY tiv_2015
    HAVING COUNT(tiv_2015) >= 2
) AND
    CONCAT(lat,lon) IN (
    SELECT CONCAT(lat,lon)
    FROM insurance
    GROUP BY lat,lon
    HAVING COUNT(*) = 1
);

LeetCode 597

Friend Requests I: Overall Acceptance Rate (Easy) [link]

SELECT ROUND(IFNULL(
(
    SELECT COUNT(*)
    FROM (
        SELECT DISTINCT requester_id, accepter_id
        FROM RequestAccepted
    ) A
) / (
    SELECT COUNT(*) 
    FROM (
        SELECT DISTINCT sender_id, send_to_id
        FROM FriendRequest
    ) B
), 0), 2) AS accept_rate;

LeetCode 602

Friend Requests II: Who Has the Most Friends (Medium) [link]

SELECT t2.friend AS id, t2.cnt AS num
FROM (
    SELECT
        t.friend, COUNT(t.friend) AS cnt, 
        dense_rank() OVER(ORDER BY COUNT(t.friend) DESC) AS rnk
    FROM (
        SELECT requester_id AS friend
        FROM RequestAccepted
        UNION ALL
        SELECT accepter_id
        FROM RequestAccepted
    ) t
    GROUP BY t.friend
) t2
WHERE t2.rnk = 1
SQL-Basic
  TOC