List 07 Tasks trong Project Reactivation Campaign Analysis -> Task 01 – Task 02 – Task 03 – Task 04 – Task 05 – Task 06 – Task 07
Các Task này mô phỏng lại 1 Case trong thực tế, các bạn có thể sử dụng để áp dụng vào công việc cũng như làm Portfolio Project dùng để phỏng vấn vị trí Data Analyst.
Task 01: Xác Định Dormant Users
Giả sử bạn đang là 1 Data Analyst cho một ứng dụng tập gym có 500.000 người dùng. Nhiều người dùng đã ngừng sử dụng app sau một thời gian. Đội Marketing thiết kế chiến dịch gửi 3 email liên tiếp để kéo họ quay lại.
Trước khi gửi email, bạn cần xác định những người dùng nào đã ngừng hoạt động từ 14 ngày trở lên. Đây là bước đầu tiên và quan trọng nhất của chiến dịch Reactivation – nếu xác định sai đối tượng, toàn bộ chiến dịch sẽ sai.
Bạn hãy viết câu truy vấn tìm tất cả người dùng đã không đăng nhập hoặc hoàn thành bất kỳ bài tập nào trong vòng 14 ngày gần nhất. Loại trừ những người đã hủy tài khoản hoặc đã bị chặn (banned).
WITH LastActivity AS (
SELECT
u.user_id, -- lay user_id tu bang users
u.full_name, -- lay full_name tu bang users
u.email, -- lay email tu bang users
u.city, -- lay city tu bang users
u.subscription_type, -- lay subscription_type tu bang users
u.account_status, -- lay account_status tu bang users
u.signup_date, -- lay signup_date tu bang users
ll.last_login_at, -- lay last_login_at tu bang user_login_logs
lw.last_workout_at, -- lay last_workout_at tu bang workout_completions
-- xac dinh ngay hoat dong cuoi cung giua dang nhap va tap luyen
CASE
-- neu ca 2 deu null => lay signup_date
WHEN ll.last_login_at IS NULL AND lw.last_workout_at IS NULL THEN u.signup_date
-- neu login >= workout (hoac workout null) => lay login
WHEN ll.last_login_at >= ISNULL(lw.last_workout_at, '1900-01-01') THEN ll.last_login_at
-- nguoc lai (workout > login) => lay workout
ELSE lw.last_workout_at
END AS last_activity_at
FROM users u
-- lay ngay dang nhap cuoi cung cua moi nguoi dung
-- dung group by de gom nhom theo user_id va lay ngay dang nhap lon nhat trong moi nhom
LEFT JOIN (SELECT user_id, MAX(login_at) as last_login_at FROM user_login_logs GROUP BY user_id) ll
ON u.user_id = ll.user_id
-- lay ngay tap luyen cuoi cung cua moi nguoi dung
-- dung group by de gom nhom theo user_id va lay ngay tap luyen lon nhat trong moi nhom
LEFT JOIN (SELECT user_id, MAX(completed_at) as last_workout_at FROM workout_completions GROUP BY user_id) lw
ON u.user_id = lw.user_id
-- dung left join de lay tat ca nguoi dung, ke ca nhung nguoi chua dang nhap hay tap luyen lan nao
-- cu phap: not in (list) -> lay cac gia tri khong thuoc list
WHERE u.account_status NOT IN ('deleted', 'banned') -- loai bo nguoi dung da bi xoa hoac cam
)
SELECT
user_id,
full_name,
email,
city,
subscription_type,
account_status,
signup_date,
last_login_at,
last_workout_at,
last_activity_at,
-- tinh so ngay khong hoat dong bang cach tinh hieu giua ngay hien tai va ngay hoat dong cuoi cung
-- cu phap: datediff(day, ngay_hoat_dong, getdate()) -> lay so ngay tu ngay_hoat_dong den ngay hien tai
DATEDIFF(DAY, last_activity_at, GETDATE()) AS days_inactive,
CASE
-- phan loai muc do dormancy dua tren so ngay khong hoat dong
-- cu phap: dateadd(day, -so_ngay, getdate()) -> lay ngay hien tai tru di so_ngay
WHEN last_activity_at <= DATEADD(DAY, -61, GETDATE()) THEN 'severe' -- neu khong hoat dong hon 60 ngay => severe
WHEN last_activity_at <= DATEADD(DAY, -31, GETDATE()) THEN 'moderate' -- neu khong hoat dong hon 30 ngay => moderate
ELSE 'mild' -- nguoc lai => mild
END AS dormancy_level
FROM LastActivity
WHERE last_activity_at <= DATEADD(DAY, -14, GETDATE())
ORDER BY last_activity_at DESC;Output Mẫu
| user_id | full_name | city | subscription_type | account_status | signup_date | last_login_at | last_workout_at | last_activity_at | days_inactive | dormancy_level | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 5 | Hoang Van Em | em.hoang@gmail.com | Ho Chi Minh | free | active | 2026-01-09 | 2026-02-07 23:01:52.970 | 2026-02-08 00:01:52.983 | 2026-02-08 00:01:52.983 | 15 | mild |
| 3 | Le Van Cuong | cuong.le@gmail.com | Da Nang | premium_yearly | active | 2025-10-26 | 2026-02-03 06:01:52.970 | 2026-02-03 07:01:52.983 | 2026-02-03 07:01:52.983 | 20 | mild |
| 12 | Mai Thi Ngoc | ngoc.mai@gmail.com | Ha Noi | free | active | 2026-02-03 | 2026-02-03 00:00:00.000 | 20 | mild | ||
| 7 | Dang Van Gia | gia.dang@gmail.com | Ha Noi | free | active | 2025-09-26 | 2026-01-24 07:01:52.970 | 2026-01-24 08:01:52.983 | 2026-01-24 08:01:52.983 | 30 | mild |
| 4 | Pham Thi Dung | dung.pham@gmail.com | Ha Noi | free | active | 2025-12-25 | 2026-01-14 01:01:52.970 | 2026-01-14 02:01:52.983 | 2026-01-14 02:01:52.983 | 40 | moderate |
| 8 | Bui Thi Hanh | hanh.bui@gmail.com | Da Nang | premium_monthly | active | 2025-12-05 | 2026-01-09 02:01:52.970 | 2026-01-09 03:01:52.983 | 2026-01-09 03:01:52.983 | 45 | moderate |
| 6 | Vo Thi Phuong | phuong.vo@gmail.com | Can Tho | premium_monthly | active | 2025-11-15 | 2025-12-25 04:01:52.970 | 2025-12-25 05:01:52.983 | 2025-12-25 05:01:52.983 | 60 | moderate |
| 11 | Truong Van Lam | lam.truong@gmail.com | Ho Chi Minh | free | inactive | 2025-11-25 | 2025-11-25 00:00:00.000 | 90 | severe |
| Cột | Ý nghĩa |
|---|---|
| user_id | Mã định danh duy nhất của người dùng |
| full_name | Họ tên đầy đủ |
| Địa chỉ email để gửi chiến dịch | |
| city | Thành phố, có thể dùng để phân nhóm theo vùng miền |
| subscription_type | Loại gói (free/premium) – giúp ưu tiên người premium |
| account_status | Trạng thái tài khoản – đã lọc bỏ deleted/banned |
| signup_date | Ngày đăng ký – dùng làm fallback khi không có hoạt động nào |
| last_login_at | Lần đăng nhập cuối – NULL nếu chưa bao giờ đăng nhập |
| last_workout_at | Lần tập cuối – NULL nếu chưa bao giờ tập |
| last_activity_at | Ngày hoạt động gần nhất (MAX của login và workout, hoặc signup_date) |
| days_inactive | Số ngày không hoạt động – chỉ số cốt lõi để xác định dormant |
| dormancy_level | Phân loại mild (14-30 ngày)/moderate (31-60 ngày)/severe (>60 ngày) – quyết định chiến lược email |
Kiến Thức Viết Query Tối Ưu – Thứ Mà Chỉ Khi Đi Làm Bạn Mới Biết
Cách viết 01:
WHERE DATEDIFF(DAY, last_activity_at, GETDATE()) >= 14- Cơ chế: Áp dụng hàm DATEDIFF lên cột last_activity_at.
- Hệ quả: SQL Server buộc phải thực hiện tính toán trên từng dòng một (row-by-row) trước khi nó có thể quyết định dòng đó có thỏa mãn điều kiện hay không.
- Kế hoạch thực thi: Nó khiến Query Optimizer không thể sử dụng thứ tự sắp xếp sẵn có của Index (B-Tree). Do đó, nó buộc phải chọn Index Scan hoặc Table Scan. Đây là thao tác rất tốn I/O và CPU khi dữ liệu lớn.
Cách viết 02: SARGable
WHERE last_activity_at <= DATEADD(DAY, -14, GETDATE())- Cơ chế: Cột last_activity_at đứng ở bên trái toán tử so sánh. Bên phải là một biểu thức hằng số (DATEADD chỉ tính 1 lần khi query bắt đầu chạy).
- Hệ quả: SQL Server có thể sử dụng thống kê (statistics) và cấu trúc của Index để biết chính xác dữ liệu nằm ở đâu.
- Kế hoạch thực thi: Nó có thể chọn Index Seek. Thay vì quét hết, nó nhảy thẳng đến đoạn dữ liệu cần thiết và chỉ đọc những dòng thỏa mãn.
-> Thay đổi từ DATEDIFF sang DATEADD trong mệnh đề WHERE giúp query của bạn tối ưu hơn.
Tới đây, bạn đã hoàn thành xong Task 01 trong Project Reactivation Campaign Analysis. Hãy bước tiếp sang Task 02.
Nếu bạn muốn học thực chiến SQL qua Challenges và Cases thì có thể đăng ký khóa học SQL Data Analyst Thực Chiến 100+ Challenges hoặc Data Analyst BootCamp. Inbox mình Tại Đây
