Data Challenge 191125: Order Flow Analysis & SQL Challenge

Với mô hình Challenge-based Learning, bạn sẽ không chỉ học Business Domain, mà được giải các bài toán như trong doanh nghiệp, rèn tư duy phân tích và tạo Portfolio ngay trong các khóa học phân tích dữ liệu thực chiến. Inbox mình TẠI ĐÂY nếu bạn muốn đăng ký khóa học phân tích dữ liệu nhé!

Sắp tới dịp Black Friday, Tèo Đầu To cần một công cụ báo cáo hiệu quả để theo dõi sức khỏe của hệ thống, dự báo các điểm tắc nghẽn và ra quyết định điều phối nhân sự, nguồn lực một cách kịp thời.

Báo cáo hiện tại chỉ cung cấp các chỉ số tồn kho tại một thời điểm (Ví dụ: Tổng đơn tồn: 5,000). Con số này không cung cấp đủ thông tin để trả lời các câu hỏi vận hành quan trọng:

• Lượng tồn kho này là do đơn mới tăng vọt hay do năng suất xử lý bị sụt giảm?
• Năng suất xử lý của kho trong tuần qua là bao nhiêu?
• Tốc độ giao hàng có đang theo kịp tốc độ đóng gói không?

Việc thiếu thông tin chi tiết này dẫn đến rủi ro ra quyết định sai lầm, gây quá tải hệ thống, giao hàng chậm trễ và ảnh hưởng đến trải nghiệm khách hàng.

Tèo giao cho Tý Đít Lép xây dựng báo cáo “Dòng chảy của đơn hàng” để cung cấp một cái nhìn toàn diện hàng tuần, nhằm đạt được các mục tiêu sau:

• Tách biệt rõ ràng giữa chỉ số Tồn Kho (Stock) và Dòng Chảy (Flow).
• Đo lường chính xác năng suất xử lý của kho (đóng gói) và hiệu suất của đội ngũ giao hàng.
• Xác định các điểm tắc nghẽn trong quy trình vận hành (tắc nghẽn tại kho hay tại khâu giao hàng).
• Theo dõi các dòng chảy thất thoát (đơn bị hủy, bị trả lại, thất lạc) để đánh giá rủi ro và chất lượng dịch vụ.
• Cung cấp dữ liệu đáng tin cậy để ban quản lý đưa ra các quyết định vận hành chiến lược (ví dụ: tuyển thêm nhân sự, điều phối đơn hàng cho đối tác vận chuyển).

• Pending: Trạng thái của một đơn hàng đã được khách hàng đặt thành công nhưng chưa được kho xử lý (đóng gói và bàn giao cho đơn vị vận chuyển).
• Shipped: Trạng thái của một đơn hàng đã được kho xử lý xong và đang trên đường giao đến khách hàng.

• Tồn Kho (Stock): Là số lượng đơn hàng tại một trạng thái nhất định, được đo tại một thời điểm cụ thể (đầu tuần hoặc cuối tuần).
• Dòng Chảy (Flow): Là số lượng đơn hàng di chuyển vào hoặc ra khỏi một trạng thái trong một khoảng thời gian (trong tuần).

Cột	Mô tả nghiệp vụ
week_start_date	Ngày Thứ Hai của tuần báo cáo.

Cột	Loại	Mô tả nghiệp vụ
pending_at_week_start	Stock	Số đơn tồn đọng từ tuần trước chuyển sang.
newly_pending_in_week	Flow (IN)	Số đơn khách hàng mới đặt trong tuần.
shipped_from_pending_in_week	Flow (OUT)	Số đơn đã được kho xử lý (đóng gói, gửi đi) trong tuần.
canceled_from_pending_in_week	Flow (OUT)	Số đơn bị khách hàng hủy khi vẫn còn đang ở trong kho.
pending_at_week_end	Stock	Số đơn còn lại trong kho vào cuối tuần.

Cột	Loại	Mô tả Nghiệp vụ
shipped_at_week_start	Stock	Số đơn đang trên đường vận chuyển từ tuần trước.
newly_shipped_in_week	Flow (IN)	Số đơn được kho chuyển sang cho đội giao hàng trong tuần.
delivered_from_shipped_in_week	Flow (OUT)	Số đơn đã được giao thành công đến tay khách hàng trong tuần.
returned_in_week	Flow (OUT)	Số đơn bị khách hàng trả lại sau khi đã nhận hàng.
lost_in_week	Flow (OUT)	Số đơn được xác định là thất lạc trong quá trình vận chuyển.
shipped_at_week_end	Stock	Số đơn vẫn còn đang trên đường vào cuối tuần.

• pending_at_week_end = pending_at_week_start + newly_pending_in_week – shipped_from_pending_in_week – canceled_from_pending_in_week
• shipped_at_week_end = shipped_at_week_start + newly_shipped_in_week – delivered_from_shipped_in_week – returned_in_week – lost_in_week

week_start_date	pending_at_week_start	newly_pending_in_week	shipped_from_pending_in_week	canceled_from_pending_in_week	pending_at_week_end	shipped_at_week_start	newly_shipped_in_week	delivered_from_shipped_in_week	returned_in_week	lost_in_week	shipped_at_week_end
2023-10-23	0	1	0	0	1	0	0	0	0	0	0
2023-10-30	1	6	3	0	4	0	3	0	0	0	3
2023-11-06	4	34	7	2	29	3	7	4	0	0	6
2023-11-13	29	30	29	3	27	6	29	15	0	0	20
2023-11-20	27	15	34	1	7	20	34	17	4	0	33
2023-11-27	7	14	10	0	11	33	10	34	0	1	8
2023-12-04	11	0	0	0	11	8	0	0	0	0	8

Nguyên tắc: Luôn so sánh Dòng Chảy VÀO (IN) với Dòng Chảy RA (OUT) trong cùng một trạng thái.

• Kho (Pending): So sánh newly_pending_in_week (VÀO) với shipped… + canceled… (RA).
• Giao hàng (Shipped): So sánh newly_shipped_in_week (VÀO) với delivered… + returned… + lost… (RA).

Tuần 1-2 (23/10 & 30/10):

• Dữ liệu: Số lượng đơn hàng mới (newly_pending) và số đơn xử lý (shipped) ở mức thấp và cân bằng.
  • pending_at_week_end (tồn kho) chỉ tăng nhẹ từ 1 lên 4 đơn.
  • shipped_at_week_end (hàng trên đường) chỉ có 3 đơn.
• Insight: Hệ thống đang hoạt động ổn định ở mức tải thấp. Đây là trạng thái bình thường.
• Quyết định: Không cần hành động gì đặc biệt.

Tuần 3 (2023-11-06): Báo Động Cấp 1

• Dữ liệu KHO:
  • VÀO: newly_pending_in_week tăng vọt lên 34 đơn.
  • RA: shipped_from_pending_in_week chỉ có 7 đơn.
  • Kết quả: Tồn kho cuối tuần (pending_at_week_end) phình to từ 4 lên 29 đơn.
• Dữ liệu GIAO HÀNG: Vẫn ổn định. shipped_at_week_end chỉ tăng nhẹ lên 6.
• Insight: TẮC NGHẼN BẮT ĐẦU TẠI KHO! Lượng khách hàng mua sắm khởi động trước sale đã tăng đột biến, nhưng năng suất xử lý của kho chưa theo kịp. Điểm tắc nghẽn đầu tiên đã xuất hiện.
• Quyết định: Báo động! Tuần sau sale chính thức, áp lực sẽ còn kinh khủng hơn. Cần họp khẩn để xem xét kế hoạch tăng ca và chuẩn bị nhân sự thời vụ.

Tuần 4 (2023-11-13): “BÃO SALE” – KHỦNG HOẢNG TOÀN DIỆN

• Dữ liệu KHO:
  • VÀO: newly_pending_in_week vẫn rất cao ở mức 30 đơn.
  • RA: Năng suất kho đã có sự cải thiện VƯỢT BẬC, shipped_from_pending_in_week tăng từ 7 lên 29 đơn.
  • Kết quả: Tồn kho (pending_at_week_end) vẫn rất cao (27 đơn).
• Dữ liệu GIAO HÀNG:
  • VÀO: Kho đẩy ra 29 đơn mới.
  • RA: Đội giao hàng chỉ xử lý được 15 đơn.
  • Kết quả: shipped_at_week_end tăng vọt từ 6 lên 20 đơn.
• Insight: ĐIỂM TẮC NGHẼN BẮT ĐẦU MỞ RỘNG! Kho đã xử lý đơn rất tốt (nhờ các quyết định từ tuần trước), nhưng giờ lại tạo ra một “nút thắt cổ chai” mới ở khâu giao hàng. Hàng hóa ùn ứ trên đường giao.
• Quyết định: Gọi ngay cho các đối tác vận chuyển Giao Hàng Tiết Kiệm, J&T! San sẻ ngay 30% lượng hàng mới ra khỏi kho cho họ để giải tỏa áp lực cho đội nhà.

Tuần 5 (2023-11-20): Giai đoạn hồi phục

• Dữ liệu KHO:
  • VÀO: Lượng đơn mới đã giảm về mức bình thường (15 đơn).
  • RA: Năng suất kho vẫn duy trì ở mức đỉnh điểm (34 đơn).
  • Kết quả: Lần đầu tiên, dòng chảy RA > VÀO. Tồn kho (pending_at_week_end) giảm mạnh từ 27 xuống chỉ còn 7 đơn.
• Dữ liệu GIAO HÀNG & CHẤT LƯỢNG:
  • delivered_from_shipped_in_week ở mức 17, vẫn chưa theo kịp newly_shipped_in_week (34). shipped_at_week_end tiếp tục tăng lên mức đỉnh 33.
  • Điểm đáng chú ý: Xuất hiện 4 đơn trả hàng (returned_in_week).
• Insight: Áp lực tại kho đã được giải tỏa hoàn toàn. Tuy nhiên, hậu quả của tuần trước vẫn còn, đội giao hàng đang phải xử lý lượng hàng tồn khổng lồ. Vấn đề chất lượng dịch vụ bắt đầu phát sinh (khách trả hàng) có thể do giao quá chậm hoặc do sai sót trong lúc đóng gói vội vã.
• Quyết định: Yêu cầu bộ phận Chăm sóc khách hàng liên hệ ngay với 4 khách hàng trả hàng để tìm hiểu lý do.

Tuần 6-7 (2023-11-27 & 2023-12-04): TRỞ LẠI BÌNH THƯỜNG

• Dữ liệu KHO: Tồn kho pending_at_week_end tăng nhẹ trở lại (11 đơn) do năng suất giảm sau giai đoạn căng thẳng, nhưng vẫn trong tầm kiểm soát.
• Dữ liệu GIAO HÀNG: Năng suất giao hàng delivered_from_shipped_in_week đạt 34 đơn, cuối cùng cũng vượt qua được lượng hàng mới. shipped_at_week_end giảm mạnh từ 33 xuống chỉ còn 8. Đến tuần sau, hệ thống gần như đã ổn định hoàn toàn.
• Insight: Cả hệ thống đã quay về trạng thái cân bằng.
• Quyết định: Tổ chức cuộc họp tổng kết. Sử dụng chính báo cáo này để phân tích lại toàn bộ quá trình, rút kinh nghiệm và xây dựng kịch bản ứng phó tốt hơn cho đợt Sale sắp tới.

WITH
-- *** Bước 0: Xác định khoảng thời gian phân tích một cách linh hoạt ***
-- Mục tiêu: Tạo ra một CTE duy nhất chứa ngày bắt đầu và ngày kết thúc của toàn bộ giai đoạn phân tích.
date_range AS (
    SELECT
        -- Kỹ thuật "LÀM TRÒN VỀ ĐẦU TUẦN" để tìm ngày bắt đầu báo cáo.
        DATEADD(wk, DATEDIFF(wk, 0, MIN(ordered_at)), 0) AS min_date,

        -- Kỹ thuật "TÌM MAX TRÊN NHIỀU CỘT" để đảm bảo khoảng thời gian phân tích bao trùm tất cả các sự kiện.
        (SELECT MAX(last_event_date)
         FROM (
             SELECT MAX(d) AS last_event_date FROM (VALUES (MAX(ordered_at)), (MAX(shipped_at)), (MAX(delivered_at)), (MAX(canceled_at)), (MAX(returned_at))) AS v(d)
         ) AS t
        ) AS max_date
    FROM orders
),

-- *** Bước 1: Tạo trục thời gian hàng tuần (Nền tảng của báo cáo) ***
-- Mục tiêu: Tạo một danh sách đầy đủ các ngày đầu tuần, từ `min_date` đến `max_date`.
-- Đảm bảo ngay cả những tuần không có hoạt động nào vẫn xuất hiện trong báo cáo cuối cùng.
week_spine AS (
    -- Phần neo (Anchor Member) - Điểm khởi đầu của đệ quy.
    SELECT date_range.min_date AS week_start_date FROM date_range
    UNION ALL
    -- Phần đệ quy (Recursive Member) - Lặp lại và cộng thêm 1 tuần cho đến khi đạt đến ngày kết thúc.
    SELECT DATEADD(WEEK, 1, week_start_date) FROM week_spine, date_range
    WHERE week_spine.week_start_date < date_range.max_date
),

-- *** Bước 2: Quét bảng `orders` MỘT LẦN DUY NHẤT và tổng hợp các dòng chảy (Flows) ***
-- Tối ưu hiệu suất bằng kỹ thuật UNPIVOT: Chuyển đổi các cột ngày thành các dòng sự kiện.
all_flows_unpivoted AS (
    SELECT
        DATEADD(wk, DATEDIFF(wk, 0, event_at), 0) AS week_start_date,
        -- Sử dụng SUM(CASE WHEN...) để đếm số lượng cho từng loại sự kiện trong tuần.
        SUM(CASE WHEN event_type = 'ordered'   THEN 1 ELSE 0 END) AS newly_pending_in_week,
        SUM(CASE WHEN event_type = 'shipped'   THEN 1 ELSE 0 END) AS shipped_from_pending_in_week,
        SUM(CASE WHEN event_type = 'delivered' THEN 1 ELSE 0 END) AS delivered_from_shipped_in_week,
        SUM(CASE WHEN event_type = 'canceled'  THEN 1 ELSE 0 END) AS canceled_from_pending_in_week,
        SUM(CASE WHEN event_type = 'returned'  THEN 1 ELSE 0 END) AS returned_in_week,
        SUM(CASE WHEN event_type = 'lost'      THEN 1 ELSE 0 END) AS lost_in_week
    FROM (
        -- UNPIVOT: Biến mỗi cột ngày thành một dòng với `event_at` và `event_type`.
        SELECT order_id, ordered_at AS event_at, 'ordered' AS event_type FROM orders
        UNION ALL
        SELECT order_id, shipped_at, 'shipped' FROM orders WHERE shipped_at IS NOT NULL
        UNION ALL
        SELECT order_id, delivered_at, 'delivered' FROM orders WHERE delivered_at IS NOT NULL
        UNION ALL
        SELECT order_id, canceled_at, 'canceled' FROM orders WHERE canceled_at IS NOT NULL
        UNION ALL
        SELECT order_id, returned_at, 'returned' FROM orders WHERE returned_at IS NOT NULL
        UNION ALL
        -- Logic đặc biệt cho đơn thất lạc.
        SELECT order_id, DATEADD(day, 21, shipped_at), 'lost'
        FROM orders, date_range
        WHERE shipped_at IS NOT NULL AND delivered_at IS NULL AND canceled_at IS NULL AND returned_at IS NULL
          AND DATEDIFF(day, shipped_at, date_range.max_date) > 21
    ) AS events
    WHERE event_at IS NOT NULL -- Loại bỏ các dòng có ngày sự kiện là NULL
    GROUP BY DATEADD(wk, DATEDIFF(wk, 0, event_at), 0)
),

-- *** Bước 3: Tổng hợp tất cả các dòng chảy vào một bảng duy nhất ***
-- Mục tiêu: Hợp nhất tất cả các số liệu dòng chảy đã tính, sử dụng `week_spine` làm nền.
weekly_flows_summary AS (
    SELECT
        w.week_start_date,
        -- Kỹ thuật `LEFT JOIN` và `ISNULL` để đảm bảo báo cáo liên tục.
        ISNULL(a.newly_pending_in_week, 0) AS newly_pending_in_week,
        ISNULL(a.shipped_from_pending_in_week, 0) AS shipped_from_pending_in_week,
        ISNULL(a.delivered_from_shipped_in_week, 0) AS delivered_from_shipped_in_week,
        ISNULL(a.canceled_from_pending_in_week, 0) AS canceled_from_pending_in_week,
        ISNULL(a.returned_in_week, 0) AS returned_in_week,
        ISNULL(a.lost_in_week, 0) AS lost_in_week
    FROM week_spine AS w
    LEFT JOIN all_flows_unpivoted AS a ON w.week_start_date = a.week_start_date
),

-- *** Bước 4: Tính toán số liệu Tồn Kho (Stock) cuối kỳ bằng tổng lũy kế ***
-- Mục tiêu: Tính các chỉ số tồn kho cuối mỗi tuần (`pending_at_week_end`, `shipped_at_week_end`).
weekly_stock_calc AS (
    SELECT
        *,
        -- Kỹ thuật: Dùng hàm cửa sổ `SUM(...) OVER (ORDER BY ...)` để tính tổng lũy kế.
        -- CÔNG THỨC: Tồn cuối kỳ = Tổng lũy kế của (Vào - Ra).
        SUM(newly_pending_in_week - shipped_from_pending_in_week - canceled_from_pending_in_week) OVER (ORDER BY week_start_date) AS pending_at_week_end,
        SUM(shipped_from_pending_in_week - delivered_from_shipped_in_week - returned_in_week - lost_in_week) OVER (ORDER BY week_start_date) AS shipped_at_week_end
    FROM weekly_flows_summary
)

-- *** Bước 5: Hoàn thiện báo cáo - Tính Tồn Kho đầu kỳ và trình bày kết quả ***
-- Mục tiêu: Tạo ra bảng kết quả cuối cùng với đầy đủ các cột theo đặc tả.
SELECT
    -- Định dạng lại cột ngày cho dễ đọc, tuân thủ tiêu chuẩn ISO 8601.
    FORMAT(week_start_date, 'yyyy-MM-dd') AS week_start_date,
    
    -- Kỹ thuật: Dùng hàm `LAG` để "nhìn lại" giá trị tồn kho cuối của tuần trước đó.
    LAG(pending_at_week_end, 1, 0) OVER (ORDER BY week_start_date) AS pending_at_week_start,
    newly_pending_in_week,
    shipped_from_pending_in_week,
    canceled_from_pending_in_week,
    pending_at_week_end,
    
    LAG(shipped_at_week_end, 1, 0) OVER (ORDER BY week_start_date) AS shipped_at_week_start,
    shipped_from_pending_in_week AS newly_shipped_in_week, -- Đổi tên cột cho phù hợp đặc tả
    delivered_from_shipped_in_week,
    returned_in_week,
    lost_in_week,
    shipped_at_week_end
FROM weekly_stock_calc
-- Tùy chọn `MAXRECURSION 0` gỡ bỏ giới hạn 100 lần lặp mặc định của CTE đệ quy.
OPTION (MAXRECURSION 0);