LC 181 & LC 603 & LC 1731 & LC 1747

SQL-Basic
SQL
Publish Date:   2022-03-28

The following basic-level SQL problems are about ‘inequality linking’.

LeetCode 181

Employees Earning More Than Their Managers (Easy) [link]

SELECT e1.name AS Employee
FROM Employee e1
JOIN Employee e2
ON e1.managerId = e2.id
WHERE e1.managerId IS NOT NULL AND
    e1.salary > e2.salary;

LeetCode 603

Consecutive Available Seats (Easy) [link]

Using JOIN. The idea is to find consecutive free seats (absolute difference of two ids is 1).

SELECT DISTINCT a.seat_id
FROM Cinema a 
JOIN Cinema b
ON abs(a.seat_id - b.seat_id) = 1 
    AND a.free = 1 AND b.free = 1
ORDER BY a.seat_id;

Using window function. The idea is that if ids are consecutive, the difference between ids with the rank of row_number should be the same. The count of the same diff is the number of consecutive ids.

SELECT t2.seat_id
FROM(
    SELECT t.seat_id,
        COUNT(t.seat_id) OVER(PARTITION BY diff) AS cnt
    FROM(
        SELECT seat_id,
            seat_id - row_number() OVER(ORDER BY seat_id) AS diff
        FROM Cinema
        WHERE free = 1
    ) AS t
) AS t2
WHERE t2.cnt >= 2
ORDER BY t2.seat_id;

LeetCode 1731

The number of employees which report to each employee (Easy) [link]

Using LEFT JOIN

SELECT e1.reports_to AS employee_id, e2.name, COUNT(e2.employee_id) AS reports_count, ROUND(AVG(e1.age)) AS average_age
FROM Employees e1
LEFT JOIN Employees e2
ON e1.reports_to = e2.employee_id
GROUP BY e1.reports_to
HAVING e1.reports_to IS NOT NULL
ORDER BY e1.reports_to;

Using (inner) JOIN

SELECT e1.reports_to AS employee_id, e2.name, COUNT(e2.employee_id) AS reports_count, ROUND(AVG(e1.age)) AS average_age
FROM Employees e1
JOIN Employees e2
ON e1.reports_to = e2.employee_id
GROUP BY e1.reports_to
ORDER BY e1.reports_to;

LeetCode 1747

Leetflex Banned Accounts (Medium) [link]

Using JOIN.

SELECT DISTINCT l1.account_id
FROM LogInfo l1
JOIN LogInfo l2
ON l1.account_id = l2.account_id AND l1.ip_address < l2.ip_address
WHERE (l1.login BETWEEN l2.login AND l2.logout) 
    OR (l2.login BETWEEN l1.login AND l1.logout);
SQL-Basic
  TOC