12.12 - Review and Assessment

Mục tiêu bài học

Nắm được ý chính của bài và mối liên hệ với module.
Áp dụng được kiến thức vào bối cảnh CRM/.NET backend.
Sẵn sàng chuyển sang bài kế tiếp với nền tảng chắc chắn.

Nội dung bài học

12.12.1 — Thực hành

Bài 1: Tạo Full CRM Schema

Tạo database CrmDb và toàn bộ schema từ phần 1, thêm các constraint và index sau:

sql

-- Tạo database
CREATE DATABASE CrmDb;
USE CrmDb;

-- (chạy tất cả CREATE TABLE từ phần 1)

-- Thêm indexes cơ bản sau khi tạo bảng
CREATE NONCLUSTERED INDEX IX_Customers_Status_AssignedTo
    ON Customers (Status, AssignedToUserId)
    INCLUDE (CompanyName, CreatedAt);

CREATE NONCLUSTERED INDEX IX_Leads_Status_CreatedAt
    ON Leads (Status, CreatedAt DESC)
    INCLUDE (LeadId, CompanyName, EstimatedValue, AssignedToUserId);

CREATE NONCLUSTERED INDEX IX_Leads_AssignedUser
    ON Leads (AssignedToUserId, Status);

CREATE NONCLUSTERED INDEX IX_Contacts_CustomerId
    ON Contacts (CustomerId, IsPrimary);

CREATE NONCLUSTERED INDEX IX_Notifications_UserId_IsRead
    ON Notifications (UserId, IsRead, CreatedAt DESC);

-- Test insert dữ liệu mẫu
INSERT INTO Users (Email, FullName, Role) VALUES
    ('[email protected]',   N'Nguyễn Thị Alice',  'Manager'),
    ('[email protected]',     N'Trần Văn Bob',       'SalesRep'),
    ('[email protected]', N'Lê Thị Charlie',     'SalesRep');

INSERT INTO Customers (CompanyName, Industry, AnnualRevenue, AssignedToUserId) VALUES
    (N'Công ty TNHH ABC', N'Công nghệ',     500000, 2),
    (N'Tập đoàn XYZ',     N'Bất động sản', 2000000, 1),
    (N'Startup DEF',       N'Fintech',       150000, 3);

-- Thêm 20 leads mẫu với status khác nhau (tự viết hoặc dùng script generate)

Yêu cầu kiểm tra:

Thử insert Lead với Status không hợp lệ → phải báo lỗi constraint
Thử xóa User đang được assign Lead → Lead.AssignedToUserId thành NULL
Thử xóa Customer đang có Contact → Contact bị xóa theo (CASCADE)

Bài 2: Viết 5 Queries Thống kê CRM

Query 1 — Lead Conversion Rate theo Sales Rep:

sql

SELECT  u.FullName AS SalesRep,
        COUNT(l.LeadId)                                         AS TotalLeads,
        SUM(CASE WHEN l.Status = 'Won' THEN 1 ELSE 0 END)       AS WonLeads,
        SUM(CASE WHEN l.Status = 'Lost' THEN 1 ELSE 0 END)      AS LostLeads,
        ROUND(
            100.0 * SUM(CASE WHEN l.Status = 'Won' THEN 1 ELSE 0 END)
                  / NULLIF(COUNT(l.LeadId), 0),
            1
        )                                                        AS ConversionRatePct,
        ISNULL(SUM(CASE WHEN l.Status = 'Won' THEN l.EstimatedValue END), 0)
                                                                AS TotalRevenue
FROM    Users u
LEFT JOIN Leads l ON u.UserId = l.AssignedToUserId
WHERE   u.Role = 'SalesRep'
GROUP   BY u.UserId, u.FullName
ORDER   BY TotalRevenue DESC;

Query 2 — Top Sales Rep theo tháng (Window Function):

sql

WITH MonthlySales AS (
    SELECT  u.UserId, u.FullName,
            FORMAT(l.ConvertedAt, 'yyyy-MM')            AS YearMonth,
            COUNT(*)                                     AS WonCount,
            SUM(l.EstimatedValue)                        AS Revenue
    FROM    Users u
    JOIN    Leads l ON u.UserId = l.AssignedToUserId
    WHERE   l.Status = 'Won' AND l.ConvertedAt IS NOT NULL
    GROUP   BY u.UserId, u.FullName, FORMAT(l.ConvertedAt, 'yyyy-MM')
)
SELECT  YearMonth, FullName, WonCount, Revenue,
        RANK() OVER (PARTITION BY YearMonth ORDER BY Revenue DESC) AS RankInMonth
FROM    MonthlySales
ORDER   BY YearMonth DESC, RankInMonth;

Query 3 — Monthly Revenue với MoM Growth:

sql

WITH Monthly AS (
    SELECT  FORMAT(ConvertedAt, 'yyyy-MM')  AS YearMonth,
            SUM(EstimatedValue)             AS Revenue
    FROM    Leads
    WHERE   Status = 'Won' AND ConvertedAt IS NOT NULL
    GROUP   BY FORMAT(ConvertedAt, 'yyyy-MM')
)
SELECT  YearMonth,
        Revenue,
        LAG(Revenue) OVER (ORDER BY YearMonth)  AS PrevRevenue,
        ROUND(
            100.0 * (Revenue - LAG(Revenue) OVER (ORDER BY YearMonth))
                  / NULLIF(LAG(Revenue) OVER (ORDER BY YearMonth), 0),
            1
        )                                        AS GrowthPct
FROM    Monthly
ORDER   BY YearMonth;

Query 4 — Lead Aging Report (leads quá hạn theo stage):

sql

SELECT  Status,
        COUNT(*)                                        AS Count,
        AVG(DATEDIFF(DAY, CreatedAt, GETUTCDATE()))     AS AvgAgeDays,
        MAX(DATEDIFF(DAY, CreatedAt, GETUTCDATE()))     AS MaxAgeDays,
        SUM(CASE WHEN DATEDIFF(DAY, CreatedAt, GETUTCDATE()) > 30 THEN 1 ELSE 0 END)
                                                        AS Overdue30Days
FROM    Leads
WHERE   Status NOT IN ('Won', 'Lost')
GROUP   BY Status
ORDER   BY AvgAgeDays DESC;

Query 5 — Customer Lifetime Value (CLV) ước tính:

sql

WITH CustomerRevenue AS (
    SELECT  c.CustomerId,
            c.CompanyName,
            c.CreatedAt                             AS CustomerSince,
            COUNT(l.LeadId)                          AS TotalDeals,
            SUM(l.EstimatedValue)                    AS TotalRevenue,
            MAX(l.ConvertedAt)                       AS LastDealDate
    FROM    Customers c
    LEFT JOIN Leads l
        ON l.ConvertedToCustomerId = c.CustomerId AND l.Status = 'Won'
    GROUP   BY c.CustomerId, c.CompanyName, c.CreatedAt
)
SELECT  CustomerId, CompanyName,
        TotalDeals, TotalRevenue,
        DATEDIFF(MONTH, CustomerSince, GETUTCDATE())    AS TenureMonths,
        ROUND(
            TotalRevenue
            / NULLIF(DATEDIFF(MONTH, CustomerSince, GETUTCDATE()), 0),
            0
        )                                               AS AvgMonthlyRevenue,
        ROUND(
            (TotalRevenue
            / NULLIF(DATEDIFF(MONTH, CustomerSince, GETUTCDATE()), 0))
            * 24,   -- ước tính 24 tháng tới
            0
        )                                               AS EstimatedCLV_24M
FROM    CustomerRevenue
ORDER   BY EstimatedCLV_24M DESC;

Bài 3: Optimize Query Chậm

Tình huống: Query báo cáo dashboard chạy 8 giây mỗi lần load:

sql

-- Query gốc — CHẬM
SELECT  u.FullName,
        COUNT(*) AS TotalLeads,
        SUM(l.EstimatedValue) AS Pipeline
FROM    Leads l
JOIN    Users u ON l.AssignedToUserId = u.UserId
WHERE   YEAR(l.CreatedAt) = YEAR(GETDATE())
  AND   l.Status NOT IN ('Won', 'Lost')
  AND   UPPER(u.Role) = 'SALESREP'
GROUP   BY u.UserId, u.FullName
ORDER   BY Pipeline DESC;

Bước 1 — Đọc execution plan:

sql

SET STATISTICS IO ON;
-- Chạy query trên trong SSMS với Actual Execution Plan
-- Quan sát: Index Scan thay vì Seek trên Leads.CreatedAt và Users.Role
-- Logical reads cao bất thường

Bước 2 — Rewrite SARGable:

sql

-- YEAR(l.CreatedAt) không SARGable → rewrite thành range
-- UPPER(u.Role) không SARGable → lưu Role nhất quán, so sánh trực tiếp

DECLARE @YearStart  DATETIME2 = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);
DECLARE @YearEnd    DATETIME2 = DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1);

SELECT  u.FullName,
        COUNT(*)              AS TotalLeads,
        SUM(l.EstimatedValue) AS Pipeline
FROM    Leads l
JOIN    Users u ON l.AssignedToUserId = u.UserId
WHERE   l.CreatedAt >= @YearStart
  AND   l.CreatedAt <  @YearEnd
  AND   l.Status NOT IN ('Won', 'Lost')
  AND   u.Role = 'SalesRep'       -- lưu nhất quán, không cần UPPER()
GROUP   BY u.UserId, u.FullName
ORDER   BY Pipeline DESC;

Bước 3 — Thêm index phù hợp:

sql

-- Covering index cho Leads
CREATE NONCLUSTERED INDEX IX_Leads_Dashboard
    ON Leads (CreatedAt, Status)
    INCLUDE (AssignedToUserId, EstimatedValue)
    WHERE Status NOT IN ('Won', 'Lost');   -- filtered index

-- Index cho Users.Role (nếu filter thường xuyên)
CREATE NONCLUSTERED INDEX IX_Users_Role
    ON Users (Role)
    INCLUDE (UserId, FullName);

Bước 4 — Kiểm tra lại:

sql

SET STATISTICS IO ON;
-- Chạy lại query đã rewrite
-- Xác nhận: Index Seek xuất hiện, logical reads giảm đáng kể
-- So sánh execution plan trước và sau

Kết quả kỳ vọng: Giảm từ ~8 giây xuống dưới 100ms với bảng 500,000 rows.

12.12.2 — Tóm tắt

Chủ đề	Điểm cốt lõi
Schema Design	3NF là baseline; denormalize có lý do; dùng INT IDENTITY cho PK
SQL Server so với PostgreSQL	SQL Server cho enterprise Windows stack; Postgres cho open source, JSON mạnh
Advanced Queries	CTE > nested subquery; window function cho ranking/running total
Indexing	Covering index loại bỏ key lookup; filtered index cho subset thường query
Query Optimization	SARGable là bắt buộc; đọc execution plan trước khi đoán
Transactions	Isolation level ảnh hưởng lớn đến concurrency; RCSI là default tốt
Stored Procedures	Dùng cho batch phức tạp; parameterized là bắt buộc
Migration	Expand-contract pattern cho zero-downtime; không sửa tay production

Module tiếp theo — Module 13: Entity Framework Core — sẽ kết nối database này với ASP.NET Core qua ORM, tận dụng toàn bộ kiến thức schema và indexing từ module này.

12.12.3 — Checklist trước khi sang bài tiếp theo

Bạn đã nắm được các khái niệm chính và giải thích lại được bằng ví dụ của riêng mình.
Bạn đã chạy hoặc mô phỏng ít nhất một ví dụ trong bài.
Bạn đã hoàn thành phần quiz/lab cơ bản (hoặc ít nhất tự làm lại theo trí nhớ).
Bạn đã ghi lại 3 ý chính: học được gì, còn vướng gì, sẽ áp dụng ở đâu trong CRM.

12.12.4 — Kiểm tra & Thực hành (100 điểm)

12.12.5 — Bối cảnh giáo trình (mục tiêu xuyên suốt)

Trục CRM — một mục tiêu rõ: bạn hướng tới CRM production trong From Zero → Senior .NET (Backend-first). Giáo trình ưu tiên backend, bám chuỗi API → dữ liệu → vận hành → kiến trúc phân tán, thay vì trải rộng theo hướng full-stack. Mỗi module củng cố một lớp kỹ năng trên cùng một CRM học tập bạn phát triển xuyên suốt khóa.

Chuỗi năng lực	Đóng góp vào CRM học tập
Giai đoạn 1 — Foundation	Đọc hiểu yêu cầu, mô hình client–server, Git — nền collaboration khi CRM lớn dần.
Giai đoạn 2 — C# + P1	Domain, OOP, LINQ, async, DI — rule nghiệp vụ & tầng ứng dụng (Inventory là bản mẫu trước CRM).
Giai đoạn 3 — ASP.NET + P2	Host, routing, validation, JWT, SignalR — CRM Backend API thực tế.
Giai đoạn 4 — Data + P3	SQL, EF, cache, job, container — CRM ổn định & có thể triển khai.
Giai đoạn 5 — Senior + Final	Clean Architecture, event/outbox, gateway, observability, perf — CRM/ERP chịu tải & có chủ.

12.12.5.1 — Vai trò giai đoạn này trong chuỗi

SQL, EF Core, cache, job nền, Docker + Project 3 — CRM chạy được như môi trường thật (dữ liệu, hiệu năng, vận hành).

12.12.5.2 — Vị trí bài học (trước / sau)

Mốc	Điểm nối trong giáo trình
Trước	Project 2 — CRM Backend API
Tiếp theo	Module 13 — Entity Framework Core

12.12.6 — Quy trình làm bài (hệ thống)

Bước	Việc cần làm	Output nên có
1	Làm Quiz trước, tự ghi đáp án + 1 dòng lý do	Không xem đáp án; giữ bản nháp
2	Đối chiếu Đáp án; sửa hiểu sai, ghi takeaway vào README hoặc nhật ký học	3–5 bullet “tôi đã hiểu…”
3	Làm Lab theo rubric; trong README nêu phần CRM nào được chạm (Lead, Customer, Deal, Billing…)	Repo / zip + ảnh `dotnet run` hoặc log
4	Tự chấm theo bảng điểm; chuẩn bị 1 phút “vấn đáp” nếu mentor hỏi ngẫu nhiên	Điểm + chỗ còn yếu

12.12.7 — Phần A — Quiz trắc nghiệm (20 điểm)

Gợi ý làm bài: mỗi câu 4 điểm. Ưu tiên suy nghĩ hệ quả trên CRM (tiền, dữ liệu, đa tenant, hiệu năng, bảo mật) — không chỉ nhớ định nghĩa.

Mỗi câu 4 điểm.

ACID — chữ I là?
A. Integration B. Isolation C. Inheritance D. Index-only
Đáp án: B.
Index clustered trên SQL Server thường là?
A. Không tồn tại B. Một clustered index trên bảng (thường PK) C. Chỉ trên view D. Chỉ trên temp table
Đáp án: B.
N+1 query problem xuất hiện khi?
A. Một query trả quá nhiều cột B. Vòng lặp gọi query con cho từng dòng C. Dùng transaction D. Dùng UTF-8
Đáp án: B.
READ COMMITTED SNAPSHOT (RCSI) giúp giảm?
A. Disk size B. Reader-writer blocking so với locking read mặc định một số cấu hình C. Network latency D. JWT size
Đáp án: B (tinh thần; engine cụ thể có chi tiết).
Expand-contract migration nhằm?
A. Xóa bảng nhanh B. Zero-downtime: thêm cột mới → backfill → chuyển đọc/ghi → xóa cột cũ C. Tắt index D. Chỉ cho NoSQL
Đáp án: B.

12.12.8 — Phần B — Lab thực tế (80 điểm)

Lab & rubric: trong README, map từng nhóm điểm rubric sang phần CRM (Lead, Customer, Deal, Billing, Notification…) và artifact (test, log, screenshot, migration).

Tiêu chí	Điểm	Ghi chú
Script tạo schema CRM nhỏ (Customer, Lead) + PK/FK	25
≥ 3 index có lý do (WHERE/JOIN/ORDER BY) trong comment	20
1 stored procedure hoặc view + query parameterized	15
Execution plan: chụp “trước/sau” khi thêm index (ảnh)	10
Transaction demo: transfer balance hoặc tương tự + rollback case	10

12.12.8.1 — Ngưỡng tổng điểm (Quiz 20 + Lab 80)

Mức	Điểm	Ý nghĩa
Đạt	≥ 70	Đủ nền để học module kế mà không “lủng” kiến thức cốt lõi.
Khá	≥ 85	Có minh chứng code + nêu được liên hệ CRM rõ ràng trong README.
Giỏi	≥ 95	Có mở rộng / edge case / ADR ngắn / test bổ trợ (tùy rubric từng bài).

12.12.9 — Phần C — Reflect & nối chuỗi (không chấm điểm)

Viết 5–8 câu (README hoặc docs/learning-log.md):

Kiến thức module / project này sẽ được tái sử dụng trực tiếp ở module hoặc project CRM nào tiếp theo? (ghi tên module / P2 / P3 / Final).
Nếu bỏ qua phần lab, rủi ro lớn nhất cho CRM ở giai đoạn sau là gì?
Một quyết định kỹ thuật (nhỏ) bạn sẵn sàng ghi thành ADR một đoạn sau khi làm lab.

12.12.10 — Tài liệu tham khảo

SQL Server Documentation — Microsoft Learn
PostgreSQL 16 Documentation
Use The Index, Luke — hướng dẫn indexing thực tế xuất sắc
Brent Ozar — SQL Server Performance — video miễn phí về execution plan, parameter sniffing
Learn SQL in 30 days — học song song để luyện thêm bài tập SQL
ACID Properties — Wikipedia
Expand-Contract Pattern — Martin Fowler

Bài tập tổng hợp

Tóm tắt bài học bằng ngôn ngữ của bạn.
Liên hệ nội dung với một tình huống thực tế trong dự án.
Đề xuất một cải tiến cụ thể sau khi học bài này.

Tự kiểm tra

Bạn có thể giải thích lại nội dung chính trong 2 phút không?
Bạn có ví dụ áp dụng thực tế chưa?
Bạn biết bước tiếp theo cần học/triển khai là gì không?

Kết luận

Hoàn thành bài này giúp bạn có góc nhìn đầy đủ hơn trước khi đi tiếp trong module.

Điều hướng

Bài trước: 12.11 — Ví dụ thực tế nhanh
Bài tiếp theo: Không có (bài cuối module).
Về module: Trang mục lục

12.12 - Review and Assessment ​

Mục tiêu bài học ​

Nội dung bài học ​

12.12.1 — Thực hành ​

Bài 1: Tạo Full CRM Schema ​

Bài 2: Viết 5 Queries Thống kê CRM ​

Bài 3: Optimize Query Chậm ​

12.12.2 — Tóm tắt ​

12.12.3 — Checklist trước khi sang bài tiếp theo ​

12.12.4 — Kiểm tra & Thực hành (100 điểm) ​

12.12.5 — Bối cảnh giáo trình (mục tiêu xuyên suốt) ​

12.12.5.1 — Vai trò giai đoạn này trong chuỗi ​

12.12.5.2 — Vị trí bài học (trước / sau) ​

12.12.6 — Quy trình làm bài (hệ thống) ​

12.12.7 — Phần A — Quiz trắc nghiệm (20 điểm) ​

12.12.8 — Phần B — Lab thực tế (80 điểm) ​

12.12.8.1 — Ngưỡng tổng điểm (Quiz 20 + Lab 80) ​

12.12.9 — Phần C — Reflect & nối chuỗi (không chấm điểm) ​

12.12.10 — Tài liệu tham khảo ​

Bài tập tổng hợp ​

Tự kiểm tra ​

Kết luận ​

Điều hướng ​