3. Window Functions, CTEs (Common Table Expressions) & Phân Tích Dữ Liệu Nâng Cao

-- TÍNH DOANH SỐ LŨY KẾ VÀ TỶ LỆ TĂNG TRƯỞNG THEO THÁNG BẰNG CTE & WINDOW FUNCTIONS
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS order_month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    TO_CHAR(order_month, 'YYYY-MM') AS month,
    revenue,
    SUM(revenue) OVER (ORDER BY order_month) AS running_total,
    LAG(revenue, 1) OVER (ORDER BY order_month) AS prev_month_revenue,
    ROUND(((revenue - LAG(revenue, 1) OVER (ORDER BY order_month)) / 
           NULLIF(LAG(revenue, 1) OVER (ORDER BY order_month), 0) * 100), 2) AS growth_percentage
FROM monthly_revenue
ORDER BY order_month;