12.4 — 3. Queries Nâng Cao

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.4.1 — 3.1 JOIN đầy đủ

-- INNER JOIN: chỉ lead có assigned user
SELECT l.LeadId, l.CompanyName, u.FullName AS SalesRep
FROM   Leads l
INNER JOIN Users u ON l.AssignedToUserId = u.UserId;

-- LEFT JOIN: tất cả lead, kể cả chưa assign
SELECT l.LeadId, l.CompanyName, u.FullName AS SalesRep
FROM   Leads l
LEFT  JOIN Users u ON l.AssignedToUserId = u.UserId;

-- FULL OUTER JOIN: tất cả lead + tất cả user (kể cả user chưa có lead)
SELECT l.LeadId, l.CompanyName, u.FullName
FROM   Leads l
FULL OUTER JOIN Users u ON l.AssignedToUserId = u.UserId;

-- CROSS APPLY (SQL Server) / LATERAL (PostgreSQL):
-- Top 3 contacts mới nhất của mỗi customer
SELECT c.CustomerId, c.CompanyName, ct.FirstName, ct.LastName, ct.CreatedAt
FROM   Customers c
CROSS APPLY (
    SELECT TOP 3 FirstName, LastName, CreatedAt
    FROM   Contacts
    WHERE  CustomerId = c.CustomerId
    ORDER  BY CreatedAt DESC
) ct;

-- PostgreSQL tương đương
SELECT c.customer_id, c.company_name, ct.first_name, ct.last_name
FROM   customers c,
LATERAL (
    SELECT first_name, last_name, created_at
    FROM   contacts
    WHERE  customer_id = c.customer_id
    ORDER  BY created_at DESC
    LIMIT  3
) ct;

12.4.2 — 3.2 Subquery so với CTE

-- Subquery: tìm customer có nhiều lead nhất
SELECT CompanyName
FROM   Customers
WHERE  CustomerId = (
    SELECT TOP 1 ConvertedToCustomerId
    FROM   Leads
    WHERE  ConvertedToCustomerId IS NOT NULL
    GROUP  BY ConvertedToCustomerId
    ORDER  BY COUNT(*) DESC
);

-- CTE: dễ đọc hơn, reusable trong cùng query
WITH LeadCounts AS (
    SELECT ConvertedToCustomerId,
           COUNT(*) AS TotalLeads
    FROM   Leads
    WHERE  ConvertedToCustomerId IS NOT NULL
    GROUP  BY ConvertedToCustomerId
),
RankedCustomers AS (
    SELECT c.CustomerId, c.CompanyName, lc.TotalLeads,
           RANK() OVER (ORDER BY lc.TotalLeads DESC) AS Rnk
    FROM   Customers c
    JOIN   LeadCounts lc ON c.CustomerId = lc.ConvertedToCustomerId
)
SELECT CustomerId, CompanyName, TotalLeads
FROM   RankedCustomers
WHERE  Rnk <= 5;

Khi nào dùng CTE thay subquery:

• Logic cần dùng lại nhiều lần trong cùng query
• Cần recursive (ví dụ: cây tổ chức sales)
• Subquery quá lồng nhau, khó debug

12.4.3 — 3.3 Window Functions

-- ROW_NUMBER: đánh số thứ tự lead trong mỗi tháng
SELECT LeadId, CompanyName, CreatedAt,
       ROW_NUMBER() OVER (
           PARTITION BY FORMAT(CreatedAt, 'yyyy-MM')
           ORDER BY     EstimatedValue DESC
       ) AS RankInMonth
FROM   Leads
WHERE  Status = 'Won';

-- RANK so với DENSE_RANK: xử lý tie
SELECT UserId, FullName, TotalWon,
       RANK()       OVER (ORDER BY TotalWon DESC) AS Rank,       -- 1,2,2,4
       DENSE_RANK() OVER (ORDER BY TotalWon DESC) AS DenseRank   -- 1,2,2,3
FROM (
    SELECT u.UserId, u.FullName, COUNT(*) AS TotalWon
    FROM   Users u
    JOIN   Leads l ON u.UserId = l.AssignedToUserId
    WHERE  l.Status = 'Won'
    GROUP  BY u.UserId, u.FullName
) x;

-- LAG / LEAD: so sánh doanh thu tháng này so với tháng trước
WITH MonthlyRevenue 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 PrevMonthRevenue,
       Revenue - LAG(Revenue) OVER (ORDER BY YearMonth) AS MoMChange
FROM   MonthlyRevenue;

-- SUM OVER: running total (tổng lũy kế)
SELECT YearMonth, Revenue,
       SUM(Revenue) OVER (ORDER BY YearMonth ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM   MonthlyRevenue;

12.4.4 — 3.4 Lead Conversion Funnel

-- Funnel report: số lead ở mỗi stage
SELECT Status,
       COUNT(*)                                          AS LeadCount,
       SUM(EstimatedValue)                              AS TotalEstimated,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS Pct
FROM   Leads
GROUP  BY Status
ORDER  BY CASE Status
    WHEN 'New'       THEN 1
    WHEN 'Contacted' THEN 2
    WHEN 'Qualified' THEN 3
    WHEN 'Proposal'  THEN 4
    WHEN 'Won'       THEN 5
    WHEN 'Lost'      THEN 6
END;

---

Bài tập áp dụng

1. Tóm tắt bài học bằng ngôn ngữ của bạn.
2. Liên hệ nội dung với một tình huống thực tế trong dự án.
3. Đề 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.3 — 2. SQL Server so với PostgreSQL
• Bài tiếp theo: 12.5 — 4. Indexing
• Về module: Trang mục lục