Complex Queries: Query SQL Tingkat Lanjut

-- Analisis penjualan per kategori per bulan
WITH monthly_sales AS (
    SELECT 
        c.nama as kategori,
        DATE_FORMAT(o.tanggal, '%Y-%m') as bulan,
        SUM(oi.quantity * oi.price) as total_penjualan
    FROM categories c
    JOIN products p ON c.id = p.category_id
    JOIN order_items oi ON p.id = oi.product_id
    JOIN orders o ON oi.order_id = o.id
    GROUP BY c.nama, DATE_FORMAT(o.tanggal, '%Y-%m')
),
category_avg AS (
    SELECT 
        kategori,
        AVG(total_penjualan) as rata_rata_penjualan
    FROM monthly_sales
    GROUP BY kategori
)
SELECT 
    ms.*,
    ca.rata_rata_penjualan,
    (ms.total_penjualan - ca.rata_rata_penjualan) as selisih
FROM monthly_sales ms
JOIN category_avg ca ON ms.kategori = ca.kategori
ORDER BY ms.bulan DESC, ms.total_penjualan DESC;

-- Generate date range
WITH RECURSIVE date_range AS (
    -- Base case: tanggal awal
    SELECT DATE('2024-01-01') as tanggal
    UNION ALL
    -- Recursive case: tambah 1 hari
    SELECT DATE_ADD(tanggal, INTERVAL 1 DAY)
    FROM date_range
    WHERE tanggal < '2024-12-31'
)
SELECT 
    dr.tanggal,
    COALESCE(SUM(oi.quantity * oi.price), 0) as total_penjualan
FROM date_range dr
LEFT JOIN orders o ON DATE(o.tanggal) = dr.tanggal
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY dr.tanggal
ORDER BY dr.tanggal;

-- Contoh stored procedure dengan dynamic SQL
DELIMITER //

CREATE PROCEDURE analyze_sales(
    IN start_date DATE,
    IN end_date DATE,
    IN group_by VARCHAR(20)  -- 'daily', 'weekly', 'monthly'
)
BEGIN
    SET @sql = CONCAT('
        SELECT 
            CASE ?
                WHEN ''daily'' THEN DATE(o.tanggal)
                WHEN ''weekly'' THEN DATE(DATE_SUB(o.tanggal, INTERVAL WEEKDAY(o.tanggal) DAY))
                WHEN ''monthly'' THEN DATE_FORMAT(o.tanggal, ''%Y-%m-01'')
            END as period,
            COUNT(DISTINCT o.id) as total_orders,
            SUM(oi.quantity * oi.price) as total_sales
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        WHERE o.tanggal BETWEEN ? AND ?
        GROUP BY 1
        ORDER BY 1'
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING group_by, start_date, end_date;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- Panggil stored procedure
CALL analyze_sales('2024-01-01', '2024-12-31', 'monthly');

-- Analisis trend penjualan
SELECT 
    DATE_FORMAT(o.tanggal, '%Y-%m') as bulan,
    SUM(oi.quantity * oi.price) as total_penjualan,
    AVG(SUM(oi.quantity * oi.price)) OVER (
        ORDER BY DATE_FORMAT(o.tanggal, '%Y-%m')
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_average_3_bulan,
    LAG(SUM(oi.quantity * oi.price), 1) OVER (
        ORDER BY DATE_FORMAT(o.tanggal, '%Y-%m')
    ) as penjualan_bulan_lalu,
    RANK() OVER (
        ORDER BY SUM(oi.quantity * oi.price) DESC
    ) as rank_penjualan
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY DATE_FORMAT(o.tanggal, '%Y-%m');

WITH customer_months AS (
    -- Get active months per customer
    SELECT 
        customer_id,
        DATE_FORMAT(tanggal, '%Y-%m') as bulan
    FROM orders
    GROUP BY customer_id, DATE_FORMAT(tanggal, '%Y-%m')
),
retention_analysis AS (
    -- Calculate retention between months
    SELECT 
        curr.bulan,
        COUNT(DISTINCT curr.customer_id) as total_customers,
        COUNT(DISTINCT next.customer_id) as retained_customers
    FROM customer_months curr
    LEFT JOIN customer_months next 
        ON curr.customer_id = next.customer_id
        AND next.bulan = DATE_FORMAT(
            DATE_ADD(STR_TO_DATE(CONCAT(curr.bulan, '-01'), '%Y-%m-%d'),
            INTERVAL 1 MONTH),
            '%Y-%m'
        )
    GROUP BY curr.bulan
)
SELECT 
    bulan,
    total_customers,
    retained_customers,
    ROUND(retained_customers / total_customers * 100, 2) as retention_rate
FROM retention_analysis
ORDER BY bulan;

WITH product_pairs AS (
    -- Find products often bought together
    SELECT 
        oi1.product_id as product1_id,
        oi2.product_id as product2_id,
        COUNT(*) as bought_together_count
    FROM order_items oi1
    JOIN order_items oi2 
        ON oi1.order_id = oi2.order_id
        AND oi1.product_id < oi2.product_id
    GROUP BY 
        oi1.product_id,
        oi2.product_id
    HAVING COUNT(*) >= 3
)
SELECT 
    p1.nama as product1,
    p2.nama as product2,
    pp.bought_together_count
FROM product_pairs pp
JOIN products p1 ON pp.product1_id = p1.id
JOIN products p2 ON pp.product2_id = p2.id
ORDER BY pp.bought_together_count DESC;