SQL Window Functions: Panduan Lengkap

-- =============================================
-- Tabel: orders (pesanan e-commerce)
-- =============================================
-- Analisis: Top 3 produk per kategori berdasarkan revenue

WITH product_revenue AS (
    SELECT
        p.nama_produk,
        p.kategori,
        SUM(dp.subtotal) AS total_revenue,
        SUM(dp.jumlah)   AS total_qty,
        ROW_NUMBER() OVER(
            PARTITION BY p.kategori
            ORDER BY SUM(dp.subtotal) DESC
        ) AS rank_in_category
    FROM detail_pesanan dp
    JOIN produk p ON dp.id_produk = p.id_produk
    JOIN pesanan ps ON dp.id_pesanan = ps.id_pesanan
    WHERE ps.status = 'selesai'
    GROUP BY p.nama_produk, p.kategori
)
SELECT
    nama_produk,
    kategori,
    total_revenue,
    total_qty,
    rank_in_category
FROM product_revenue
WHERE rank_in_category <= 3
ORDER BY kategori, rank_in_category;


-- =============================================
-- Analisis: Trend penjualan bulanan + pertumbuhan
-- =============================================
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(tanggal_pesanan, '%Y-%m') AS bulan,
        COUNT(*) AS jumlah_order,
        SUM(total) AS revenue,
        AVG(total) AS avg_order_value
    FROM pesanan
    WHERE status != 'batal'
    GROUP BY DATE_FORMAT(tanggal_pesanan, '%Y-%m')
)
SELECT
    bulan,
    jumlah_order,
    revenue,
    avg_order_value,
    LAG(revenue) OVER(ORDER BY bulan) AS prev_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER(ORDER BY bulan)) * 100.0
        / NULLIF(LAG(revenue) OVER(ORDER BY bulan), 0), 2
    ) AS revenue_growth_pct,
    SUM(revenue) OVER(ORDER BY bulan) AS cumulative_revenue,
    AVG(revenue) OVER(
        ORDER BY bulan
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM monthly_sales
ORDER BY bulan;

-- =============================================
-- Dashboard HR: Lengkap dengan window functions
-- =============================================
WITH salary_analysis AS (
    SELECT
        nama,
        departemen,
        posisi,
        gaji,
        tanggal_masuk,
        -- Peringkat dalam departemen
        DENSE_RANK() OVER(
            PARTITION BY departemen
            ORDER BY gaji DESC
        ) AS rank_dept,

        -- Quartile keseluruhan
        NTILE(4) OVER(ORDER BY gaji DESC) AS quartile,

        -- Running count berdasarkan tanggal masuk
        ROW_NUMBER() OVER(
            ORDER BY tanggal_masuk
        ) AS urutan_masuk,

        -- Bandingkan dengan rata-rata
        gaji - AVG(gaji) OVER() AS selisih_avg_global,
        gaji - AVG(gaji) OVER(PARTITION BY departemen) AS selisih_avg_dept,

        -- Persentase dari max gaji departemen
        ROUND(
            gaji * 100.0 / MAX(gaji) OVER(PARTITION BY departemen), 2
        ) AS pct_of_max_dept,

        -- Total karyawan di departemen
        COUNT(*) OVER(PARTITION BY departemen) AS total_anggota
    FROM karyawan
)
SELECT
    nama,
    departemen,
    posisi,
    gaji,
    rank_dept,
    CASE quartile
        WHEN 1 THEN 'Q1 (Top 25%)'
        WHEN 2 THEN 'Q2 (25-50%)'
        WHEN 3 THEN 'Q3 (50-75%)'
        WHEN 4 THEN 'Q4 (Bottom 25%)'
    END AS salary_quartile,
    selisih_avg_dept,
    pct_of_max_dept,
    total_anggota
FROM salary_analysis
ORDER BY departemen, gaji DESC;

-- =============================================
-- Cohort Analysis: Retensi pelanggan per bulan
-- =============================================
WITH customer_cohort AS (
    SELECT
        c.id_pelanggan,
        DATE_FORMAT(c.tanggal_daftar, '%Y-%m') AS cohort_month,
        DATE_FORMAT(p.tanggal_pesanan, '%Y-%m') AS order_month,
        -- Hitung bulan ke-n sejak daftar
        TIMESTAMPDIFF(MONTH,
            c.tanggal_daftar,
            p.tanggal_pesanan
        ) AS months_since_signup
    FROM pelanggan c
    JOIN pesanan p ON c.id_pelanggan = p.id_pelanggan
    WHERE p.status != 'batal'
),
cohort_size AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT id_pelanggan) AS cohort_customers
    FROM customer_cohort
    GROUP BY cohort_month
)
SELECT
    cc.cohort_month,
    cs.cohort_customers,
    cc.months_since_signup,
    COUNT(DISTINCT cc.id_pelanggan) AS active_customers,
    ROUND(
        COUNT(DISTINCT cc.id_pelanggan) * 100.0 / cs.cohort_customers, 2
    ) AS retention_pct
FROM customer_cohort cc
JOIN cohort_size cs ON cc.cohort_month = cs.cohort_month
GROUP BY cc.cohort_month, cs.cohort_customers, cc.months_since_signup
ORDER BY cc.cohort_month, cc.months_since_signup;