💡
List câu hỏi này tập trung vào các hàm tổng hợp sau khi sử dụng GROUP BY , nhưng cũng yêu cầu sử dụng thành thạo các kỹ thuật JOIN để chuẩn hoá dữ liệu khi cần thiết.

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

  1. JOIN và Subquery vẫn là yêu cầu bắt buộc và cơ bản nhất để manipulate dữ trong SQL, nên cần phải được thực hành thành thạo trước
  2. Các aggregation function phổ biến được sử dụng cùng với GROUP BY như min, max, sum, avg , etc. tuỳ theo yêu cầu của đề bài
  3. Trong khi sử dụng các hàm aggregation trong GROUP BY, ta có thể sử dụng filter trực tiếp, điều này cực kỳ hữu dụng trong quá trình đưa dữ liệu về kết quả cần thiết mà không làm cho câu truy vấn dài thêm với các bước lọc không cần thiết.
  4. Các câu trong phần này có nhiều cách giải khác nhau, mình chưa muốn sử dụng các kỹ thuật phức tạp như PARTITION OVER nếu không cần thiết vì sẽ làm câu lệnh khó hiểu cho người mới.

Câu 620. Not Boring Movies

Table: Cinema
+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| id             | int      |
| movie          | varchar  |
| description    | varchar  |
| rating         | float    |
+----------------+----------+

Write a solution to report the movies with an odd-numbered ID and a description that is not "boring".
Đáp án
select
  *
from
  Cinema
where
  description != 'boring'
  and id % 2 != 0
order by
  rating desc;

Câu 1251. Average Selling Price

Table: Prices
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+

Table: UnitsSold
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+ 

Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.
Đáp án
select
  p.product_id,
  case
    when sum(coalesce(u.units, 0)) = 0 then 0.00
    else round(sum(p.price * coalesce(u.units, 0) * 1.0) / sum(coalesce(u.units, 0)),2)
  end as average_price
from
  Prices p
  left join UnitsSold u on p.product_id = u.product_id
  and p.start_date <= u.purchase_date
  and p.end_date >= u.purchase_date
group by
  1

Câu 1075. Project Employees I

Table: Project
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
 

Table: Employee
+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.
Đáp án
SELECT p.project_id,
       round(avg(e.experience_years), 2) average_years
FROM Project p
JOIN Employee e ON p.employee_id = e.employee_id
GROUP BY 1

Câu 1633. Percentage of Users Attended a Contest

Table: Users
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| user_name   | varchar |
+-------------+---------+
 

Table: Register
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| contest_id  | int     |
| user_id     | int     |
+-------------+---------+

Write a solution to find the percentage of the users registered in each contest rounded to two decimals.
Đáp án
SELECT contest_id,
       round(count(DISTINCT user_id)::numeric/tmp.tt * 100, 2) AS percentage
FROM Register r
CROSS JOIN
  (SELECT count(*) AS tt
   FROM Users) tmp
GROUP BY 1,
         tmp.tt
ORDER BY 2 DESC,
         1 ASC

Câu 1211. Queries Quality and Percentage

Table: Queries
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+

Write a solution to find each query_name, the quality and poor_query_percentage.
Đáp án
select
  q.query_name,
  round(sum(q.rating :: numeric / q.position) / tmp.tt_result, 2) as quality,
  round((count(q.result) filter (where rating < 3)) :: numeric * 100 / tmp.tt_result, 2) as poor_query_percentage
from
  Queries q
  join (
    select
      query_name,
      count(result) tt_result
    from Queries group by 1
  ) tmp on q.query_name = tmp.query_name
group by
  1,
  tmp.tt_result

Câu 1193. Monthly Transactions I

Table: Transactions
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+

Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.
Đáp án
Select
  TO_CHAR(date_trunc('month', trans_date), 'YYYY-MM') as month,
  country,
  count(trans_date) as trans_count,
  count(trans_date) filter(where state = 'approved') as approved_count,
  sum(amount) as trans_total_amount,
  coalesce((sum(amount) filter(where state = 'approved')), 0) as approved_total_amount
from
  Transactions
group by
  1,
  2

Câu 1174. Immediate Food Delivery II

Table: Delivery
+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+

Write a solution to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.
Đáp án
select
  round((count(distinct d.customer_id) filter(where d.order_date = d.customer_pref_delivery_date)) :: numeric / 
         count(distinct d.customer_id) * 100, 2) 
  as immediate_percentage
from
  Delivery d
  join (
    select
      customer_id,
      min(order_date) as first_order
    from
      Delivery
    group by
      1
  ) f on d.customer_id = f.customer_id
  and d.order_date = f.first_order

Câu 550. Game Play Analysis IV

Table: Activity
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+

Write a solution to report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.
Đáp án
select
  case
    when count(distinct a.player_id) = 0 then 0
    else round(count(distinct a.player_id) :: numeric / min(tmp.tt), 2)
  end as fraction
from
  Activity a
  join (
    select
      player_id,
      min(event_date) as first_date
    from
      Activity
    group by
      1
  ) f on a.player_id = f.player_id
  and a.event_date = f.first_date + '1day' :: interval
  cross join (
    select
      count(distinct player_id) as tt
    from
      Activity
  ) tmp