LC 1303 & LC 1308 & LC 1501

SQL-Advanced
SQL
Publish Date:   2022-03-20

The following advanced-level SQL problems are about ‘Summary Function’. More: using LEFT to slice the string.

LeetCode 1303

Find the Team Size (Easy) [link]

Using self join

SELECT e1.employee_id, COUNT(*) AS team_size
FROM Employee e1
LEFT JOIN Employee e2
ON e1.team_id = e2.team_id
GROUP BY employee_id;

SELECT employee_id, (
    SELECT COUNT(*)
    FROM Employee e1
    WHERE e1.team_id = e2.team_id
) AS team_size
FROM Employee e2;

Window function

SELECT employee_id, COUNT(team_id) OVER (PARTITION BY team_id) AS team_size
FROM Employee

LeetCode 1308

Running Total for Different Genders (Medium) [link]

Using window function

SELECT gender, day, 
    SUM(score_points) OVER (PARTITION BY gender ORDER BY day) AS total
FROM Scores;

Using self join

SELECT s1.gender, s1.day, SUM(s2.score_points) AS total
FROM Scores s1
JOIN Scores s2
ON s1.gender = s2.gender AND s1.day >= s2.day
GROUP BY s1.gender, s1.day
ORDER BY s1.gender, s1.day;

SELECT s1.gender, s1.day, SUM(s2.score_points) AS total
FROM Scores s1, Scores s2
WHERE s1.gender = s2.gender AND s1.day >= s2.day
GROUP BY s1.gender, s1.day
ORDER BY s1.gender, s1.day;

LeetCode 1501

Countries You Can Safely Invest In (Medium) [link]

with tmp as (
    SELECT caller_id caller, duration FROM Calls
    UNION ALL
    SELECT callee_id caller, duration FROM Calls
)
SELECT C.name country
FROM tmp
LEFT JOIN Person P ON tmp.caller = P.id
LEFT JOIN Country C ON LEFT(P.phone_number,3) = C.country_code
GROUP BY C.name
HAVING AVG(tmp.duration) > (SELECT AVG(duration) FROM tmp);
SQL-Advanced
  TOC