LeetCode EZ - SQL50 - Subqueries

Các kiến thức cần thiết để giải quyết list câu hỏi này

• Cần phải nắm rõ các kiến thức cơ bản của SQL sử dụng trong các phần trước. Như LeetCode EZ - SQL50 - Basic Joins và LeetCode EZ - SQL50 - Basic Aggregate Functions.
• Sử dụng được thành thạo các WINDOW FUNCTIONS
• Hiểu cách Subquery hoạt động và thực hiện JOIN các bảng hợp lý
• Làm quen với AGGREGATE FUNCTIONS để tạo mảng (array) thay vì count, sum hay avg như thông thường.

Câu 1978. Employees Whose Manager Left the Company

Table: Employees
+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| manager_id  | int      |
| salary      | int      |
+-------------+----------+

Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.

Đáp án

select
  tmp.employee_id
from
  Employees e
  right join (
    select
      employee_id,
      manager_id
    from
      Employees
    where
      salary < 30000
      and manager_id is not null
  ) tmp on e.employee_id = tmp.manager_id
where
  e.employee_id is null
order by
  1 asc

Câu 626. Exchange Seats

Table: Seat
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Đáp án

select
  case
    when id % 2 = 0 then id -1
    when id % 2 != 0
    and id != mid then id + 1
    else id
  end as id,
  student
from
  Seat
  cross join (select max(id) mid from Seat) as tmp
order by 1

Câu 1341. Movie Rating

Table: Movies
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| title         | varchar |
+---------------+---------+
 
Table: Users
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
+---------------+---------+

Table: MovieRating
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| user_id       | int     |
| rating        | int     |
| created_at    | date    |
+---------------+---------+

Write a solution to:
  - Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
  - Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

Đáp án

select
  results
from
  (
    (
      select
        name as results,
        'name' as name
      from
        Users u
        join (
          select
            user_id,
            count(rating) cnt_rating
          from
            MovieRating
          group by
            1
        ) tmp on tmp.user_id = u.user_id
      order by
        cnt_rating desc,
        u.name asc
      limit
        1
    )
    union
      (
        select
          title as results,
          'title' as name
        from
          Movies m
          join (
            select
              movie_id,
              avg(rating) as avg_rating
            from
              MovieRating
            where
              date_trunc('month', created_at) = '2020-02-01' :: date
            group by
              1
          ) tmp on m.movie_id = tmp.movie_id
        order by
          avg_rating desc,
          m.title asc
        limit
          1
      )
  )

Câu 1321. Restaurant Growth

Table: Customer

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+

Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.

Đáp án

select
  visited_on,
  amount,
  average_amount
from
  (
    select
      visited_on,
      round(
        sum(amount) OVER (ORDER BY visited_on asc ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) amount,
      round(
        avg(amount) OVER (ORDER BY visited_on asc ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) average_amount,
      rank() over (order by visited_on asc) as rank
    from
      (
        select
          visited_on,
          sum(amount) as amount
        from
          Customer
        group by 1
        order by 1 asc
      )
  ) tmp
where
  rank >= 7

Câu 602. Friend Requests II: Who Has the Most Friends

Table: RequestAccepted
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+

Write a solution to find the people who have the most friends and the most friends number.

Đáp án

select
  requester_id id,
  count(distinct accepter_id) num
from
  (
    (
      select
        accepter_id as requester_id,
        requester_id as accepter_id
      from
        RequestAccepted
    )
    union
      (
        select
          requester_id,
          accepter_id
        from
          RequestAccepted
      )
  ) tmp
group by 1 order by 2 desc limit 1

Câu 585. Investments in 2016

Table: Insurance

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| pid         | int   |
| tiv_2015    | float |
| tiv_2016    | float |
| lat         | float |
| lon         | float |
+-------------+-------+

Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who:

  - have the same tiv_2015 value as one or more other policyholders, 
  - and are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique).

Đáp án

with valid as (
  select
    tmp1.pid
  from
    (
      select
        unnest(pids) pid
      from
        (
          select
            tiv_2015,
            array_agg(pid) pids
          from
            Insurance
          group by 1
          having count(pid) > 1
        )
    ) tmp1
    join (
      select
        unnest(pids) pid
      from
        (
          select
            array_agg(pid) pids,
            lat,
            lon
          from
            Insurance
          group by 2, 3
          having count(pid) = 1
        ) tmp
    ) tmp2 on tmp1.pid = tmp2.pid
)
select
  round(sum(i.tiv_2016) :: numeric, 2) tiv_2016
from
  Insurance i
  join valid v on v.pid = i.pid

Câu 185. Department Top Three Salaries

Table: Employee
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+

Table: Department
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write a solution to find the employees who are high earners in each of the departments.

Đáp án

with j as (
  select
    d.name as Department, e.name as Employee, e.salary as Salary
  from
    Employee e
    join Department d on e.departmentId = d.id
  order by 1, 3 desc
)
select
  j.*
from j
  join (
    select Department, Salary
    from
      (
        select *, rank() over(partition by Department order by Salary desc)
        from
          (
            select Department, Salary
            from j
            group by 1,2 order by 1,2 desc
          ) tmp
      )
    where rank <= 3
  ) tmp on j.Department = tmp.Department and j.Salary = tmp.Salary