Đề thi này được soạn dựa trên các đề thi tuyển Data Analyst, BI Analyst và Data Engineer thực tế và được thiết kế để kiểm tra khả năng sử dụng SQL để trích xuất, xử lý và phân tích dữ liệu của ứng viên cho các vị trí ở level junior.
Truy cập dữ liệu sử dụng để thực hành cho lesson này theo 1 trong các cách sau:
Download và cài đặt mysql (google hướng dẫn trên mạng, nên cài đặt thêm MySQL Workbench để viết lệnh và sử dụng MySQL). Bạn vẫn hoàn toàn có thể sử dụng 1 hệ quản trị cơ sở dữ liệu khác (VD: SQL Server, PostgreSQL, …) tuy nhiên có thể sẽ có 1 vài điểm khác biệt nho nhỏ (nhưng không ảnh hưởng trọng yếu tới kiến thức và thực hành của bài học này).
Truy cập link sqlfiddle có ở mỗi câu hỏi và phần bài tập thực hành để viết lệnh SQL mà không cần cài đặt
Hãy phân tích và trả lời các câu hỏi sau đây. Câu trả lời cần bao gồm SQL query và kết quả trả ra khi chạy query đó.
Đề bài: Một cửa hàng thời trang có 5 nhân viên sales. Trong tháng vừa qua doanh số bán hàng của họ như sau:
| sales_person_id | product_category | revenue |
|---|---|---|
| 1 | Casual clothes | 24 |
| 1 | Accessories | 32 |
| 1 | High-end clothes | 19 |
| 2 | High-end clothes | 92 |
| 2 | Accessories | 28 |
| 2 | Casual clothes | 33 |
| 3 | Accessories | 32 |
| 3 | Casual clothes | 50 |
| 4 | High-end clothes | 44 |
| 4 | Accessories | 30 |
| 5 | Casual clothes | 13 |
| 5 | Accessories | 11 |
Yêu cầu: Chủ cửa hàng cần biết danh sách top 2 nhân viên bán hàng có doanh thu cao nhất cho từng dòng sản phẩm để khen thưởng, trả hoa hồng. Hãy viết câu lệnh SQL để trả ra danh sách này, các dữ liệu cần lấy ra bao gồm: ID của nhân viên sales, dòng sản phẩm, doanh số bán hàng, xếp hạng (số 1 hay số 2)
sqlfiddle link: http://sqlfiddle.com/#!9/c52743/1/0
Đáp án:
-- create sales table
DROP TABLE IF EXISTS tbl_sales;
CREATE TABLE tbl_sales (
sales_person_id INT,
product_category VARCHAR(50),
revenue INT
);
INSERT INTO tbl_sales
VALUES
(1, "Casual clothes", 24),
(1, "Accessories", 32),
(1, "High-end clothes", 19),
(2, "High-end clothes", 92),
(2, "Accessories", 28),
(2, "Casual clothes", 33),
(3, "Accessories", 32),
(3, "Casual clothes", 50),
(4, "High-end clothes", 44),
(4, "Accessories", 30),
(5, "Casual clothes", 13),
(5, "Accessories", 11);
-- get top 2 best sales person by revenue and product category
WITH ranking AS(
SELECT
sales_person_id,
product_category,
revenue,
DENSE_RANK() OVER(PARTITION BY product_category ORDER BY revenue DESC) AS revenue_rank
FROM tbl_sales
)
SELECT
*
FROM ranking
WHERE revenue_rank <= 2
ORDER BY product_category, revenue_rank;
| sales_person_id | product_category | revenue | revenue_rank |
|---|---|---|---|
| 1 | Accessories | 32 | 1 |
| 3 | Accessories | 32 | 1 |
| 4 | Accessories | 30 | 2 |
| 3 | Casual clothes | 50 | 1 |
| 2 | Casual clothes | 33 | 2 |
| 2 | High-end clothes | 92 | 1 |
| 4 | High-end clothes | 44 | 2 |
Kinh nghiệm: Khi đi thi tuyển hay phỏng vấn, nếu bài toán có liên quan tới việc sắp xếp thứ tự, hãy nghĩ tới sử dụng các window functions. Phổ biến nhất thường là ROW_NUMBER() và DENSE_RANK().
Đề bài: Ta có dữ liệu các giao dịch chuyển tiền của 1 ngân hàng như sau:
| trans_id | account_id | deposit_amt | withdrawal_amt | trans_date | trans_status |
|---|---|---|---|---|---|
| 1 | 15 | 50000 | NULL | 2023-01-18 | 1 |
| 2 | 16 | 450000 | NULL | 2023-01-18 | 1 |
| 3 | 15 | NULL | 1200000 | 2023-01-18 | 1 |
| 4 | 15 | 20000 | NULL | 2023-01-18 | 1 |
| 5 | 19 | 4850000 | NULL | 2023-01-18 | 1 |
| 6 | 20 | 100000 | NULL | 2023-01-18 | 2 |
| 7 | 21 | NULL | 320000 | 2023-01-19 | 1 |
| 8 | 22 | 55000 | NULL | 2023-01-19 | 1 |
| 9 | 22 | NULL | 90000 | 2023-01-20 | 2 |
| 10 | 24 | NULL | 325000 | 2023-01-20 | 1 |
| 11 | 25 | NULL | 28000 | 2023-01-20 | 1 |
| 12 | 26 | NULL | 200000 | 2023-01-20 | 1 |
| 13 | 26 | NULL | 38000 | 2023-01-20 | 2 |
| 14 | 26 | NULL | 37500 | 2023-01-20 | 2 |
deposit_amt: số tiền chuyển vào tài khoản ngân hàng của khách hàng
withdrawal_amt: số tiền chuyển ra khỏi tài khoản ngân hàng của khách hàng
trans_status: 1 = giao dịch thành công, 2 = giao dịch thất bại
Yêu cầu: Viết SQL query để trả ra bảng kết quả gồm các thông tin sau:
tài khoản ngân hàng (account_id)
Tổng giá trị ròng của các khoản tiền chuyển ra, vào tài khoản (bằng tổng tiền chuyển vào trừ đi tổng tiền chuyển ra khỏi tài khoản)
Số lượng các giao dịch chuyển khoản tiền đi khỏi tài khoản mà thất bại
Tổng giá trị các giao dịch chuyển khoản tiền đi khỏi tài khoản mà thất bại
sqlfiddle link: http://sqlfiddle.com/#!9/cd609f6/1
Đáp án:
-- create transactions table
DROP TABLE IF EXISTS tbl_transactions;
CREATE TABLE tbl_transactions (
trans_id INT,
account_id INT,
deposit_amt FLOAT,
withdrawal_amt FLOAT,
trans_date DATE,
trans_status INT
);
INSERT INTO tbl_transactions
VALUES
(1, 15, 50000, null, '2023-01-18', 1),
(2, 16, 450000, null, '2023-01-18', 1),
(3, 15, null, 1200000, '2023-01-18', 1),
(4, 15, 20000, null, '2023-01-18', 1),
(5, 19, 4850000, null, '2023-01-18', 1),
(6, 20, 100000, null, '2023-01-18', 2),
(7, 21, null, 320000, '2023-01-19', 1),
(8, 22, 55000, null, '2023-01-19', 1),
(9, 22, null, 90000, '2023-01-20', 2),
(10, 24, null, 325000, '2023-01-20', 1),
(11, 25, null, 28000, '2023-01-20', 1),
(12, 26, null, 200000, '2023-01-20', 1),
(13, 26, null, 38000, '2023-01-20', 2),
(14, 26, null, 37500, '2023-01-20', 2)
;
-- answer:
SELECT
account_id,
SUM(IF(trans_status = 1, COALESCE(deposit_amt, 0), 0)) - SUM(IF(trans_status = 1, COALESCE(withdrawal_amt, 0), 0)) AS net_amt,
-- instead of using IF, we can also use CASE WHEN like below
-- SUM(CASE WHEN trans_status = 1 THEN COALESCE(deposit_amt, 0) ELSE 0 END) - SUM(CASE WHEN trans_status = 1 THEN COALESCE(withdrawal_amt, 0) ELSE 0 END) AS net_amt_2,
SUM(IF(withdrawal_amt IS NOT NULL AND trans_status = 2, 1, 0)) AS no_failed_withdrawal_trans,
SUM(IF(withdrawal_amt IS NOT NULL AND trans_status = 2, withdrawal_amt, 0)) AS failed_withdrawal_trans_amt
FROM tbl_transactions
GROUP BY account_id;| account_id | net_amt | no_failed_withdrawal_trans | failed_withdrawal_trans_amt |
|---|---|---|---|
| 15 | -1130000 | 0 | 0 |
| 16 | 450000 | 0 | 0 |
| 19 | 4850000 | 0 | 0 |
| 20 | 0 | 0 | 0 |
| 21 | -320000 | 0 | 0 |
| 22 | 55000 | 1 | 90000 |
| 24 | -325000 | 0 | 0 |
| 25 | -28000 | 0 | 0 |
| 26 | -200000 | 2 | 75500 |
Kinh nghiệm: luôn lưu ý tới việc xử lý giá trị NULL khi viết câu lệnh SQL
Đề bài: Có bảng user_logins như dưới đây lưu dữ liệu các user login vào hệ thống của 1 công ty. Để đơn giản hoá bài toán, dữ liệu đã được xử lý thành dạng gồm 2 cột:
user_id: ID trên hệ thống của người dùng
login_date: ngày user đăng nhập vào hệ thống (nếu user đăng nhập nhiều lần trong 1 ngày thì cũng sẽ chỉ có 1 bản ghi xuất hiện trong bảng này)
DROP TABLE IF EXISTS user_logins;
CREATE TABLE user_logins(
user_id INT,
login_date DATE
);
INSERT INTO user_logins
VALUES
(1, '2023-01-01'),
(1, '2023-01-03'),
(1, '2023-01-04'),
(1, '2023-01-05'),
(1, '2023-01-08'),
(1, '2023-01-12'),
(1, '2023-01-20'),
(1, '2023-01-21'),
(1, '2023-01-22'),
(1, '2023-01-23'),
(2, '2023-01-05'),
(2, '2023-01-06'),
(2, '2023-01-09'),
(2, '2023-01-10'),
(2, '2023-01-16'),
(2, '2023-01-28'),
(2, '2023-01-29'),
(2, '2023-01-30')
;
SELECT * FROM user_logins;| user_id | login_date |
|---|---|
| 1 | 2023-01-01 |
| 1 | 2023-01-03 |
| 1 | 2023-01-04 |
| 1 | 2023-01-05 |
| 1 | 2023-01-08 |
| 1 | 2023-01-12 |
| 1 | 2023-01-20 |
| 1 | 2023-01-21 |
| 1 | 2023-01-22 |
| 1 | 2023-01-23 |
| 2 | 2023-01-05 |
| 2 | 2023-01-06 |
| 2 | 2023-01-09 |
| 2 | 2023-01-10 |
| 2 | 2023-01-16 |
| 2 | 2023-01-28 |
| 2 | 2023-01-29 |
| 2 | 2023-01-30 |
sqlfiddle link: http://sqlfiddle.com/#!9/6721bb8/1
Yêu cầu: Viết câu lệnh SQL để với từng user, đánh dấu các ngày mà user đó login vào hệ thống liên tiếp. Kết quả trả ra cần giống như dưới đây:
| user_id | login_date | consecutive_batch |
|---|---|---|
| 1 | 2023-01-01 | 1 |
| 1 | 2023-01-03 | 2 |
| 1 | 2023-01-04 | 2 |
| 1 | 2023-01-05 | 2 |
| 1 | 2023-01-08 | 3 |
| 1 | 2023-01-12 | 4 |
| 1 | 2023-01-20 | 5 |
| 1 | 2023-01-21 | 5 |
| 1 | 2023-01-22 | 5 |
| 1 | 2023-01-23 | 5 |
| 2 | 2023-01-05 | 1 |
| 2 | 2023-01-06 | 1 |
| 2 | 2023-01-09 | 2 |
| 2 | 2023-01-10 | 2 |
| 2 | 2023-01-16 | 3 |
| 2 | 2023-01-28 | 4 |
| 2 | 2023-01-29 | 4 |
| 2 | 2023-01-30 | 4 |
Đáp án:
WITH ranked_login AS (
SELECT
user_id,
login_date,
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login_date
FROM
user_logins
)
SELECT
user_id,
login_date,
SUM(CASE WHEN login_date = DATE_ADD(prev_login_date, INTERVAL 1 DAY) THEN 0 ELSE 1 END)
OVER (PARTITION BY user_id ORDER BY login_date) AS consecutive_batch
FROM
ranked_login
ORDER BY
user_id, login_date;Giải thích code:
Ta sử dụng window function LAG() để tạo ra cột lưu ngày login date liền trước cho từng user và lưu kết quả vào 1 bảng CTE gọi là ranked_login
Từ kết quả ở bảng CTE ta dùng window function SUM() kết hợp với lệnh CASE để đánh dấu các nhóm ngày login liên tiếp (gọi là batch). Login như sau: kiểm tra với mỗi bản ghi dữ liệu, nếu ngày login_date = ngày prev_login_date + 1 thì có nghĩa là ngày login_date ở bản ghi đó là ngày liên tiếp với bản ghi liền trước, và ta đánh số 0. Các bản ghi đứng độc lập (không có bản ghi nào liên tiếp với nó cả) và các bản ghi đứng đầu tiên trong 1 nhóm liên tiếp (trong 1 batch) thì được đánh số 1. Sau đó ta dùng window function SUM() để tính tổng luỹ kế của các giá trị 0, 1 này cho từng bản ghi, sẽ ra được số thứ tự cho từng batch (cột consecutive batch). Để hiểu thêm cách hoạt động của SUM() khi sử dụng là 1 window function với PARTITION BY và ORDER BY, các bạn xem article này có giải thích rõ ràng và trực quan, xem kĩ phần “EXAMPLE 3” về “RUNNING TOTAL”: https://learnsql.com/blog/sum-over-partition-by/#:~:text=The%20SUM()%20function%20is,details%20of%20each%20individual%20row.
Kinh nghiệm: khi yêu cầu liên quan tới việc tìm các giá trị liền trước, liền sau, liên tiếp, v.v. thì hãy nghĩ tới việc sử dụng các window function LEAD() và LAG()