🧠 Case Study Buổi 6: SQL — Window Function & Data Warehouse

Bài học thực tế từ các nền tảng công nghệ hàng đầu thế giới và Việt Nam

Tổng quan

Trong buổi học này, chúng ta đã tìm hiểu về Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD), Running Total, Moving Average, kiến trúc Data Warehouse (Star Schema — Fact vs Dimension Table), CTE (Common Table Expression), và quy trình ETL. Lý thuyết cho thấy Window Functions là bước nhảy từ SQL cơ bản sang SQL phân tích chuyên nghiệp, còn Data Warehouse là cách tổ chức dữ liệu tối ưu cho phân tích quy mô lớn. Nhưng chỉ khi nhìn vào thực tế, bạn mới thấy sức mạnh thực sự: ranking top bài hát theo từng quốc gia mỗi tuần (Spotify), phân tích doanh thu bán lẻ quy mô petabyte trên star schema (Amazon), hay xây dựng data warehouse cho hàng trăm triệu giao dịch thanh toán điện tử (VNPay).

Ba case study dưới đây minh họa cách Window Functions và Data Warehouse được áp dụng ở 3 bối cảnh khác nhau — từ nền tảng music streaming lớn nhất thế giới (Spotify), đế chế e-commerce toàn cầu (Amazon), đến cổng thanh toán điện tử hàng đầu Việt Nam (VNPay). Mỗi case study sẽ trả lời 3 câu hỏi cốt lõi: (1) Dữ liệu gì được lưu và tổ chức như thế nào? (2) Phân tích bằng kỹ thuật SQL nào (Window Function, CTE, Star Schema)? (3) Quyết định kinh doanh nào đã thay đổi nhờ kết quả phân tích?

#	Công ty	Vấn đề	Kỹ thuật SQL chính
1	Spotify	Ranking top songs per region per week	ROW_NUMBER, RANK, DENSE_RANK, PARTITION BY, LAG
2	Amazon	Star schema cho retail analytics quy mô petabyte	Star Schema, Fact/Dimension Tables, CTE, SUM() OVER
3	VNPay	Data Warehouse cho transaction analytics	Data Warehouse, ETL, Running Total, LAG/LEAD, CTE

Case Study 1: Spotify — Window Function cho ranking top songs per region per week

🏷️ Thông tin

Tiêu chí	Chi tiết
Công ty	Spotify Technology S.A.
Ngành	Music Streaming — Phát nhạc trực tuyến
Quy mô	675+ triệu monthly active users, 260+ triệu Premium subscribers, 100+ triệu bài hát, hoạt động tại 180+ quốc gia, revenue ~€15.7 tỷ (2025)
Thị trường	Global — 180+ quốc gia và vùng lãnh thổ
Chủ đề DA liên quan	Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LAG), PARTITION BY, CTE, trend analysis, ranking per group

📋 Bối cảnh

Spotify là nền tảng music streaming lớn nhất thế giới, với hơn 675 triệu người dùng hoạt động hàng tháng trên 180+ quốc gia. Mỗi ngày, người dùng tạo ra hơn 1.8 tỷ lượt stream — mỗi lượt stream ghi lại thông tin: user nào, nghe bài gì, của nghệ sĩ nào, ở quốc gia nào, lúc mấy giờ, nghe bao lâu, skip hay nghe hết.

Bộ phận Content & Editorial Analytics tại Spotify chịu trách nhiệm tạo bảng xếp hạng hàng tuần (Weekly Charts) cho từng quốc gia — dữ liệu này quyết định: bài hát nào hiển thị trên "Top 50 Vietnam", "Top Songs Global", playlist "Today's Top Hits" (37+ triệu followers); nghệ sĩ nào được promote trên homepage; label nào được ưu tiên trong editorial playlists; và Spotify trả royalties cho label/nghệ sĩ dựa trên stream share.

Vấn đề cốt lõi: Spotify cần xếp hạng hàng triệu bài hát trong mỗi quốc gia mỗi tuần — không phải xếp hạng toàn cầu đơn giản (chỉ cần ORDER BY streams DESC), mà là ranking riêng trong từng quốc gia (PARTITION BY country) và so sánh thay đổi thứ hạng tuần này vs tuần trước (LAG). Đây chính xác là bài toán mà GROUP BY không giải quyết được — cần Window Functions.

⚡ Thách thức

Xếp hạng per region, không phải global: Bài hát "APT." của Rosé & Bruno Mars có thể #1 ở Việt Nam nhưng #15 ở Mỹ, #3 ở Hàn Quốc. Cần ranking riêng biệt cho 180+ quốc gia trong cùng 1 query — GROUP BY chỉ cho tổng streams, không cho thứ hạng trong nhóm.
Trend tracking tuần-qua-tuần: Sếp hỏi: "Bài nào tăng hạng nhanh nhất tuần này so với tuần trước?" — cần truy cập dòng tuần trước để so sánh. SQL cơ bản không có khái niệm "dòng trước" — cần LAG().
Phân biệt RANK vs DENSE_RANK: Nếu 2 bài hát có cùng số streams, chúng nên xếp hạng giống nhau (tie). Nhưng bài tiếp theo nên xếp hạng gì? #3 (DENSE_RANK) hay #4 (RANK)? Spotify chọn DENSE_RANK cho charts công khai để không "nhảy hạng".
Khối lượng dữ liệu cực lớn: ~1.8 tỷ streams/ngày × 7 ngày = ~12.6 tỷ stream records/tuần. Cần aggregate → rồi rank → rồi compare → trên 180+ quốc gia × 100 triệu bài hát. Query phải hiệu quả.
Phát hiện viral songs sớm: Bài hát mới release mà tuần đầu đã vào top 50 — cần flag "New Entry" (tuần trước không có trên bảng xếp hạng). Điều này cần logic: nếu LAG() trả về NULL → đây là new entry.

🛠️ Giải pháp

Dữ liệu gì? (Database schema)

┌──────────────────┐       ┌──────────────────────┐       ┌──────────────────┐
│     tracks       │       │   weekly_streams      │       │     artists      │
├──────────────────┤       ├──────────────────────┤       ├──────────────────┤
│ track_id (PK)    │───┐   │ stream_id (PK)       │   ┌───│ artist_id (PK)   │
│ track_name       │   └──>│ track_id (FK)        │   │   │ artist_name      │
│ artist_id (FK)   │───────│ artist_id (FK)       │<──┘   │ label            │
│ album_id         │       │ country_code         │       │ genre            │
│ genre            │       │ week_start_date      │       │ country_origin   │
│ duration_ms      │       │ total_streams        │       │ monthly_listeners│
│ release_date     │       │ unique_listeners     │       └──────────────────┘
│ is_explicit      │       │ avg_stream_duration  │
└──────────────────┘       │ skip_rate            │       ┌──────────────────┐
                           │ save_rate            │       │    countries     │
                           └──────────────────────┘       ├──────────────────┤
                                                          │ country_code (PK)│
                                                          │ country_name     │
                                                          │ region           │
                                                          └──────────────────┘

tracks: ~100 triệu records — metadata mỗi bài hát
weekly_streams: aggregate từ raw streams — 1 dòng per (track, country, week). ~180 quốc gia × top 10,000 tracks = ~1.8 triệu records/tuần
artists: ~11 triệu records — thông tin nghệ sĩ
countries: 180+ records — mapping country_code → tên, khu vực

Phân tích bằng SQL query nào?

Query 1: Top 10 bài hát mỗi quốc gia mỗi tuần — ROW_NUMBER + PARTITION BY

sql

-- Xếp hạng top 10 bài hát theo streams cho MỖI quốc gia trong tuần
-- ROW_NUMBER: mỗi bài có 1 thứ hạng duy nhất (không tie)
-- PARTITION BY country_code: xếp hạng RIÊNG cho từng quốc gia

WITH weekly_ranking AS (
    SELECT
        ws.country_code,
        c.country_name,
        ws.track_id,
        t.track_name,
        a.artist_name,
        ws.total_streams,
        ws.unique_listeners,
        ROW_NUMBER() OVER (
            PARTITION BY ws.country_code
            ORDER BY ws.total_streams DESC
        ) AS rank_position
    FROM weekly_streams ws
    JOIN tracks t ON ws.track_id = t.track_id
    JOIN artists a ON t.artist_id = a.artist_id
    JOIN countries c ON ws.country_code = c.country_code
    WHERE ws.week_start_date = '2026-02-09'
)
SELECT *
FROM weekly_ranking
WHERE rank_position <= 10
ORDER BY country_code, rank_position;

Kết quả ví dụ (trích — Vietnam):

country_code	country_name	track_name	artist_name	total_streams	unique_listeners	rank_position
VN	Vietnam	Đừng Làm Trái Tim Anh Đau	Sơn Tùng M-TP	18,500,000	4,200,000	1
VN	Vietnam	APT.	Rosé & Bruno Mars	15,200,000	3,800,000	2
VN	Vietnam	Die With A Smile	Lady Gaga & Bruno Mars	12,800,000	3,100,000	3
VN	Vietnam	Có Em Chờ	MIN & MR.A	11,500,000	2,900,000	4
VN	Vietnam	Espresso	Sabrina Carpenter	10,200,000	2,600,000	5

→ Insight: ROW_NUMBER + PARTITION BY cho phép Spotify tạo bảng xếp hạng riêng biệt cho 180+ quốc gia trong 1 query duy nhất. Không cần viết 180 query khác nhau. Đây là sức mạnh cốt lõi của Window Functions — xếp hạng trong mỗi nhóm mà vẫn giữ nguyên chi tiết từng dòng.

Query 2: So sánh thứ hạng tuần này vs tuần trước — LAG() + CTE

sql

-- So sánh rank tuần này với tuần trước cho mỗi bài hát tại mỗi quốc gia
-- LAG(): truy cập giá trị của dòng trước (tuần trước) trong cùng partition

WITH current_week AS (
    SELECT
        ws.country_code,
        ws.track_id,
        t.track_name,
        a.artist_name,
        ws.total_streams,
        ws.week_start_date,
        DENSE_RANK() OVER (
            PARTITION BY ws.country_code
            ORDER BY ws.total_streams DESC
        ) AS current_rank
    FROM weekly_streams ws
    JOIN tracks t ON ws.track_id = t.track_id
    JOIN artists a ON t.artist_id = a.artist_id
    WHERE ws.week_start_date = '2026-02-09'
),
previous_week AS (
    SELECT
        ws.country_code,
        ws.track_id,
        ws.total_streams AS prev_streams,
        DENSE_RANK() OVER (
            PARTITION BY ws.country_code
            ORDER BY ws.total_streams DESC
        ) AS prev_rank
    FROM weekly_streams ws
    WHERE ws.week_start_date = '2026-02-02'
)
SELECT
    cw.country_code,
    cw.track_name,
    cw.artist_name,
    cw.total_streams,
    cw.current_rank,
    pw.prev_rank,
    CASE
        WHEN pw.prev_rank IS NULL THEN 'NEW'
        WHEN cw.current_rank < pw.prev_rank THEN '▲ +' || (pw.prev_rank - cw.current_rank)
        WHEN cw.current_rank > pw.prev_rank THEN '▼ -' || (cw.current_rank - pw.prev_rank)
        ELSE '= (unchanged)'
    END AS rank_change,
    CASE
        WHEN pw.prev_streams IS NOT NULL AND pw.prev_streams > 0 THEN
            ROUND((cw.total_streams - pw.prev_streams) * 100.0 / pw.prev_streams, 1)
        ELSE NULL
    END AS stream_growth_pct
FROM current_week cw
LEFT JOIN previous_week pw
    ON cw.country_code = pw.country_code
    AND cw.track_id = pw.track_id
WHERE cw.current_rank <= 20
  AND cw.country_code = 'VN'
ORDER BY cw.current_rank;

Kết quả ví dụ (Vietnam, tuần 2026-02-09):

country_code	track_name	artist_name	total_streams	current_rank	prev_rank	rank_change	stream_growth_pct
VN	Đừng Làm Trái Tim Anh Đau	Sơn Tùng M-TP	18,500,000	1	3	▲ +2	45.2
VN	APT.	Rosé & Bruno Mars	15,200,000	2	1	▼ -1	-8.5
VN	Die With A Smile	Lady Gaga & Bruno Mars	12,800,000	3	2	▼ -1	-3.2
VN	Có Em Chờ	MIN & MR.A	11,500,000	4	NULL	NEW	NULL
VN	Espresso	Sabrina Carpenter	10,200,000	5	4	▲ +1	12.8

→ Insight: Sơn Tùng M-TP tăng 2 bậc lên #1 ở Việt Nam với stream growth +45.2% — dấu hiệu viral. "Có Em Chờ" là NEW entry (tuần trước không có trên chart) → Editorial team đưa vào playlist "New Music Friday Vietnam". "APT." giảm 1 bậc nhưng vẫn giữ #2 — bài hát ở giai đoạn plateau sau khi peak 3 tuần trước. Spotify dùng insight này để quyết định bài nào promote trên homepage Vietnam.

Query 3: Bài hát debut mạnh nhất — LAG() trả NULL = New Entry

sql

-- Tìm bài hát mới debut tuần này (chưa từng lên chart tuần trước)
-- mà đã vào top 50 → dấu hiệu viral, cần promote ngay

WITH ranked_songs AS (
    SELECT
        ws.country_code,
        ws.track_id,
        t.track_name,
        a.artist_name,
        t.release_date,
        ws.total_streams,
        ws.week_start_date,
        DENSE_RANK() OVER (
            PARTITION BY ws.country_code
            ORDER BY ws.total_streams DESC
        ) AS chart_rank,
        LAG(ws.total_streams) OVER (
            PARTITION BY ws.country_code, ws.track_id
            ORDER BY ws.week_start_date
        ) AS prev_week_streams
    FROM weekly_streams ws
    JOIN tracks t ON ws.track_id = t.track_id
    JOIN artists a ON t.artist_id = a.artist_id
    WHERE ws.week_start_date IN ('2026-02-02', '2026-02-09')
)
SELECT
    country_code,
    track_name,
    artist_name,
    release_date,
    total_streams,
    chart_rank,
    'NEW ENTRY' AS status
FROM ranked_songs
WHERE week_start_date = '2026-02-09'
  AND chart_rank <= 50
  AND prev_week_streams IS NULL    -- LAG = NULL → tuần trước không có dữ liệu
ORDER BY country_code, chart_rank;

→ Insight: LAG() trả NULL khi không tồn tại dòng trước — tức bài hát này chưa từng xuất hiện trên chart. Spotify tự động flag các bài hát "New Entry in Top 50" để Editorial team review và đưa vào playlist. Trung bình mỗi tuần có 200–400 new entries toàn cầu, nhưng chỉ 15–20 bài debut ở top 10 — đây là tiềm năng viral lớn.

Query 4: Running total streams tích lũy — SUM() OVER + CTE

sql

-- Tính running total streams tích lũy theo tuần cho 1 bài hát tại 1 quốc gia
-- Mục tiêu: theo dõi trajectory — bài hát đang tăng trưởng hay suy giảm?

WITH track_weekly AS (
    SELECT
        ws.week_start_date,
        ws.total_streams,
        ws.unique_listeners,
        SUM(ws.total_streams) OVER (
            ORDER BY ws.week_start_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_streams,
        ROUND(AVG(ws.total_streams) OVER (
            ORDER BY ws.week_start_date
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ), 0) AS moving_avg_4w,
        LAG(ws.total_streams, 1) OVER (
            ORDER BY ws.week_start_date
        ) AS prev_week_streams,
        ROUND(
            (ws.total_streams - LAG(ws.total_streams, 1) OVER (
                ORDER BY ws.week_start_date
            )) * 100.0 /
            NULLIF(LAG(ws.total_streams, 1) OVER (
                ORDER BY ws.week_start_date
            ), 0), 1
        ) AS wow_growth_pct
    FROM weekly_streams ws
    WHERE ws.track_id = 'SPT_TRACK_001'    -- APT. - Rosé & Bruno Mars
      AND ws.country_code = 'VN'
)
SELECT *
FROM track_weekly
ORDER BY week_start_date;

Kết quả ví dụ:

week_start_date	total_streams	unique_listeners	cumulative_streams	moving_avg_4w	prev_week_streams	wow_growth_pct
2026-01-05	8,500,000	2,100,000	8,500,000	8,500,000	NULL	NULL
2026-01-12	12,200,000	2,800,000	20,700,000	10,350,000	8,500,000	43.5
2026-01-19	16,800,000	3,500,000	37,500,000	12,500,000	12,200,000	37.7
2026-01-26	18,100,000	3,900,000	55,600,000	13,900,000	16,800,000	7.7
2026-02-02	16,600,000	3,700,000	72,200,000	15,925,000	18,100,000	-8.3
2026-02-09	15,200,000	3,800,000	87,400,000	16,675,000	16,600,000	-8.4

→ Insight: "APT." đạt peak ở tuần 2026-01-26 (18.1M streams) rồi bắt đầu giảm — WoW growth chuyển từ +7.7% sang -8.3%, -8.4%. Moving average 4 tuần cho thấy trend đang đi ngang/giảm nhẹ. Cumulative streams đạt 87.4M sau 6 tuần. Spotify dùng insight này để quyết định: bài hát đang ở giai đoạn "decline" → giảm dần vị trí trên playlist lớn, nhường chỗ cho bài hát mới đang trending lên.

Query 5: Stream share — tỷ trọng mỗi bài hát trong tổng quốc gia

sql

-- Tính % stream mỗi bài hát so với tổng streams quốc gia (Percent of Total)
-- SUM() OVER (PARTITION BY country) = tổng toàn bộ quốc gia, giữ nguyên mỗi dòng

SELECT
    ws.country_code,
    t.track_name,
    a.artist_name,
    ws.total_streams,
    SUM(ws.total_streams) OVER (
        PARTITION BY ws.country_code
    ) AS country_total_streams,
    ROUND(
        ws.total_streams * 100.0 /
        SUM(ws.total_streams) OVER (PARTITION BY ws.country_code),
        3
    ) AS stream_share_pct
FROM weekly_streams ws
JOIN tracks t ON ws.track_id = t.track_id
JOIN artists a ON t.artist_id = a.artist_id
WHERE ws.week_start_date = '2026-02-09'
  AND ws.country_code = 'VN'
ORDER BY ws.total_streams DESC
LIMIT 10;

→ Insight: Top 1 bài hát (Sơn Tùng M-TP) chiếm 2.8% tổng streams Việt Nam, top 10 chiếm ~15%. Điều này cho thấy Spotify Vietnam có long-tail distribution — hàng ngàn bài hát chia sẻ 85% streams còn lại, healthy cho hệ sinh thái. Nếu 1 quốc gia có top 1 chiếm >10% → quá tập trung, rủi ro nếu nghệ sĩ đó rời nền tảng. Spotify dùng stream_share_pct làm cơ sở tính royalty cho label và nghệ sĩ.

📊 Kết quả

Chỉ số	Trước (SQL cơ bản / Export Excel)	Sau (Window Functions + CTE)
Thời gian tạo weekly chart 180+ quốc gia	4–6 giờ (query từng quốc gia riêng)	15 phút (1 query PARTITION BY country)
So sánh rank tuần này vs tuần trước	Thủ công: export 2 tuần → VLOOKUP	Tự động: LAG() trong cùng query
Phát hiện New Entry / viral songs	Cuối tuần (manual review)	Real-time: LAG() IS NULL → auto-flag
Tính cumulative streams cho label	Export → Excel → running total	SUM() OVER (ROWS UNBOUNDED PRECEDING)
Stream share cho royalty calculation	Chạy 2 query riêng: tổng + chi tiết → chia	1 query: SUM() OVER (PARTITION BY country)
Số query cần viết cho global charts	180+ queries (1 per country)	1 query duy nhất
Tính moving average trend	Excel: AVERAGE(D2:D5) kéo xuống	SQL: AVG() OVER (ROWS 3 PRECEDING)

Window Functions giúp Spotify Content Analytics team biến 12.6 tỷ stream records/tuần thành bảng xếp hạng chính xác cho 180+ quốc gia — từ ranking per region, trend tracking WoW, phát hiện viral songs, đến tính stream share cho royalty. Tất cả chỉ cần 1 nhóm query với PARTITION BY — thay vì 180+ query riêng lẻ.

💡 Bài học cho Data Analyst Việt Nam

PARTITION BY là "GROUP BY không collapse". GROUP BY gom 18 triệu dòng streams Việt Nam thành 1 dòng tổng. PARTITION BY giữ nguyên 18 triệu dòng nhưng thêm cột tổng bên cạnh — bạn vừa thấy chi tiết, vừa thấy tổng thể. Đây là khác biệt cốt lõi và quyền lực nhất của Window Functions.
LAG() thay thế self-JOIN cho so sánh kỳ trước. Trước khi có Window Functions, để so sánh tuần này vs tuần trước, bạn phải self-JOIN bảng với chính nó (chậm, khó đọc). LAG(column, 1) = giá trị dòng trước — 1 dòng code thay 10 dòng JOIN.
CTE giúp query phức tạp trở nên đọc được. Query 2 (so sánh rank) dùng 2 CTE: current_week và previous_week — mỗi CTE có tên rõ ràng, logic riêng, dễ debug. Nếu viết tất cả trong 1 query lồng nhau (subquery in subquery) — không ai đọc nổi.
Running total + Moving average = Trend analysis. SUM() OVER (ORDER BY date) cho running total, AVG() OVER (ROWS 3 PRECEDING) cho moving average 4 tuần. Hai giá trị này giúp phân biệt: bài hát đang tăng trưởng (running total tăng nhanh, MA tăng) hay suy giảm (running total tăng chậm lại, MA giảm).

Case Study 2: Amazon — Star Schema cho retail analytics quy mô petabyte

🏷️ Thông tin

Tiêu chí	Chi tiết
Công ty	Amazon.com, Inc.
Ngành	E-commerce / Cloud Computing — Thương mại điện tử
Quy mô	310+ triệu active customer accounts, ~$640 tỷ USD revenue (2025), 12+ triệu products, 2+ triệu third-party sellers
Thị trường	Global — 20+ marketplace quốc gia (US, UK, DE, JP, IN, ...)
Chủ đề DA liên quan	Star Schema, Fact Table vs Dimension Table, CTE, SUM() OVER, RANK(), Data Warehouse, ETL pipeline

📋 Bối cảnh

Amazon là công ty e-commerce lớn nhất thế giới, xử lý hơn 1.6 triệu packages/ngày tại thị trường Mỹ và hàng triệu đơn hàng toàn cầu. Dữ liệu kinh doanh Amazon bao gồm: thông tin sản phẩm (12+ triệu SKUs), thông tin khách hàng (310+ triệu accounts), đơn hàng (hàng tỷ records/năm), thanh toán, vận chuyển, đánh giá, quảng cáo, và seller performance.

Bộ phận Retail Analytics tại Amazon Redshift (Data Warehouse) cần trả lời hàng nghìn câu hỏi kinh doanh mỗi ngày: "Doanh thu theo category tuần này?", "Top sellers theo marketplace?", "Customer Lifetime Value theo phân khúc?", "Tồn kho bao nhiêu ngày?". Hệ thống OLTP (Online Transaction Processing — database giao dịch thời gian thực) không thể chạy những query phân tích nặng này — sẽ làm chậm checkout flow.

Giải pháp: Amazon xây dựng Data Warehouse tách biệt, dữ liệu được ETL (Extract-Transform-Load) từ systems giao dịch vào warehouse theo mô hình Star Schema — tối ưu cho truy vấn phân tích. Star Schema là kiến trúc chuẩn công nghiệp cho data warehouse, trong đó 1 fact table trung tâm (chứa metrics — revenue, quantity, cost) được bao quanh bởi nhiều dimension tables (chứa mô tả — product info, customer info, date info, geography info).

⚡ Thách thức

OLTP không thể phục vụ analytics: Database giao dịch của Amazon được thiết kế cho INSERT/UPDATE nhanh (đặt hàng, thanh toán) — schema normalized (3NF) với hàng trăm bảng. Query phân tích cần JOIN 10+ bảng, scan hàng tỷ dòng → làm chậm giao dịch thực. Cần tách biệt OLTP ↔ OLAP.
Schema quá phức tạp cho DA: Schema OLTP có 200+ bảng normalized — DA query 1 báo cáo đơn giản cần JOIN 8–12 bảng. Cần mô hình đơn giản hơn: Star Schema với 1 fact table + 4–6 dimension tables → DA JOIN tối đa 4–5 bảng.
Tính running total và year-to-date: Ban lãnh đạo muốn báo cáo doanh thu tích lũy (YTD revenue), so sánh MoM/YoY. Cần Window Functions trên fact table: SUM() OVER (ORDER BY month) cho running total, LAG() cho so sánh kỳ trước.
Ranking sản phẩm trong mỗi category: Với 12+ triệu SKUs trải trên 30+ categories — cần ranking top products in each category. Bài toán tương tự Spotify nhưng cho e-commerce: RANK() OVER (PARTITION BY category_id).
Data freshness vs query performance: Data cần được ETL từ OLTP vào Data Warehouse hàng ngày/giờ. Nếu ETL chậm → DA xem data cũ. Nếu ETL quá frequent → warehouse overload.

🛠️ Giải pháp

Dữ liệu gì? (Star Schema design)

                         ┌────────────────────┐
                         │  dim_customers     │
                         ├────────────────────┤
                         │ customer_key (PK)  │
                         │ customer_id        │
                         │ customer_name      │
                         │ email              │
                         │ segment            │
                         │ signup_date        │
                         │ country            │
                         │ city               │
                         └────────┬───────────┘
                                  │
┌────────────────────┐   ┌───────┴────────────────────┐   ┌────────────────────┐
│  dim_products      │   │      fact_orders           │   │  dim_dates         │
├────────────────────┤   ├────────────────────────────┤   ├────────────────────┤
│ product_key (PK)   │──>│ order_key (PK)             │<──│ date_key (PK)      │
│ product_id         │   │ product_key (FK)           │   │ full_date          │
│ product_name       │   │ customer_key (FK)          │   │ day_of_week        │
│ category           │   │ date_key (FK)              │   │ week_number        │
│ sub_category       │   │ seller_key (FK)            │   │ month              │
│ brand              │   │ marketplace_key (FK)       │   │ quarter            │
│ price              │   │ ────── MEASURES ──────     │   │ year               │
│ cost               │   │ quantity                   │   │ is_holiday          │
│ weight_kg          │   │ unit_price                 │   │ fiscal_quarter     │
└────────────────────┘   │ discount_amount            │   └────────────────────┘
                         │ net_revenue                │
┌────────────────────┐   │ shipping_cost              │   ┌────────────────────┐
│  dim_sellers       │   │ tax_amount                 │   │  dim_marketplace   │
├────────────────────┤   │ total_amount               │   ├────────────────────┤
│ seller_key (PK)    │──>│ is_prime                   │   │ mkt_key (PK)       │
│ seller_id          │   │ fulfillment_type           │   │ marketplace_id     │
│ seller_name        │   │ order_status               │   │ marketplace_name   │
│ seller_type        │   └────────────────────────────┘   │ currency           │
│ seller_rating      │                                    │ timezone           │
│ registration_date  │                                    └────────────────────┘
└────────────────────┘

Tại sao Star Schema?

Đặc điểm	OLTP (Normalized 3NF)	OLAP — Star Schema
Mục đích	Ghi giao dịch nhanh (INSERT/UPDATE)	Phân tích nhanh (SELECT/JOIN/GROUP BY)
Số bảng	200+	1 fact + 5–6 dimensions = 6–7 bảng
Redundancy	Tối thiểu (no duplicate data)	Chấp nhận (dimension table denormalized)
JOIN cần thiết	8–12 bảng cho 1 report	2–5 bảng tối đa
Query speed	Chậm cho analytics	Nhanh — tối ưu cho aggregate + scan
DA usability	Khó — cần hiểu 200+ bảng	Dễ — fact table trung tâm, dimensions mô tả

Phân tích bằng SQL query nào?

Query 1: Doanh thu theo category + Running total YTD — CTE + SUM() OVER

sql

-- Tính doanh thu theo category theo tháng + running total tích lũy YTD
-- Star Schema: fact_orders JOIN dim_products + dim_dates

WITH monthly_revenue AS (
    SELECT
        p.category,
        d.year,
        d.month,
        SUM(f.net_revenue) AS monthly_revenue,
        SUM(f.quantity) AS monthly_units
    FROM fact_orders f
    JOIN dim_products p ON f.product_key = p.product_key
    JOIN dim_dates d ON f.date_key = d.date_key
    WHERE d.year = 2026
      AND f.order_status = 'delivered'
    GROUP BY p.category, d.year, d.month
)
SELECT
    category,
    year,
    month,
    monthly_revenue,
    monthly_units,
    SUM(monthly_revenue) OVER (
        PARTITION BY category
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_revenue,
    LAG(monthly_revenue, 1) OVER (
        PARTITION BY category
        ORDER BY month
    ) AS prev_month_revenue,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue, 1) OVER (
            PARTITION BY category ORDER BY month
        )) * 100.0 /
        NULLIF(LAG(monthly_revenue, 1) OVER (
            PARTITION BY category ORDER BY month
        ), 0), 1
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY category, month;

Kết quả ví dụ (trích — Electronics & Books):

category	year	month	monthly_revenue	monthly_units	ytd_revenue	prev_month_revenue	mom_growth_pct
Electronics	2026	1	8,200,000,000	1,250,000	8,200,000,000	NULL	NULL
Electronics	2026	2	7,500,000,000	1,100,000	15,700,000,000	8,200,000,000	-8.5
Books	2026	1	1,800,000,000	3,500,000	1,800,000,000	NULL	NULL
Books	2026	2	2,100,000,000	4,200,000	3,900,000,000	1,800,000,000	16.7

→ Insight: Electronics giảm 8.5% MoM (post-holiday) nhưng YTD vẫn dẫn đầu với $15.7B. Books tăng 16.7% MoM — có thể do back-to-school season. Running total (ytd_revenue) giúp ban lãnh đạo theo dõi tiến độ so với target năm: Electronics YTD = $15.7B, target = $98B → đạt 16% sau 2 tháng (on track). Star Schema cho phép query này chỉ cần JOIN 2 dimension tables — thay vì 8+ bảng ở OLTP schema.

Query 2: Top 5 sản phẩm mỗi category — RANK() OVER + PARTITION BY

sql

-- Xếp hạng top 5 sản phẩm theo doanh thu trong MỖI category
-- Star Schema: JOIN fact_orders → dim_products → dim_dates

WITH product_sales AS (
    SELECT
        p.category,
        p.product_name,
        p.brand,
        SUM(f.net_revenue) AS total_revenue,
        SUM(f.quantity) AS total_units,
        COUNT(DISTINCT f.customer_key) AS unique_buyers,
        RANK() OVER (
            PARTITION BY p.category
            ORDER BY SUM(f.net_revenue) DESC
        ) AS category_rank
    FROM fact_orders f
    JOIN dim_products p ON f.product_key = p.product_key
    JOIN dim_dates d ON f.date_key = d.date_key
    WHERE d.year = 2026
      AND d.month <= 2
      AND f.order_status IN ('delivered', 'shipped')
    GROUP BY p.category, p.product_name, p.brand
)
SELECT
    category,
    product_name,
    brand,
    total_revenue,
    total_units,
    unique_buyers,
    category_rank
FROM product_sales
WHERE category_rank <= 5
ORDER BY category, category_rank;

→ Insight: RANK() OVER (PARTITION BY category) cho phép Amazon xếp hạng sản phẩm trong từng category riêng biệt — iPhone 16 là #1 trong Electronics, không cạnh tranh xếp hạng với "Atomic Habits" #1 trong Books. Nếu 2 sản phẩm cùng doanh thu → cùng rank (RANK cho phép tie), sản phẩm tiếp theo nhảy hạng. Amazon dùng ranking này cho "Best Sellers" page trên website — cập nhật hàng giờ.

Query 3: Customer Lifetime Value phân khúc — CTE nhiều bước + Window Function

sql

-- Tính CLV (Customer Lifetime Value) theo phân khúc khách hàng
-- Star Schema: fact_orders + dim_customers + dim_dates
-- Nhiều CTE: step 1 tính metrics/customer → step 2 phân khúc → step 3 aggregate

WITH customer_metrics AS (
    -- Step 1: Tính metrics cho mỗi khách hàng
    SELECT
        c.customer_key,
        c.segment,
        c.signup_date,
        COUNT(DISTINCT f.order_key) AS total_orders,
        SUM(f.net_revenue) AS total_revenue,
        MIN(d.full_date) AS first_order_date,
        MAX(d.full_date) AS last_order_date,
        SUM(f.net_revenue) / NULLIF(COUNT(DISTINCT f.order_key), 0) AS avg_order_value
    FROM fact_orders f
    JOIN dim_customers c ON f.customer_key = c.customer_key
    JOIN dim_dates d ON f.date_key = d.date_key
    WHERE f.order_status = 'delivered'
    GROUP BY c.customer_key, c.segment, c.signup_date
),
customer_tiers AS (
    -- Step 2: Phân hạng khách hàng dựa trên CLV
    SELECT
        *,
        NTILE(5) OVER (ORDER BY total_revenue DESC) AS revenue_quintile,
        CASE
            WHEN total_revenue >= 50000000 AND total_orders >= 50 THEN 'Diamond'
            WHEN total_revenue >= 20000000 AND total_orders >= 20 THEN 'Gold'
            WHEN total_revenue >= 5000000 AND total_orders >= 10 THEN 'Silver'
            ELSE 'Bronze'
        END AS customer_tier
    FROM customer_metrics
)
-- Step 3: Tổng hợp theo tier
SELECT
    customer_tier,
    COUNT(*) AS num_customers,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_total_customers,
    SUM(total_revenue) AS tier_total_revenue,
    ROUND(SUM(total_revenue) * 100.0 / SUM(SUM(total_revenue)) OVER (), 1) AS pct_of_total_revenue,
    ROUND(AVG(total_revenue), 0) AS avg_clv,
    ROUND(AVG(avg_order_value), 0) AS avg_aov,
    ROUND(AVG(total_orders), 1) AS avg_orders
FROM customer_tiers
GROUP BY customer_tier
ORDER BY avg_clv DESC;

Kết quả ví dụ:

customer_tier	num_customers	pct_of_total_customers	tier_total_revenue	pct_of_total_revenue	avg_clv	avg_aov	avg_orders
Diamond	620,000	0.2	45,200,000,000,000	35.0	72,903,226	1,458,065	68.5
Gold	3,100,000	1.0	31,000,000,000,000	24.0	10,000,000	500,000	32.2
Silver	18,600,000	6.0	27,900,000,000,000	21.6	1,500,000	300,000	15.8
Bronze	287,680,000	92.8	25,100,000,000,000	19.4	87,248	145,000	4.2

→ Insight: 0.2% khách hàng Diamond tạo ra 35% tổng doanh thu — xác nhận "quy tắc Pareto" trong e-commerce. SUM() OVER () (không PARTITION BY) tính tổng toàn bộ → lấy % mỗi tier so với toàn bộ. CTE 3 bước giúp query dài nhưng logic rõ ràng: tính metrics → phân hạng → tổng hợp. Amazon dùng insight này để ưu tiên Prime benefits cho Diamond/Gold — giữ chân 1.2% khách hàng tạo 59% revenue.

Query 4: Phân tích doanh thu theo ngày trong tuần — dim_dates dimension table

sql

-- Tìm ngày nào trong tuần có doanh thu cao nhất — dùng sức mạnh dim_dates
-- Dimension table dim_dates có sẵn cột day_of_week, is_holiday → không cần tính

SELECT
    d.day_of_week,
    SUM(f.net_revenue) AS total_revenue,
    COUNT(DISTINCT f.order_key) AS total_orders,
    ROUND(AVG(f.total_amount), 0) AS avg_order_value,
    RANK() OVER (ORDER BY SUM(f.net_revenue) DESC) AS revenue_rank
FROM fact_orders f
JOIN dim_dates d ON f.date_key = d.date_key
WHERE d.year = 2026
  AND d.month IN (1, 2)
  AND f.order_status IN ('delivered', 'shipped')
GROUP BY d.day_of_week
ORDER BY revenue_rank;

Kết quả ví dụ:

day_of_week	total_revenue	total_orders	avg_order_value	revenue_rank
Monday	22,500,000,000,000	18,200,000	1,236,264	1
Tuesday	20,800,000,000,000	16,900,000	1,230,769	2
Sunday	19,500,000,000,000	15,800,000	1,234,177	3
Friday	18,200,000,000,000	14,600,000	1,246,575	4
Wednesday	17,800,000,000,000	14,200,000	1,253,521	5
Thursday	17,200,000,000,000	13,800,000	1,246,377	6
Saturday	16,500,000,000,000	13,100,000	1,259,542	7

→ Insight: Monday dẫn đầu doanh thu (nhiều người order đầu tuần sau khi browse cuối tuần). Saturday thấp nhất (người ta đi chơi, ít browse online). Dimension table dim_dates giúp phân tích theo day_of_week, is_holiday mà không cần hàm EXTRACT hay DATEPART — thông tin đã được ETL sẵn vào dimension. Đây chính là lợi thế của Star Schema: precomputed dimensions = query đơn giản hơn.

📊 Kết quả

Chỉ số	Trước (OLTP schema)	Sau (Star Schema Data Warehouse)
Số bảng cần JOIN cho 1 report	8–12 bảng (normalized 3NF)	2–5 bảng (1 fact + dimensions)
Thời gian query doanh thu theo category	15–30 phút (full table scan OLTP)	30 giây (columnar storage, pre-aggregated)
DA onboarding time	4–6 tuần (học 200+ bảng)	1–2 tuần (hiểu fact + 5 dimensions)
Running total / YTD calculation	Export → Excel → manual formula	SUM() OVER — 1 dòng SQL
MoM/YoY comparison	Self-JOIN phức tạp	LAG() — 1 dòng SQL
Impact đến production database	Queries nặng làm chậm checkout	Zero impact — warehouse tách biệt
Customer segmentation	Thủ công hàng quý	Real-time: CTE + NTILE → auto-segment
Phát hiện Diamond customers → retention	Ad-hoc, không hệ thống	0.2% customers = 35% revenue → focused retention program → churn giảm 18%

Star Schema Data Warehouse giúp Amazon Retail Analytics team chuyển từ 200+ bảng phức tạp sang mô hình 6–7 bảng trực quan — DA viết query nhanh hơn 10x, query chạy nhanh hơn 30x, và không ảnh hưởng đến hệ thống giao dịch. Window Functions trên Star Schema cho phép tính running total, MoM growth, customer segmentation — tất cả trong SQL mà không cần Excel.

💡 Bài học cho Data Analyst Việt Nam

Star Schema = "sơ đồ ngôi sao" — 1 fact ở giữa, dimensions bao quanh. Khi bạn vào công ty mới, bảng đầu tiên cần hỏi là "Đâu là fact table?" — đó là bảng chứa metrics (revenue, quantity, cost). Sau đó hỏi "Dimensions nào mô tả fact?" — product, customer, date, geography. Hiểu star schema = hiểu 80% data model của công ty.
Fact table chứa "sự kiện đo lường được" — Dimension chứa "mô tả". Fact: order_key, revenue, quantity (numbers). Dimension: product_name, category, customer_name (text descriptions). Rule of thumb: nếu bạn SUM/AVG/COUNT nó → nó thuộc fact. Nếu bạn GROUP BY/FILTER nó → nó thuộc dimension.
CTE nhiều bước = "recipe cooking". Step 1 chuẩn bị nguyên liệu (tính raw metrics), Step 2 chế biến (phân hạng, phân loại), Step 3 trình bày (aggregate, format). Mỗi CTE có tên → like mỗi bước trong công thức nấu ăn. Ai cũng đọc hiểu, ai cũng debug được.
Dimension table dim_dates là "vũ khí bí mật" của DA. Thay vì viết EXTRACT(DOW FROM date), CASE WHEN month IN (1,2,3) THEN 'Q1'... — dim_dates đã có sẵn: day_of_week, quarter, fiscal_year, is_holiday. ETL team build 1 lần, DA dùng mãi mãi.

Case Study 3: VNPay — Data Warehouse cho transaction analytics

🏷️ Thông tin

Tiêu chí	Chi tiết
Công ty	VNPAY (Công ty CP Giải pháp Thanh toán Việt Nam)
Ngành	Fintech — Thanh toán điện tử
Quy mô	40+ triệu người dùng active, 100,000+ điểm chấp nhận thanh toán, 60+ ngân hàng đối tác, xử lý 200+ triệu giao dịch/tháng, ~$20 tỷ USD TPV (Total Payment Volume) (2025)
Thị trường	Việt Nam — cổng thanh toán lớn nhất Đông Nam Á
Chủ đề DA liên quan	Data Warehouse, ETL, Star Schema, Running Total, LAG/LEAD, CTE, Window Functions cho fraud detection

📋 Bối cảnh

VNPay là cổng thanh toán điện tử lớn nhất Việt Nam, kết nối 60+ ngân hàng, phục vụ 40+ triệu người dùng active. Mỗi ngày, VNPay xử lý hơn 7 triệu giao dịch — từ thanh toán QR code tại cửa hàng, chuyển tiền liên ngân hàng, thanh toán hóa đơn điện/nước, đến nạp tiền điện thoại và mua vé xem phim.

Bộ phận Data & Analytics Center tại VNPay cần phân tích dữ liệu giao dịch để trả lời liên tục: "Tổng giá trị giao dịch (TPV) hôm nay bao nhiêu? So với hôm qua?", "Loại giao dịch nào tăng trưởng nhanh nhất?", "Merchant (điểm chấp nhận) nào có doanh thu cao nhất?", "Có giao dịch bất thường (fraud suspicion) nào không?", "Doanh thu tích lũy tháng này so với target?".

Ban đầu, VNPay phân tích trực tiếp trên OLTP database (PostgreSQL) phục vụ giao dịch real-time. Nhưng khi volume tăng lên 200+ triệu giao dịch/tháng, các query phân tích bắt đầu làm chậm hệ thống thanh toán — ảnh hưởng trực tiếp đến trải nghiệm người dùng đang quét QR code thanh toán. VNPay quyết định xây dựng Data Warehouse riêng biệt với star schema, quy trình ETL chuẩn hóa, và sử dụng SQL nâng cao (Window Functions, CTE) để phân tích.

⚡ Thách thức

OLTP chịu không nổi analytics queries: Database thanh toán cần respond < 200ms cho mỗi giao dịch QR code. Khi DA chạy query SUM(amount) GROUP BY merchant trên bảng 200+ triệu dòng → database chậm → user quét QR code bị timeout → mất tiền. Critical risk.
Đa dạng loại giao dịch: QR payment, bank transfer, bill payment, mobile top-up, movie ticket, airline ticket — mỗi loại có schema khác nhau. Cần chuẩn hóa vào 1 fact table thống nhất.
Fraud detection cần so sánh cross-transaction: Phát hiện giao dịch bất thường yêu cầu so sánh giao dịch hiện tại với giao dịch trước đó của cùng user — cần LAG() để xem transaction trước, tính time gap, so sánh amount. GROUP BY không làm được.
Running total cho settlement: VNPay thanh toán cho merchants hàng ngày/tuần (settlement). Cần tính running total giao dịch tích lũy đến cuối mỗi ngày = số tiền cần settle cho merchant. SUM() OVER (ORDER BY date) là giải pháp.
ETL pipeline cho freshness: Data cần ETL từ OLTP sang warehouse đủ sớm để DA phân tích vào buổi sáng (T+1). Nhưng ETL too early (2AM) → miss late-night transactions. ETL too late (8AM) → DA không có data khi bắt đầu làm việc.

🛠️ Giải pháp

Dữ liệu gì? (Star Schema cho fintech)

                         ┌────────────────────┐
                         │  dim_merchants     │
                         ├────────────────────┤
                         │ merchant_key (PK)  │
                         │ merchant_id        │
                         │ merchant_name      │
                         │ merchant_category  │
                         │ province           │
                         │ district           │
                         │ merchant_tier      │
                         └────────┬───────────┘
                                  │
┌────────────────────┐   ┌───────┴────────────────────┐   ┌────────────────────┐
│  dim_users         │   │   fact_transactions        │   │  dim_dates         │
├────────────────────┤   ├────────────────────────────┤   ├────────────────────┤
│ user_key (PK)      │──>│ txn_key (PK)               │<──│ date_key (PK)      │
│ user_id            │   │ user_key (FK)              │   │ full_date          │
│ user_name          │   │ merchant_key (FK)          │   │ day_of_week        │
│ phone_number       │   │ date_key (FK)              │   │ month              │
│ bank_partner       │   │ txn_type_key (FK)          │   │ quarter            │
│ kyc_level          │   │ ────── MEASURES ──────     │   │ year               │
│ registration_date  │   │ txn_amount                 │   │ is_weekend         │
│ user_segment       │   │ fee_amount                 │   │ is_holiday         │
└────────────────────┘   │ net_amount                 │   │ lunar_month        │
                         │ txn_status                 │   └────────────────────┘
┌────────────────────┐   │ txn_timestamp              │
│  dim_txn_types     │   │ processing_time_ms         │
├────────────────────┤   │ is_flagged_fraud           │
│ txn_type_key (PK)  │──>│ source_bank                │
│ txn_type_code      │   │ dest_bank                  │
│ txn_type_name      │   └────────────────────────────┘
│ txn_category       │
│ settlement_cycle   │
└────────────────────┘

ETL Pipeline — từ OLTP sang Data Warehouse:

┌──────────┐    Extract     ┌───────────┐    Transform    ┌──────────────┐    Load    ┌──────────────┐
│  OLTP    │  ───────────>  │  Staging  │  ────────────>  │  Clean &     │  ───────>  │   Data       │
│  (Postgres│  (5AM daily)  │   Area    │  (Map to star   │  Conform     │           │  Warehouse   │
│   200M+  │               │           │   schema keys)  │  (De-dup,    │           │  (Star Schema│
│   txn/mo)│               │           │                 │   validate)  │           │   6 tables)  │
└──────────┘               └───────────┘                 └──────────────┘           └──────────────┘
                                                                                          │
                                                                                    DA queries
                                                                                    (7AM ready)

Extract (5:00 AM): Pull incremental data từ OLTP — chỉ lấy giao dịch mới từ lần ETL trước
Transform (5:30 AM): Map merchant_id → merchant_key (surrogate key), generate date_key, standardize txn_type, de-duplicate, validate amounts
Load (6:30 AM): Insert vào fact_transactions và update dimension tables (SCD Type 2 cho dim_merchants — lưu lịch sử thay đổi)
Ready (7:00 AM): DA bắt đầu query — data T-1 (yesterday) hoàn chỉnh

Phân tích bằng SQL query nào?

Query 1: Daily TPV + Running total tích lũy — SUM() OVER + CTE

sql

-- Tổng giá trị giao dịch (TPV) theo ngày + running total tích lũy tháng
-- Mục tiêu: theo dõi tiến độ TPV so với target tháng

WITH daily_tpv AS (
    SELECT
        d.full_date,
        d.day_of_week,
        d.is_weekend,
        SUM(f.txn_amount) AS daily_tpv,
        COUNT(*) AS daily_txn_count,
        COUNT(DISTINCT f.user_key) AS daily_active_users
    FROM fact_transactions f
    JOIN dim_dates d ON f.date_key = d.date_key
    WHERE d.year = 2026
      AND d.month = 2
      AND f.txn_status = 'success'
    GROUP BY d.full_date, d.day_of_week, d.is_weekend
)
SELECT
    full_date,
    day_of_week,
    is_weekend,
    daily_tpv,
    daily_txn_count,
    daily_active_users,
    SUM(daily_tpv) OVER (
        ORDER BY full_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS mtd_cumulative_tpv,
    SUM(daily_txn_count) OVER (
        ORDER BY full_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS mtd_cumulative_txn,
    LAG(daily_tpv, 1) OVER (ORDER BY full_date) AS prev_day_tpv,
    ROUND(
        (daily_tpv - LAG(daily_tpv, 1) OVER (ORDER BY full_date)) * 100.0 /
        NULLIF(LAG(daily_tpv, 1) OVER (ORDER BY full_date), 0), 1
    ) AS dod_growth_pct
FROM daily_tpv
ORDER BY full_date;

Kết quả ví dụ (trích):

full_date	day_of_week	daily_tpv	daily_txn_count	daily_active_users	mtd_cumulative_tpv	prev_day_tpv	dod_growth_pct
2026-02-14	Saturday	580,000,000,000	5,800,000	2,100,000	8,120,000,000,000	720,000,000,000	-19.4
2026-02-15	Sunday	520,000,000,000	5,200,000	1,900,000	8,640,000,000,000	580,000,000,000	-10.3
2026-02-16	Monday	780,000,000,000	7,500,000	2,800,000	9,420,000,000,000	520,000,000,000	50.0
2026-02-17	Tuesday	750,000,000,000	7,200,000	2,700,000	10,170,000,000,000	780,000,000,000	-3.8

→ Insight: Cuối tuần (Sat-Sun) TPV giảm 10–19% so với ngày thường — fewer business transactions. Monday spike +50% vs Sunday (back-to-work effect). MTD cumulative TPV đạt 10.17 nghìn tỷ VNĐ sau 17/28 ngày (tháng 2) = 60.7% → target tháng ~16.7 nghìn tỷ → on track. Running total giúp ban lãnh đạo theo dõi tiến độ realtime mà không cần đợi cuối tháng.

Query 2: Phân tích TPV theo loại giao dịch + tỷ trọng — SUM() OVER + Percent of Total

sql

-- Phân tích TPV theo loại giao dịch + tỷ trọng mỗi loại trong tổng
-- Window Function: SUM() OVER () = tổng không partition = tổng toàn bộ

SELECT
    tt.txn_type_name,
    tt.txn_category,
    SUM(f.txn_amount) AS total_tpv,
    COUNT(*) AS total_txns,
    COUNT(DISTINCT f.user_key) AS unique_users,
    ROUND(
        SUM(f.txn_amount) * 100.0 /
        SUM(SUM(f.txn_amount)) OVER (), 2
    ) AS tpv_share_pct,
    ROUND(
        COUNT(*) * 100.0 /
        SUM(COUNT(*)) OVER (), 2
    ) AS txn_share_pct,
    RANK() OVER (ORDER BY SUM(f.txn_amount) DESC) AS tpv_rank
FROM fact_transactions f
JOIN dim_txn_types tt ON f.txn_type_key = tt.txn_type_key
JOIN dim_dates d ON f.date_key = d.date_key
WHERE d.year = 2026
  AND d.month = 2
  AND f.txn_status = 'success'
GROUP BY tt.txn_type_name, tt.txn_category
ORDER BY tpv_rank;

Kết quả ví dụ:

txn_type_name	txn_category	total_tpv	total_txns	unique_users	tpv_share_pct	txn_share_pct	tpv_rank
Bank Transfer	Transfer	6,800,000,000,000	42,000,000	8,500,000	40.72	19.63	1
QR Payment	Payment	4,200,000,000,000	85,000,000	15,200,000	25.15	39.72	2
Bill Payment	Payment	2,500,000,000,000	35,000,000	12,000,000	14.97	16.36	3
E-commerce	Payment	1,800,000,000,000	28,000,000	9,800,000	10.78	13.08	4
Mobile Top-up	Top-up	900,000,000,000	18,000,000	7,500,000	5.39	8.41	5
Others	Misc	500,000,000,000	6,000,000	3,000,000	2.99	2.80	6

→ Insight: Bank Transfer chiếm 40.72% TPV nhưng chỉ 19.63% số giao dịch — vì giá trị/giao dịch cao (chuyển khoản lớn). QR Payment ngược lại: 25.15% TPV nhưng 39.72% số giao dịch — nhiều giao dịch nhỏ (mua cà phê, ăn trưa). SUM() OVER () tính tổng toàn bộ → chia ra % cho mỗi loại. VNPay dùng insight này để ưu tiên phát triển QR Payment (high frequency → high user engagement) và tối ưu fee structure cho Bank Transfer (high value → high revenue per txn).

Query 3: Fraud detection — LAG() so sánh cross-transaction

sql

-- Phát hiện giao dịch bất thường: so sánh giao dịch hiện tại với giao dịch trước
-- của CÙNG user — tìm pattern: nhiều giao dịch liên tiếp trong thời gian ngắn,
-- hoặc amount đột ngột tăng gấp 10x

WITH user_txn_sequence AS (
    SELECT
        f.txn_key,
        f.user_key,
        u.user_name,
        u.phone_number,
        f.txn_amount,
        f.txn_timestamp,
        f.merchant_key,
        LAG(f.txn_amount, 1) OVER (
            PARTITION BY f.user_key
            ORDER BY f.txn_timestamp
        ) AS prev_txn_amount,
        LAG(f.txn_timestamp, 1) OVER (
            PARTITION BY f.user_key
            ORDER BY f.txn_timestamp
        ) AS prev_txn_timestamp,
        LAG(f.merchant_key, 1) OVER (
            PARTITION BY f.user_key
            ORDER BY f.txn_timestamp
        ) AS prev_merchant_key,
        ROW_NUMBER() OVER (
            PARTITION BY f.user_key
            ORDER BY f.txn_timestamp
        ) AS txn_sequence_num
    FROM fact_transactions f
    JOIN dim_users u ON f.user_key = u.user_key
    JOIN dim_dates d ON f.date_key = d.date_key
    WHERE d.full_date = '2026-02-17'
      AND f.txn_status = 'success'
)
SELECT
    txn_key,
    user_key,
    user_name,
    txn_amount,
    txn_timestamp,
    prev_txn_amount,
    prev_txn_timestamp,
    EXTRACT(EPOCH FROM (txn_timestamp - prev_txn_timestamp)) AS seconds_since_prev,
    ROUND(txn_amount / NULLIF(prev_txn_amount, 0), 1) AS amount_ratio,
    CASE
        WHEN EXTRACT(EPOCH FROM (txn_timestamp - prev_txn_timestamp)) < 60
             AND txn_amount > 5000000
        THEN '🔴 RAPID HIGH-VALUE'
        WHEN txn_amount / NULLIF(prev_txn_amount, 0) > 10
        THEN '🟡 AMOUNT SPIKE (>10x)'
        WHEN EXTRACT(EPOCH FROM (txn_timestamp - prev_txn_timestamp)) < 30
        THEN '🟠 RAPID SEQUENCE (<30s)'
        ELSE '🟢 NORMAL'
    END AS fraud_flag
FROM user_txn_sequence
WHERE prev_txn_timestamp IS NOT NULL
  AND (
    EXTRACT(EPOCH FROM (txn_timestamp - prev_txn_timestamp)) < 60
    OR txn_amount / NULLIF(prev_txn_amount, 0) > 10
  )
ORDER BY txn_timestamp;

→ Insight: LAG() cho phép so sánh mỗi giao dịch với giao dịch ngay trước đó của cùng user — tính time gap (seconds_since_prev) và amount ratio. Pattern bất thường: user thực hiện 5 giao dịch trong 2 phút, mỗi giao dịch 4.9 triệu VNĐ (ngay dưới ngưỡng 5 triệu cần xác thực OTP bổ sung) → flag "RAPID SEQUENCE" → gửi fraud review team. Trung bình VNPay phát hiện ~500 giao dịch bất thường/ngày bằng pattern này, trong đó ~15% là fraud thật → chặn ~2 tỷ VNĐ/tháng.

Query 4: Merchant settlement — Running total cho đối soát

sql

-- Tính running total giao dịch tích lũy cho merchant settlement
-- Merchant cần biết: "Ngày hôm nay VNPay nợ tôi bao nhiêu (tích lũy)?"

WITH merchant_daily AS (
    SELECT
        m.merchant_key,
        m.merchant_name,
        m.merchant_category,
        d.full_date,
        SUM(f.txn_amount) AS daily_gross,
        SUM(f.fee_amount) AS daily_fee,
        SUM(f.net_amount) AS daily_net
    FROM fact_transactions f
    JOIN dim_merchants m ON f.merchant_key = m.merchant_key
    JOIN dim_dates d ON f.date_key = d.date_key
    WHERE m.merchant_key = 12345      -- VinMart (ví dụ)
      AND d.year = 2026
      AND d.month = 2
      AND f.txn_status = 'success'
    GROUP BY m.merchant_key, m.merchant_name, m.merchant_category, d.full_date
)
SELECT
    merchant_name,
    full_date,
    daily_gross,
    daily_fee,
    daily_net,
    SUM(daily_net) OVER (
        ORDER BY full_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_settlement,
    LAG(daily_net, 7) OVER (ORDER BY full_date) AS same_day_last_week,
    ROUND(
        (daily_net - LAG(daily_net, 7) OVER (ORDER BY full_date)) * 100.0 /
        NULLIF(LAG(daily_net, 7) OVER (ORDER BY full_date), 0), 1
    ) AS wow_growth_pct
FROM merchant_daily
ORDER BY full_date;

Kết quả ví dụ (VinMart):

merchant_name	full_date	daily_gross	daily_fee	daily_net	cumulative_settlement	same_day_last_week	wow_growth_pct
VinMart	2026-02-15	2,800,000,000	28,000,000	2,772,000,000	38,256,000,000	2,550,000,000	8.7
VinMart	2026-02-16	3,500,000,000	35,000,000	3,465,000,000	41,721,000,000	3,200,000,000	8.3
VinMart	2026-02-17	3,200,000,000	32,000,000	3,168,000,000	44,889,000,000	2,980,000,000	6.3

→ Insight: Cumulative settlement cho biết VNPay nợ VinMart 44.89 tỷ VNĐ tính đến 17/02. LAG(daily_net, 7) so sánh với cùng thứ tuần trước (WoW) — VinMart tăng trưởng 6–9% WoW. Running total giúp finance team VNPay biết chính xác số tiền cần chuyển cho merchant khi settle — không cần export Excel tính tay.

Query 5: User activity trend — LEAD() dự báo churn

sql

-- Phân tích user activity: khoảng cách giữa các giao dịch
-- Nếu khoảng cách ngày càng dài → user đang dần inactive → churn risk

WITH user_activity AS (
    SELECT
        f.user_key,
        u.user_name,
        u.user_segment,
        d.full_date AS txn_date,
        ROW_NUMBER() OVER (
            PARTITION BY f.user_key
            ORDER BY d.full_date
        ) AS txn_order,
        LEAD(d.full_date, 1) OVER (
            PARTITION BY f.user_key
            ORDER BY d.full_date
        ) AS next_txn_date,
        LAG(d.full_date, 1) OVER (
            PARTITION BY f.user_key
            ORDER BY d.full_date
        ) AS prev_txn_date
    FROM fact_transactions f
    JOIN dim_users u ON f.user_key = u.user_key
    JOIN dim_dates d ON f.date_key = d.date_key
    WHERE d.year = 2026
      AND d.month IN (1, 2)
      AND f.txn_status = 'success'
    GROUP BY f.user_key, u.user_name, u.user_segment, d.full_date
)
SELECT
    user_segment,
    COUNT(DISTINCT user_key) AS total_users,
    ROUND(AVG(next_txn_date - txn_date), 1) AS avg_days_between_txn,
    COUNT(CASE WHEN next_txn_date - txn_date > 14 THEN 1 END) AS gaps_over_14_days,
    COUNT(CASE WHEN next_txn_date IS NULL THEN 1 END) AS no_next_txn,
    ROUND(
        COUNT(CASE WHEN next_txn_date IS NULL THEN 1 END) * 100.0 /
        NULLIF(COUNT(DISTINCT user_key), 0), 1
    ) AS potential_churn_pct
FROM user_activity
GROUP BY user_segment
ORDER BY potential_churn_pct DESC;

→ Insight: LEAD() cho phép nhìn "tương lai" — biết giao dịch tiếp theo xảy ra khi nào. Nếu LEAD() trả NULL → user không có giao dịch nào tiếp theo (trong dữ liệu hiện có) → potential churn. Kết hợp LAG() (nhìn quá khứ) và LEAD() (nhìn tương lai), VNPay xây dựng churn prediction model: user có avg_days_between_txn tăng dần + last_txn > 14 ngày → gửi push notification hoặc cashback voucher. Campaign win-back dựa trên insight này recovery 12% users at-risk.

📊 Kết quả

Chỉ số	Trước (Query OLTP trực tiếp)	Sau (Data Warehouse + Window Functions)
Ảnh hưởng production	Query analytics làm chậm QR payment 300–500ms	Zero impact — warehouse tách biệt
Thời gian daily TPV report	45 phút (manual query + Excel)	5 phút (automated running total query)
Fraud detection	Cuối ngày (batch review)	Near real-time: LAG() pattern → flag trong 15 phút
Merchant settlement accuracy	±2% sai lệch (tính tay)	<0.01% (running total SQL tự động)
Số giao dịch fraud bị chặn/tháng	~100 (manual review)	~500 flagged → ~75 confirmed → chặn ~2 tỷ VNĐ/tháng
Churn prediction	Không có (reactive)	Proactive: LEAD() + LAG() → 12% user win-back
DA onboarding time	6 tuần (học OLTP schema phức tạp)	2 tuần (star schema 6 bảng)
ETL freshness	Ad-hoc export	T+1 automated (5AM extract → 7AM ready)

Data Warehouse với Star Schema giúp VNPay Data & Analytics Center tách biệt analytics khỏi production, chuẩn hóa 200+ triệu giao dịch/tháng vào mô hình 6 bảng rõ ràng, và sử dụng Window Functions cho phân tích chuyên sâu: running total cho settlement, LAG/LEAD cho fraud detection và churn prediction, SUM() OVER cho tỷ trọng giao dịch.

💡 Bài học cho Data Analyst Việt Nam

Không bao giờ chạy analytics query trên production database. Bài học #1 của VNPay: 1 query GROUP BY trên 200 triệu dòng làm chậm QR payment → user mất tiền. Data Warehouse tồn tại để bảo vệ production — và cho DA thoải mái query mà không sợ "làm sập hệ thống".
ETL là "đường ống dẫn dầu" — data warehouse không tự có dữ liệu. Luôn hỏi: "Dữ liệu trong warehouse được cập nhật khi nào? T+1 hay T+0? Incremental hay full load?" — điều này ảnh hưởng trực tiếp đến kết quả phân tích. Nếu data T+1, đừng trả lời câu hỏi "hôm nay bao nhiêu?" — bạn chỉ có data hôm qua.
LAG() + LEAD() = "cặp đôi quyền lực" cho fintech analytics. LAG(amount) so sánh giao dịch hiện tại với trước — phát hiện spike (fraud). LEAD(date) biết giao dịch tiếp theo khi nào — dự báo churn. Kết hợp cả hai = hiểu hành vi user theo timeline, điều mà GROUP BY không thể làm.
Star Schema cho fintech: Fact = giao dịch, Dimensions = user + merchant + date + txn_type. Mọi startup fintech Việt Nam (MoMo, ZaloPay, VNPay) đều cần mô hình này. Nếu bạn vào công ty fintech, hỏi ngay: "Fact table nào chứa transactions? Dimension nào mô tả merchants?" → bạn nắm 80% data model trong ngày đầu tiên.

📝 So sánh & tổng hợp

Tiêu chí	Spotify	Amazon	VNPay
Ngành	Music Streaming	E-commerce	Fintech — Thanh toán điện tử
Quy mô dữ liệu	~12.6 tỷ streams/tuần	~hàng tỷ orders/năm (petabyte)	~200 triệu giao dịch/tháng
Data Architecture	Aggregate tables + Window Functions	Star Schema Data Warehouse (Redshift)	Star Schema Data Warehouse + ETL pipeline
Fact table chính	weekly_streams	fact_orders	fact_transactions
Dimension tables	tracks, artists, countries	dim_products, dim_customers, dim_dates, dim_sellers, dim_marketplace	dim_users, dim_merchants, dim_dates, dim_txn_types
Window Function nổi bật	ROW_NUMBER, DENSE_RANK, LAG, SUM() OVER	RANK, SUM() OVER (running total), NTILE, LAG	SUM() OVER (running total), LAG, LEAD, ROW_NUMBER
CTE dùng cho	Weekly ranking + trend comparison	Customer CLV multi-step + category analysis	Daily TPV + fraud detection sequence
Business question chính	Top songs per region per week, viral detection	Revenue by category (YTD), customer segmentation, product ranking	TPV tracking, fraud detection, merchant settlement, churn prediction
Kết quả nổi bật	180+ country charts trong 1 query, +200–400 viral detections/tuần	10x faster query, customer tiers: 0.2% = 35% revenue, churn -18%	Chặn ~2 tỷ VNĐ fraud/tháng, settlement accuracy < 0.01%, 12% churn win-back

Điểm chung của cả 3 case study:

Đều sử dụng Window Functions để giải quyết bài toán mà GROUP BY không làm được: ranking per group (PARTITION BY), so sánh dòng trước/sau (LAG/LEAD), running total (SUM OVER), và percent of total.
Đều chứng minh Star Schema là kiến trúc chuẩn cho analytics — từ music streaming (aggregate tables giống mini star schema) đến e-commerce (classic star schema) đến fintech (star schema + ETL). Fact table chứa metrics, dimension tables chứa mô tả — mọi query phân tích xoay quanh JOIN fact ↔ dimensions.
Đều dùng CTE để viết query "sạch" — chia query dài thành từng bước có tên, dễ đọc, dễ debug. Không ai viết subquery lồng 5 tầng khi làm việc nhóm — CTE là chuẩn team collaboration.
Đều cho thấy Window Functions không thay thế GROUP BY — chúng bổ sung. GROUP BY tổng hợp (tổng revenue per category), Window Functions bổ sung thêm thông tin so sánh trên mỗi dòng (rank, running total, prev value). Kết hợp cả hai = phân tích đầy đủ.
Đều nhấn mạnh: Data Warehouse tách biệt khỏi production database là bắt buộc ở quy mô lớn. Analytics queries trên OLTP = rủi ro kinh doanh (chậm checkout, chậm payment). Data Warehouse = an toàn + tối ưu cho DA.

🤔 Câu hỏi thảo luận

ROW_NUMBER vs RANK vs DENSE_RANK: Trong case Spotify, nếu 2 bài hát cùng 15.2 triệu streams ở Việt Nam, ROW_NUMBER, RANK, và DENSE_RANK sẽ cho kết quả rank khác nhau như thế nào? Hãy viết ví dụ cụ thể với 5 bài hát (trong đó 2 bài tie) — liệt kê kết quả rank cho cả 3 hàm.
Star Schema design: Nếu bạn thiết kế star schema cho chuỗi cà phê Highland Coffee (có ~600 cửa hàng toàn quốc), đâu là fact table? Dimensions nào cần có? Hãy vẽ sơ đồ star schema với tên bảng và cột chính, xác định rõ measures (trong fact) và attributes (trong dimensions).
LAG() cho fraud detection: VNPay dùng LAG() để so sánh giao dịch hiện tại với giao dịch trước. Nếu bạn muốn phát hiện pattern "3 giao dịch liên tiếp trong 5 phút", bạn cần dùng LAG() với tham số gì? (Gợi ý: LAG(column, 2) → quay lại 2 dòng). Hãy viết pseudo-SQL.
Running total ứng dụng: Một startup Việt Nam có target doanh thu tháng 2/2026 là 5 tỷ VNĐ. Bạn có bảng daily_sales(sale_date, revenue). Hãy viết SQL tính running total revenue tích lũy theo ngày + cột pct_of_target (% so với target 5 tỷ) + cột days_remaining bằng Window Functions.
ETL freshness vs accuracy: VNPay ETL data T+1 (5AM). Nếu sếp hỏi lúc 3PM hôm nay: "TPV hôm nay bao nhiêu rồi?" — bạn trả lời thế nào? Data warehouse chỉ có data đến hôm qua. Bạn có nên query OLTP trực tiếp không? Giải thích trade-off giữa freshness và safety.

📚 Nguồn tham khảo

Spotify Engineering Blog. "How Spotify Generates Its Weekly Charts." — Kiến trúc data pipeline phục vụ global weekly charts.
Amazon Web Services (AWS). "Amazon Redshift — Data Warehouse Service." — Kiến trúc Redshift star schema cho analytics petabyte-scale.
VNPay. "Báo cáo hoạt động thanh toán điện tử Việt Nam." — Thông tin về hệ sinh thái thanh toán VNPay và quy mô giao dịch.
Kimball, R. & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. 3rd Edition. Wiley. — "Kinh thánh" về star schema, fact vs dimension, slowly changing dimensions.
Molinaro, A. & Graaf, R. (2020). SQL Cookbook. O'Reilly Media. — Patterns SQL nâng cao: window functions, CTE, analytical queries.
DataLemur (2025). "Top SQL Interview Questions — Window Functions." — Bộ câu hỏi phỏng vấn DA phổ biến nhất: ROW_NUMBER, RANK, LAG, running total.
Inmon, W.H. (2005). Building the Data Warehouse. 4th Edition. Wiley. — Kiến trúc data warehouse chuẩn, ETL process, data quality.
Mode Analytics SQL Tutorial. mode.com/sql-tutorial/sql-window-functions — Tutorial tương tác Window Functions với datasets thực tế.
CafeBiz, VnExpress, TechInAsia — Các bài viết về VNPay, MoMo, ZaloPay và hệ sinh thái fintech Việt Nam.
PostgreSQL Documentation — Window Functions. postgresql.org/docs/current/tutorial-window.html — Tài liệu chính thức PostgreSQL về Window Functions.

🔗 Xem thêm Buổi 6

→ 📘 Nội dung chính → 📝 Blog → 🏆 Tiêu chuẩn → 🛠 Workshop → 🎮 Mini Game

🧠 Case Study Buổi 6: SQL — Window Function & Data Warehouse ​

Tổng quan ​

Case Study 1: Spotify — Window Function cho ranking top songs per region per week ​

🏷️ Thông tin ​

📋 Bối cảnh ​

⚡ Thách thức ​

🛠️ Giải pháp ​

📊 Kết quả ​

💡 Bài học cho Data Analyst Việt Nam ​

Case Study 2: Amazon — Star Schema cho retail analytics quy mô petabyte ​

🏷️ Thông tin ​

📋 Bối cảnh ​

⚡ Thách thức ​

🛠️ Giải pháp ​

📊 Kết quả ​

💡 Bài học cho Data Analyst Việt Nam ​

Case Study 3: VNPay — Data Warehouse cho transaction analytics ​

🏷️ Thông tin ​

📋 Bối cảnh ​

⚡ Thách thức ​

🛠️ Giải pháp ​

📊 Kết quả ​

💡 Bài học cho Data Analyst Việt Nam ​

📝 So sánh & tổng hợp ​

🤔 Câu hỏi thảo luận ​

📚 Nguồn tham khảo ​

🔗 Xem thêm Buổi 6 ​

🧠 Case Study Buổi 6: SQL — Window Function & Data Warehouse

Tổng quan

Case Study 1: Spotify — Window Function cho ranking top songs per region per week

🏷️ Thông tin

📋 Bối cảnh

⚡ Thách thức

🛠️ Giải pháp

📊 Kết quả

💡 Bài học cho Data Analyst Việt Nam

Case Study 2: Amazon — Star Schema cho retail analytics quy mô petabyte

🏷️ Thông tin

📋 Bối cảnh

⚡ Thách thức

🛠️ Giải pháp

📊 Kết quả

💡 Bài học cho Data Analyst Việt Nam

Case Study 3: VNPay — Data Warehouse cho transaction analytics

🏷️ Thông tin

📋 Bối cảnh

⚡ Thách thức

🛠️ Giải pháp

📊 Kết quả

💡 Bài học cho Data Analyst Việt Nam

📝 So sánh & tổng hợp

🤔 Câu hỏi thảo luận

📚 Nguồn tham khảo

🔗 Xem thêm Buổi 6