💡
Thành thạo 3 phương pháp join phổ biến bao gồm left/right join , cross join và phép join thông thường.

Câu 1378. Replace Employee ID With The Unique Identifier

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

Table: EmployeeUNI
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| unique_id     | int     |
+---------------+---------+

Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.
Đáp án

Có thể sử dụng left join

-- Write your PostgreSQL query statement below
select eu.unique_id, e.name from Employees e left join EmployeeUNI eu on e.id = eu.id;

hoặc right join

-- Write your PostgreSQL query statement below
select eu.unique_id, e.name from EmployeeUNI eu right join Employees e on e.id = eu.id;
Giải thích

Phép join left hoặc right sẽ không loại bỏ những dòng mà bảng phụ (EmployeeUNI) không có dữ liệu nhưng bảng chính có (Employees).

Khi đó của unique_id của những nhân viên không có trong bảng EmployeeUNI sẽ trả về null.

Câu 1068. Product Sales Analysis I

Table: Sales
+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+

Table: Product
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+

Write a solution to report the product_name, year, and price for each sale_id in the Sales table.
Đáp án
-- Write your PostgreSQL query statement below
select
  p.product_name,
  s.year,
  s.price
from
  Sales s
  join Product p on s.product_id = p.product_id;

Câu 1581. Customer Who Visited but Did Not Make Any Transactions

Table: Visits
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+

Table: Transactions
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| transaction_id | int     |
| visit_id       | int     |
| amount         | int     |
+----------------+---------+

Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.
Đáp án
-- Write your PostgreSQL query statement below
SELECT
  v.customer_id,
  COUNT(v.visit_id) AS count_no_trans
from
  Visits v
  LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE
  t.amount IS NULL
GROUP BY
  v.customer_id;
Giải thích

Điểm mấu chốt là sử dụng left join để tìm ra tập khách hàng đã ghé nhưng không mua hàng.

Câu 197. Rising Temperature

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+

Write a solution to find all dates' id with higher temperatures compared to its previous dates (yesterday).
Đáp án
select
  w2.id
from
  Weather w1
  join Weather w2 on w2.recordDate = w1.recordDate + '1day' :: interval
  and w2.temperature > w1.temperature

Câu 1661. Average Time of Process per Machine

Table: Activity

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+

There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
Đáp án
select
  a1.machine_id,
  round(avg(a2.timestamp - a1.timestamp) :: numeric, 3) as processing_time
from
  Activity a1
  join Activity a2 on a1.machine_id = a2.machine_id
  and a1.process_id = a2.process_id
  and a1.activity_type = 'start'
  and a2.activity_type = 'end'
group by
  1

Câu 577. Employee Bonus

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| empId       | int     |
| name        | varchar |
| supervisor  | int     |
| salary      | int     |
+-------------+---------+

Table: Bonus

+-------------+------+
| Column Name | Type |
+-------------+------+
| empId       | int  |
| bonus       | int  |
+-------------+------+

Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.
Đáp án
select
  e.name,
  b.bonus
from
  Employee e
  left join Bonus b on e.empId = b.empId
where
  bonus < 1000
  or bonus is null;

Câu 1280. Students and Examinations

Table: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+

Table: Subjects
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+

Table: Examinations
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+

Write a solution to find the number of times each student attended each exam.
Đáp án
-- Write your PostgreSQL query statement below
select
  cj.student_id,
  cj.student_name,
  cj.subject_name,
  coalesce(count(e.subject_name)) attended_exams
from
  (
    select
      *
    from
      Students
      cross join Subjects
  ) cj
  left join Examinations e on cj.student_id = e.student_id
  and cj.subject_name = e.subject_name
group by
  1,
  2,
  3

Câu 570. Managers with at Least 5 Direct Reports

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| department  | varchar |
| managerId   | int     |
+-------------+---------+

Write a solution to find managers with at least five direct reports.
Đáp án
select
  name
from
  Employee e
  join (
    select
      managerId,
      count(distinct id) as c
    from
      Employee
    where
      managerId is not null
      and id != managerId
    group by
      1
  ) agg on agg.managerId = e.id
  and agg.c >= 5

Câu 1934. Confirmation Rate

Table: Signups
+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+

Table: Confirmations
+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+

Write a solution to find the confirmation rate of each user.
Đáp án
select
  s.user_id,
  coalesce(round(r, 2), 0.00) as confirmation_rate
from
  Signups s
  left join (
    select
      user_id,
      (
        count(time_stamp) filter (
          where
            action = 'confirmed'
        )
      ) / count(time_stamp)::numeric as r
    from
      Confirmations
    group by
      1
  ) rts on s.user_id = rts.user_id