💡
Phần này không quá khó, chỉ cần nắm được một vài kỹ thuật JOIN kèm điều kiện lọc là được. Đề bài đánh đố nhiều ở edge cases, câu truy vấn cần phải xử lý được hết các trường hợp con.

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
  • Một vài câu hỏi có thể xử lý dễ dàng bằng hàm UNION nhưng lại làm câu truy vấn dài và không tối ưu.
  • Một vài câu hỏi bắt buộc phải sử dụng đến hàm liên quan đến WINDOW FUNCTIONS có thể gây khó hiểu, WINDOW FUNCTIONS có rất nhiều cách sử dụng và biến thể tuỳ vào yêu cầu của câu truy vấn.

Câu 1713. The Number of Employees Which Report to Each Employee

Table: Employees
+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| reports_to  | int      |
| age         | int      |
+-------------+----------+
 

For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.

Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.
Đáp án
select
  e1.employee_id,
  e1.name,
  count(e2.reports_to) as reports_count,
  round(avg(e2.age)) as average_age
from
  Employees e1
  join Employees e2 on e1.employee_id = e2.reports_to
  and e2.reports_to is not null
  and e2.employee_id != e2.reports_to
group by
  1,
  2
order by
  1 asc

Câu 1789. Primary Department for Each Employee 

Table: Employee
+---------------+---------+
| Column Name   |  Type   |
+---------------+---------+
| employee_id   | int     |
| department_id | int     |
| primary_flag  | varchar |
+---------------+---------+
Employees can belong to multiple departments. When the employee joins other departments, they need to decide which department is their primary department. Note that when an employee belongs to only one department, their primary column is 'N'.

Write a solution to report all the employees with their primary department. For employees who belong to one department, report their only department.
Đáp án
select
  d.employee_id,
  case
    when p.department_id is null then s.department_id
    else p.department_id
  end as department_id
from
  (
    select
      distinct employee_id
    from
      Employee
  ) d
  left join (
    select
      employee_id,
      department_id
    from
      Employee
    where
      primary_flag = 'Y'
  ) p on p.employee_id = d.employee_id
  left join (
    select
      employee_id,
      min(department_id) as department_id
    from
      Employee
    group by
      1
    having
      count(department_id) = 1
  ) s on d.employee_id = s.employee_id

Câu 610. Triangle Judgement

Table: Triangle
+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
| z           | int  |
+-------------+------+

Report for every three line segments whether they can form a triangle.
Đáp án
select
  x,
  y,
  z,
  case
    when x < y + z
    and y < x + z
    and z < x + y then 'Yes'
    else 'No'
  end as triangle
from
  Triangle
Giải thích

Điều kiện để 3 đoạn thẳng có thể hình thành 1 tam giác là chiều dài 1 canh phải nhỏ hơn tổng của chiều dài 2 cạnh còn lại.

Câu 180. Consecutive Numbers

Table: Logs
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+ 

Find all numbers that appear at least three times consecutively.
Đáp án
select
  distinct l1.num ConsecutiveNums
from
  Logs l1
  join Logs l2 on l1.id = l2.id + 1
  and l1.num = l2.num
  join Logs l3 on l2.id = l3.id + 1
  and l2.num = l3.num

Câu 1164. Product Price at a Given Date

Table: Products
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+

Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.
Đáp án
select
  tmp1.product_id,
  case
    when tmp2.new_price is null then 10
    else tmp2.new_price
  end as price
from
  (
    select
      distinct product_id
    from
      Products
  ) tmp1
  left join (
    select
      p.product_id,
      p.new_price
    from
      Products p
      join (
        select
          product_id,
          max(change_date) as change_date
        from
          Products p
        where
          change_date <= '2019-08-16' :: date
        group by
          1
      ) tmp on p.product_id = tmp.product_id
      and p.change_date = tmp.change_date
  ) tmp2 on tmp1.product_id = tmp2.product_id

Câu 1204. Last Person to Fit in the Bus

Table: Queue
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+

There is a queue of people waiting to board a bus. However, the bus has a weight limit of 1000 kilograms, so there may be some people who cannot board.
Write a solution to find the person_name of the last person that can fit on the bus without exceeding the weight limit. The test cases are generated such that the first person does not exceed the weight limit.
Đáp án
with acum as (
  select
    turn,
    person_name,
    sum(weight) over(order by turn asc) as acum_weight
  from Queue order by turn asc
)
select
  person_name
from
  (
    select
      a1.person_name,
      case
        when a1.acum_weight <= 1000 and a2.acum_weight > 1000 then true
        when a1.acum_weight <= 1000 and a1.turn = (select max(turn) from Queue) then true
        else false
      end as valid
    from
      acum a1
      left join acum a2 on a1.turn = a2.turn - 1
  ) tmp
where
  valid is true

Câu 1907. Count Salary Categories

Table: Accounts
+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+

Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:
Đáp án
SELECT
  nt.*,
  coalesce(tmp.count, 0) accounts_count
FROM
  (
    VALUES
      ('Low Salary'),
      ('Average Salary'),
      ('High Salary')
  ) AS nt(category)
  left join (
    select
      case
        when income < 20000 then 'Low Salary'
        when income >= 20000
        and income <= 50000 then 'Average Salary'
        else 'High Salary'
      end as category,
      count(account_id)
    from
      Accounts
    group by
      1
  ) tmp on tmp.category = nt.category