Tiếp nối với case study ở phần 1, hãy cùng UniTrain khám phá một case study mới với lĩnh vực ngân hàng nhé!
Tình huống:
Một làn sóng đổi mới đang diễn ra trong lĩnh vực tài chính với sự ra đời của Ngân hàng Neo – các ngân hàng kỹ thuật số hoàn toàn mới. Danny đã nảy ra ý tưởng kết hợp giữa Ngân hàng Neo, tiền điện tử với thế giới dữ liệu. Và thế là Data Bank ra đời!
Data Bank hoạt động giống như bất kỳ Ngân hàng kỹ thuật số nào khác, nhưng không chỉ dừng lại ở các hoạt động ngân hàng truyền thống. Data Bank còn cung cấp nền tảng lưu trữ dữ liệu phân tán, với mức độ bảo mật hàng đầu thế giới!
Câu hỏi:
- Mỗi loại giao dịch đã được sử dụng bao nhiêu lần và với tổng số tiền được giao dịch là bao nhiêu?
- Giá trị trung bình của số lần gửi tiền và số tiền được gửi của mỗi khách hàng là bao nhiêu?
- Đối với mỗi tháng, có bao nhiêu khách hàng của Data Bank thực hiện nhiều hơn 1 giao dịch gửi tiền và đồng thời thực hiện ít nhất 1 giao dịch mua hoặc rút tiền trong cùng tháng đó?
Bạn có thể thực hành với bộ dữ liệu tại: https://8weeksqlchallenge.com/case-study-4/
1. Mỗi hình thức giao dịch đã được sử dụng bao nhiêu lần và với tổng số tiền được giao dịch là bao nhiêu?
Chúng ta sẽ tính số lượng (count) duy nhất và tổng số tiền (sum) theo từng loại giao dịch (txn_type) của khách hàng.
WITH customer_transactions AS(
SELECT * FROM data_bank.customer_transactions)
SELECT
txn_type,
COUNT(*) AS unique_count,
SUM(txn_amount) AS total_amount
FROM customer_transactions
GROUP BY txn_type;
2. Giá trị trung bình của số lần gửi tiền và số tiền được gửi của mỗi khách hàng là bao nhiêu?
Bước 1: Tạo bảng tạm thời ‘customerDeposit’:
– Lọc lấy các giao dịch ‘deposit’ từ bảng ‘customer_transactions’.
– Đối với mỗi khách hàng, tính toán tổng số lần gửi tiền và tổng số tiền gửi.
Bước 2: Tính toán các giá trị trung bình:
– Tính trung bình số lần gửi tiền của tất cả khách hàng trong bảng ‘customerDeposit’, lưu kết quả vào cột ‘avg_dep_count’.
– Tính trung bình số tiền gửi của tất cả khách hàng trong bảng ‘customerDeposit’, lưu kết quả vào cột ‘avg_dep_amount’.
WITH customerDeposit AS (
SELECT
customer_id,
txn_type,
COUNT(*) AS dep_count,
SUM(txn_amount) AS dep_amount
FROM data_bank.customer_transactions
WHERE txn_type = ‘deposit’
GROUP BY customer_id, txn_type
)
SELECT
AVG(dep_count) AS avg_dep_count,
AVG(dep_amount) AS avg_dep_amount
FROM customerDeposit;
3. Đối với mỗi tháng, có bao nhiêu khách hàng của Data Bank thực hiện nhiều hơn 1 giao dịch gửi tiền và đồng thời thực hiện ít nhất 1 giao dịch mua hoặc rút tiền trong cùng tháng đó?
Bước 1: Tạo bảng tạm thời cte_transaction để tính
– Số lần gửi tiền (deposit_count)
– Số lần mua (purchase_count)
– Số lần rút tiền (withdrawal_count)
Bước 2: Đếm số khách hàng thỏa mãn yêu cầu
WITH cte_transaction AS (
SELECT
customer_id,
MONTH(txn_date) AS months,
SUM(CASE WHEN txn_type = ‘deposit’ THEN 1 ELSE 0 END) AS deposit_count,
SUM(CASE WHEN txn_type = ‘purchase’ THEN 1 ELSE 0 END) AS purchase_count,
SUM(CASE WHEN txn_type = ‘withdrawal’ THEN 1 ELSE 0 END) AS withdrawal_count
FROM data_bank.customer_transactions
GROUP BY customer_id, MONTH(txn_date)
)
SELECT
months,
COUNT(customer_id) AS customer_count
FROM cte_transaction
WHERE deposit_count > 1
AND (purchase_count = 1 OR withdrawal_count = 1)
GROUP BY months;
Xem thêm: