8. Đồ Án Tốt Nghiệp: Triển Khai Trung Tâm Điều Hành Dữ Liệu (BI Operations Center) Kết Nối CSDL Thực Tế

-- TRUY VẤN SQL ĐỒ ÁN MẪU: BÁO CÁO DOANH THU & TĂNG TRƯỞNG THEO THÁNG (MOM)
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', created_at) AS report_month,
        SUM(total_amount) AS total_revenue,
        COUNT(DISTINCT id) AS total_orders,
        COUNT(DISTINCT customer_id) AS active_customers
    FROM orders
    WHERE status = 'COMPLETED'
    GROUP BY 1
)
SELECT 
    report_month,
    total_revenue,
    total_orders,
    active_customers,
    LAG(total_revenue) OVER (ORDER BY report_month) AS prev_month_revenue,
    ROUND(
        ((total_revenue - LAG(total_revenue) OVER (ORDER BY report_month)) / NULLIF(LAG(total_revenue) OVER (ORDER BY report_month), 0)) * 100, 
        2
    ) AS mom_growth_percentage
FROM monthly_revenue
ORDER BY report_month DESC;