Đề 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
Kết quả cần trả ra sẽ như sau:
| 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 |
Đề 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
Kết quả cần trả ra sẽ như sau:
| 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 |
Đề 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ả cần trả ra sẽ như sau:
| 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 |