Việc thực hành giải case study với SQL đóng vai trò vô cùng quan trọng trong việc nâng cao kỹ năng và kiến thức về ngôn ngữ SQL. Nó mang lại nhiều lợi ích thiết thực, bao gồm:
– Giúp bạn áp dụng kiến thức lý thuyết vào thực tế một cách hiệu quả.
– Nâng cao khả năng tư duy logic và giải quyết vấn đề.
– Tăng cường khả năng cạnh tranh trên thị trường lao động.
Hãy cùng UniTrain khám phá ngay một case study thông qua bài viết này nhé!
Tình huống:
Danny là một người rất yêu thích đồ ăn Nhật Bản. Đầu năm 2021, anh quyết định khởi nghiệp bằng cách mở một nhà hàng nhỏ, bán 3 món ăn yêu thích của mình gồm: sushi, cà ri và ramen. Nhà hàng đã thu thập một số dữ liệu cơ bản trong vài tháng hoạt động nhưng không biết cách sử dụng chúng để điều hành việc kinh doanh.
Vấn đề:
Danny muốn sử dụng dữ liệu để trả lời một vài câu hỏi đơn giản về khách hàng, đặc biệt là về lịch sử ăn uống tại nhà hàng, số tiền họ đã chi tiêu và món ăn yêu thích của họ. Việc hiểu rõ hơn về khách hàng sẽ giúp anh ấy mang đến một trải nghiệm tốt hơn cho những khách hàng thân thiết.
Dựa trên những phân tích này, Danny sẽ quyết định xem có nên mở rộng chương trình khuyến mãi dành cho khách hàng thân thiết hay không. Do vấn đề bảo mật, Danny chỉ cung cấp cho bạn một mẫu dữ liệu về thông tin khách hàng – nhưng anh ấy hy vọng rằng các ví dụ này đủ để bạn viết các truy vấn SQL để giúp anh ấy trả lời các câu hỏi của mình!
Ba bảng dữ liệu bạn được cung cấp bao gồm:
– sales (Doanh số)
– menu (Thực đơn)
– members (Thành viên)
Bạn có thể thực hành với bộ dữ liệu tại: https://8weeksqlchallenge.com/case-study-1/
Các câu hỏi nghiên cứu tình huống
Câu hỏi 1: Tổng số tiền mỗi khách hàng đã chi tiêu tại nhà hàng là bao nhiêu?
Để tìm được tổng số tiền mà mỗi khách hàng đã chi tiêu tại nhà hàng, chúng ta cần phân tích dữ liệu về lịch sử bán hàng. Thông tin này rất quan trọng để đánh giá cách chi tiêu của khách hàng và xác định những khách hàng mang lại doanh thu cao nhất. Bằng cách kết hợp bảng sales và bảng menu thông qua LEFT JOIN, chúng ta có thể liên kết các bản ghi bán hàng với các món ăn tương ứng trong thực đơn. Điều này cho phép tính tổng số tiền mà mỗi khách hàng đã chi tiêu. Sau khi nhóm dữ liệu theo mã khách hàng (customer ID), chúng ta có thể sử dụng hàm SUM để tính tổng giá trị của các món ăn mà họ đã mua.
SELECT s.customer_id, SUM(m.price) AS “Total Amount Spent”
FROM dannys_diner.menu m
JOIN dannys_diner.sales s
ON m.product_id = s.product_id
GROUP BY s.customer_id
Câu hỏi 2: Mỗi khách hàng đã đến nhà hàng bao nhiêu lần?
Để xác định số ngày mỗi khách hàng đã đến nhà hàng, chúng ta cần phân tích dữ liệu bán hàng và đếm số ngày đặt hàng của từng người. Bằng cách sử dụng hàm COUNT(DISTINCT order_date), chúng ta có thể đếm số ngày đặt hàng riêng biệt (không trùng lặp) của mỗi khách hàng. Câu lệnh GROUP BY được sử dụng để nhóm các bản ghi bán hàng theo customer_id, đảm bảo rằng số lần đặt hàng được tính cho từng vị khách.
SELECT customer_id, COUNT(DISTINCT (order_date)) AS “Number of Days Visited”
FROM dannys_diner.sales
GROUP BY customer_id
Câu hỏi 3: Món ăn đầu tiên mỗi khách hàng gọi từ thực đơn là gì?
Để xác định món đầu tiên được mua từ thực đơn bởi mỗi khách hàng, chúng ta phân tích dữ liệu bán hàng và khớp nó với các món ăn tương ứng trong thực đơn.
Bước 1: Tạo bảng Rank
– Nối bảng Menu (m) với bảng Sales (s) dựa trên product_id để lấy thông tin khách hàng, tên món ăn (m.product_name) và ngày đặt hàng (s.order_date).
– Chuyển đổi order_date sang định dạng ‘YYYY-MM-DD’ (dễ nhìn hơn).
– Nhóm dữ liệu theo customer_id, product_name, và order_date. Điều này đảm bảo tính thứ tự theo cả ngày đặt hàng và tên món ăn cho từng khách hàng riêng lẻ (xếp hạng theo từng lần đặt hàng của khách).
– Sử dụng hàm DENSE_RANK() để đánh thứ hạng (rank) cho từng lần đặt hàng trong mỗi nhóm (xếp hạng theo cả ngày và tên món ăn).
Bước 2: Trả về danh sách khách hàng, món ăn đầu tiên và ngày đặt
– Chọn bảng Rank vừa tạo.
– Lọc ra các dòng có rank = 1. (Vì rank=1 là lần đặt hàng được xếp hạng 1 – tức là món ăn đầu tiên theo thứ tự ngày đặt, mã khách hàng và tên món).
– Trả về danh sách customer_id, product_name và order_date – thể hiện khách hàng, tên món ăn đầu tiên họ gọi và ngày đặt hàng đó.
WITH Rank AS
(
SELECT S.customer_id,
M.product_name,
TO_CHAR (S.order_date, ‘YYYY-MM-DD’) AS order_date,
DENSE_RANK() OVER (PARTITION BY s.Customer_ID
ORDER BY s.order_date, m.product_name) AS rank
FROM dannys_diner.menu m
JOIN dannys_diner.sales s
ON m.product_id = s.product_id
GROUP BY S.customer_id, M.product_name,S.order_date
)
SELECT Customer_id, product_name, order_date
FROM Rank
WHERE rank = 1
Câu hỏi 4: Món ăn nào được gọi nhiều nhất và đã được gọi bao nhiêu lần?
Để xác định món ăn được gọi nhiều nhất trên thực đơn và tổng số lần được gọi bởi tất cả khách hàng, chúng ta phân tích dữ liệu bán hàng và đếm số lần xuất hiện của từng sản phẩm. Bằng cách nối các bảng sales và menu dựa trên cột product_id, chúng ta có thể lấy tên sản phẩm liên quan đến mỗi lần mua hàng.
Truy vấn con nhóm dữ liệu theo product_id và tính tổng số lần xuất hiện của mỗi sản phẩm bằng cách sử dụng COUNT(*).
Truy vấn bên ngoài chọn tên sản phẩm cùng với số đếm tương ứng cho sản phẩm có tần suất xuất hiện nhiều nhất. Câu lệnh ORDER BY sắp xếp kết quả theo thứ tự giảm dần dựa trên số lần xuất hiện, đảm bảo rằng món ăn được gọi nhiều nhất xuất hiện ở đầu. Câu lệnh LIMIT 1 đảm bảo rằng chỉ có bản ghi đầu tiên được trả về, cung cấp cho chúng ta món ăn được gọi nhiều nhất và tổng số lần được gọi.
Thông tin này sẽ giúp chúng ta hiểu được mức độ phổ biến của các món ăn khác nhau trong thực đơn đối với khách hàng.
SELECT product_name AS “Most Purchased Item”, num_times AS “Number of Times Purchased”
FROM(
SELECT s.product_id, m.product_name, COUNT(*) as num_times
FROM dannys_diner.sales s
JOIN dannys_diner.menu m ON s.product_id = m.product_id
GROUP BY s.product_id, m.product_name
)
AS SUBQUERY
ORDER BY num_times DESC
LIMIT 1;
Câu hỏi 5: Món ăn nào được mỗi khách hàng mua nhiều nhất?
Để xác định món ăn được mua nhiều nhất bởi mỗi khách hàng, chúng ta phân tích dữ liệu bán hàng và xác định mặt hàng có số lượng mua cao nhất cho từng khách hàng. Bằng cách nối các bảng sales và menu dựa trên cột product_id, chúng ta có thể truy xuất tên sản phẩm của mỗi lần đặt hàng.
Truy vấn nhóm dữ liệu theo cả customer_id, product_id và product_name tính toán số lần mua cho mỗi tổ hợp bằng COUNT(*).
Câu lệnh HAVING đảm bảo rằng chỉ chọn các bản ghi có số lần mua khớp với số lần mua nhiều nhất của mỗi khách hàng. Truy vấn con bên trong tính toán số lần mua nhiều nhất của mỗi khách hàng. Kết quả bao gồm customer_id, mặt hàng được đặt nhiều nhất và số lần mua. Các bản ghi được sắp xếp theo customer_id để dễ tham khảo.
Thông tin này giúp chúng ta hiểu được sở thích của từng khách hàng và xác định những mặt hàng được ưa chuộng nhất trong số đó.
SELECT s.customer_id, m.product_name AS popular_item, COUNT(*) AS purchase_count
FROM dannys_diner.sales s
JOIN dannys_diner.menu m ON s.product_id = m.product_id
GROUP BY s.customer_id, s.product_id, m.product_name
HAVING COUNT (*) = (
SELECT MAX(purchase_count)
FROM (
SELECT customer_id, COUNT(*) AS purchase_count
FROM dannys_diner.sales
GROUP BY customer_id, product_id, m.product_name
) AS counts
WHERE s.customer_id = counts.customer_id
)
ORDER BY s.customer_id;
Xem thêm: