Tiếp nối với phần 1 về dữ liệu khách hàng của một quán ăn, chúng ta hãy cùng trả lời thêm một vài câu hỏi để tìm ra xu hướng và hành vi mua hàng nhé!
Câu hỏi 6: Món ăn nào được khách hàng mua đầu tiên sau khi họ trở thành thành viên?
Để xác định món ăn được mua đầu tiên bởi khách hàng sau khi họ trở thành thành viên, chúng ta phân tích dữ liệu bán hàng, thông tin thành viên và ngày đặt hàng.
Bước 1: Tạo bảng Rank:
– Nối ba bảng: Sales (s), Menu (m), và Members (mem) dựa trên các ID tương ứng.
– Giữ lại các bản ghi bán hàng có ngày đặt hàng lớn hơn hoặc bằng ngày gia nhập thành viên (s.order_date >= mem.join_date).
– Chọn customer_id, product_name, và sử dụng DENSE_RANK() để đánh thứ hạng (Rank) cho từng lần mua hàng theo thứ tự ngày đặt hàng trong mỗi nhóm khách hàng (xếp hạng theo ngày đặt hàng của từng khách hàng).
Bước 2: Lấy món ăn đầu tiên:
– Chọn tất cả (*) từ bảng Rank vừa tạo.
– Lọc ra các dòng có Rank = 1 (chỉ lấy lần đầu tiên – rank 1).
Kết quả: Bảng kết quả sẽ hiển thị customer_id và product_name của món ăn đầu tiên mà mỗi khách hàng mua sau khi họ trở thành thành viên.
WITH Rank AS
(
SELECT s.customer_id,
m.product_name,
DENSE_RANK() OVER (PARTITION BY s.Customer_id ORDER BY s.Order_date) AS Rank
FROM dannys_diner.Sales s
JOIN dannys_diner.Menu m
ON m.product_id = s.product_id
JOIN dannys_diner.Members mem
ON mem.Customer_id = s.customer_id
Where s.order_date >= mem.join_date
)
SELECT *
FROM Rank
WHERE Rank = 1
Câu hỏi 7: Món ăn nào được khách hàng mua ngay trước khi họ trở thành thành viên?
Để xác định món ăn được mua ngay trước khi khách hàng trở thành thành viên, chúng ta phân tích dữ liệu bán hàng, thông tin thành viên và ngày đặt hàng. Bằng cách nối các bảng sales, menu và members dựa trên ID tương ứng của chúng, chúng ta có thể liên kết thông tin khách hàng, sản phẩm và thành viên với nhau.
Câu lệnh WHERE lọc các bản ghi bán hàng để chỉ bao gồm các đơn hàng được thực hiện trước ngày gia nhập của khách hàng.
Kết quả cuối cùng bao gồm customer_id, tên món ăn tương ứng và ngày đặt hàng của lần mua ngay trước khi họ trở thành thành viên. Thông tin này giúp chúng ta hiểu được sở thích và hành vi mua sắm của khách hàng ngay trước khi tham gia chương trình thành viên.
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 DESC) AS Rank
FROM dannys_diner.Sales s
Join dannys_diner.Menu m
ON m.product_id = s.product_id
JOIN dannys_diner.Members mem
ON mem.Customer_id = s.customer_id
Where s.order_date < mem.join_date
)
SELECT customer_ID, Product_name,nOrder_date
FROM Rank
WHERE Rank = 1;
Câu hỏi 8: Tổng số mặt hàng và số tiền chi tiêu của mỗi thành viên trước khi họ trở thành thành viên là bao nhiêu?
Để tính toán tổng số mặt hàng và tổng số tiền chi tiêu của mỗi thành viên trước khi họ trở thành thành viên, chúng ta cần phân tích dữ liệu bán hàng, thông tin thành viên và ngày đặt hàng. Thực hiện truy vấn liên kết các bảng bán hàng, thành viên và thực đơn dựa trên ID tương ứng của chúng. Điều kiện liên kết bao gồm hai yếu tố: khớp ID khách hàng và đảm bảo ngày đặt hàng sớm hơn ngày gia nhập của mỗi thành viên.
Tính toán: Sử dụng hàm COUNT(*) để tính tổng số mặt hàng được mua bởi mỗi thành viên. Bên cạnh đó, SUM(menu.price) được sử dụng để tính tổng số tiền chi tiêu cho mỗi thành viên bằng cách cộng tổng giá tiền từ bảng thực đơn được liên kết.
Nhóm dữ liệu: Dữ liệu sau đó được nhóm theo ID khách hàng sử dụng GROUP BY, đảm bảo chúng ta phân tích từng thành viên riêng lẻ. Câu lệnh ORDER BY sắp xếp kết quả theo ID khách hàng để dễ tham khảo.
Kết quả: Kết quả cuối cùng bao gồm ID khách hàng, tổng số mặt hàng đã mua và tổng số tiền tương ứng đã chi tiêu cho mỗi thành viên trước khi họ trở thành thành viên. Thông tin này cung cấp cái nhìn sâu sắc về hành vi mua hàng và chi tiêu của các khách haG trước khi họ tham gia chương trình thành viên.
SELECT sales.customer_id, COUNT(*) AS “Total Items”, SUM(menu.price) AS “Total Amount Spent”
FROM dannys_diner.sales
INNER JOIN dannys_diner.members
ON sales.customer_id = members.customer_id AND sales.order_date < members.join_date
INNER JOIN dannys_diner.menu ON sales.product_id = menu.product_id
GROUP BY sales.customer_id
ORDER BY sales.customer_id;
Câu hỏi 9: Mỗi khách hàng có bao nhiêu điểm tích lũy?
Giả sử mỗi $1 tương ứng với 10 điểm và nếu khách hàng mua sushi thì sẽ được nhân đôi điểm. Vậy tổng điểm tích lũy của mỗi khách hàng là bao nhiêu?
Để tính toán số điểm của mỗi khách hàng dựa trên chi tiêu và hệ số nhân điểm, chúng ta phân tích dữ liệu bán hàng và nối bảng sales với bảng menu dựa trên ID tương ứng. Truy vấn nhóm dữ liệu theo customer_id bằng GROUP BY để đảm bảo tính toán điểm cho từng khách hàng riêng lẻ.
Hàm SUM được sử dụng để tính tổng điểm cho mỗi khách hàng. Câu lệnh CASE kiểm tra xem product_name có phải là ‘sushi’ hay không. Nếu đúng, nó tính điểm bằng 2 lần giá tiền (tính đến hệ số nhân đôi điểm cho sushi). Ngược lại, nó tính điểm bằng giá thông thường nhân 10.
Kết quả bao gồm customer_id và tổng điểm tương ứng (total_points) cho mỗi khách hàng.
WITH Points AS
(
SELECT *, CASE WHEN product_id = 1 THEN price*20
ELSE price*10
END AS Points
FROM dannys_diner.Menu
)
SELECT S.customer_id, SUM(P.points) AS Points
FROM dannys_diner.Sales s
JOIN Points p
ON p.product_id = s.product_id
GROUP BY s.customer_id;
Câu hỏi 10: Khách hàng A và B có bao nhiêu điểm tích lũy vào cuối tháng 1?
Trong tuần đầu tiên sau khi tham gia chương trình (bao gồm cả ngày gia nhập), khách hàng nhận được gấp đôi điểm cho tất cả các món ăn, không chỉ sushi. Vậy tổng điểm tích lũy của khách hàng A và B là bao nhiêu vào cuối tháng 1?
Để tính toán số điểm cho khách hàng A và B vào cuối tháng 1, có tính đến hệ số nhân đôi điểm trong tuần đầu tiên sau khi gia nhập, chúng ta phân tích dữ liệu bán hàng, thông tin thành viên và ngày đặt hàng.
Câu lệnh CASE được sử dụng để kiểm tra hai điều kiện:
– Kiểm tra xem order_date có nằm trong tuần đầu tiên sau ngày gia nhập của khách hàng hay không, bao gồm cả ngày gia nhập. Nếu đúng, nó tính điểm bằng 2 lần giá tiền nhân 10 (tính đến hệ số nhân đôi điểm cho tuần đầu tiên).
– Kiểm tra xem product_name có phải là ‘sushi’ hay không. Nếu đúng, nó cũng tính điểm bằng 2 lần giá tiền nhân 10 (tính đến hệ số nhân đôi điểm cho sushi). Đối với các mặt hàng khác, nó tính điểm bằng giá thông thường nhân với 10.
Câu lệnh WHERE lọc các bản ghi bán hàng để chỉ bao gồm các đơn đặt hàng được thực hiện vào tháng 1 năm 2021, cụ thể là ngày đầu tiên của tháng 1.
Dữ liệu sau đó được nhóm theo customer_id bằng GROUP BY để đảm bảo tính toán điểm cho từng khách hàng riêng lẻ.
Kết quả bao gồm customer_id và tổng điểm tương ứng (total_points) cho khách hàng A và khách hàng B vào cuối tháng 1, tính đến hệ số nhân đôi điểm trong tuần đầu tiên sau khi gia nhập.
WITH dates AS
(
SELECT *, (join_date + INTERVAL ‘6 DAY’) AS valid_date,
date_trunc(‘month’, join_date) + INTERVAL ‘1 month – 1 day’ AS last_date
FROM dannys_diner.members
)
SELECT s.Customer_id,
SUM(
CASE
WHEN m.product_ID = 1 THEN m.price*20
WHEN s.order_date between d.join_date and d.valid_date THEN m.price*20
ELSE m.price*10
END
) AS Points
FROM Dates d
JOIN dannys_diner.Sales s
ON d.customer_id = s.customer_id
JOIN dannys_diner.Menu m
On m.product_id = s.product_id
WHERE s.order_date < d.last_date
GROUP BY s.customer_id;
Xem thêm:
