Cho hai bảng dữ liệu như sau:

Bảng: Signups

+----------------+----------+
| Tên cột        | Kiểu     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+

user_id là khóa chính. Mỗi hàng ghi lại thời điểm đăng ký của một người dùng.

Bảng: Confirmations

+----------------+----------+
| Tên cột        | Kiểu     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+

(user_id, time_stamp) là khóa chính. user_id là khóa ngoại tham chiếu đến bảng Signups. action là kiểu ENUM với hai giá trị: 'confirmed' hoặc 'timeout'. Mỗi hàng ghi lại một yêu cầu xác nhận của người dùng tại một thời điểm nhất định, với kết quả là được xác nhận hoặc hết hạn.

Yêu cầu: Tính tỷ lệ xác nhận cho mỗi người dùng. Tỷ lệ xác nhận được định nghĩa là số lượng yêu cầu có action = 'confirmed' chia cho tổng số yêu cầu. Nếu người dùng không có yêu cầu nào, tỷ lệ xác nhận là 0. Làm tròn kết quả đến 2 chữ số thập phân. Kết quả có thể trả về theo thứ tự bất kỳ.

Ví dụ:

Dữ liệu đầu vào:

Signups:
+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 3       | 2020-03-21 10:16:13 |
| 7       | 2020-01-04 13:57:59 |
| 2       | 2020-07-29 23:09:44 |
| 6       | 2020-12-09 10:39:37 |
+---------+---------------------+

Confirmations:
+---------+---------------------+-----------+
| user_id | time_stamp          | action    |
+---------+---------------------+-----------+
| 3       | 2021-01-06 03:30:46 | timeout   |
| 3       | 2021-07-14 14:00:00 | timeout   |
| 7       | 2021-06-12 11:57:29 | confirmed |
| 7       | 2021-06-13 12:58:28 | confirmed |
| 7       | 2021-06-14 13:59:27 | confirmed |
| 2       | 2021-01-22 00:00:00 | confirmed |
| 2       | 2021-02-28 23:59:59 | timeout   |
+---------+---------------------+-----------+

Kết quả mong đợi:

+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6       | 0.00              |
| 3       | 0.00              |
| 7       | 1.00              |
| 2       | 0.50              |
+---------+-------------------+

Phân tích:

• Người dùng 6: không có yêu cầu nào → tỷ lệ = 0
• Người dùng 3: 2 yêu cầu, cả hai đều timeout → tỷ lệ = 0
• Người dùng 7: 3 yêu cầu, tất cả đều confirmed → tỷ lệ = 1
• Người dùng 2: 2 yêu cầu, 1 confirmed, 1 timeout → tỷ lệ = 0.5

Giải pháp bằng SQL:

Sử dụng hàm AVG() kết hợp với biểu thức điều kiện để tính tỷ lệ. Hàm COALESCE() (hoặc IFNULL() trong MySQL) để xử lý trường hợp không có yêu cầu nào, trả về 0. Hàm ROUND() để làm tròn đến 2 chữ số thập phân.

SELECT 
    s.user_id,
    ROUND(COALESCE(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;

Giải thích truy vấn:

• LEFT JOIN đảm bảo tất cả người dùng từ bảng Signups đều xuất hiện, kể cả những người không có yêu cầu nào.
• AVG(c.action = 'confirmed'): Trong SQL, c.action = 'confirmed' trả về 1 nếu đúng, 0 nếu sai. Vì vậy, AVG sẽ tính trung bình cộng, tương đương với tỷ lệ.
• COALESCE(..., 0): Nếu người dùng không có bản ghi nào trong Confirmations, kết quả AVG sẽ là NULL, và COALESCE thay thế bằng 0.
• ROUND(..., 2): Làm tròn đến 2 chữ số thập phân.