mysql_queries.sql

-- ============================================================
-- ADVANCED SQL QUERIES — E-COMMERCE PORTFOLIO PROJECT
-- (MySQL / MariaDB version — สำหรับ Laragon + HeidiSQL)
-- MariaDB 10.2+ / MySQL 8.0+ จำเป็น (รองรับ window function, recursive CTE)
-- ============================================================


-- ============================================================
-- 1. WINDOW FUNCTION: จัดอันดับสินค้าขายดี Top 3 ต่อหมวดหมู่
-- ============================================================
WITH product_sales AS (
    SELECT
        p.product_id,
        p.product_name,
        c.category_name,
        SUM(oi.quantity) AS total_units_sold
    FROM order_items oi
    JOIN product_variants pv ON pv.variant_id = oi.variant_id
    JOIN products p          ON p.product_id = pv.product_id
    JOIN categories c        ON c.category_id = p.category_id
    JOIN orders o             ON o.order_id = oi.order_id
    WHERE o.order_status <> 'cancelled'
    GROUP BY p.product_id, p.product_name, c.category_name
),
ranked AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY category_name ORDER BY total_units_sold DESC) AS rank_in_category
    FROM product_sales
)
SELECT category_name, product_name, total_units_sold, rank_in_category
FROM ranked
WHERE rank_in_category <= 3
ORDER BY category_name, rank_in_category;


-- ============================================================
-- 2. RUNNING TOTAL / MOVING AVERAGE: ยอดขายสะสมรายเดือน
--    (MySQL ไม่มี DATE_TRUNC ใช้ DATE_FORMAT แทน)
-- ============================================================
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(o.order_date, '%Y-%m-01') AS sales_month,
        SUM(oi.quantity * oi.unit_price)       AS gross_revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_status <> 'cancelled'
    GROUP BY DATE_FORMAT(o.order_date, '%Y-%m-01')
)
SELECT
    sales_month,
    gross_revenue,
    SUM(gross_revenue) OVER (ORDER BY sales_month) AS cumulative_revenue,
    ROUND(
        AVG(gross_revenue) OVER (ORDER BY sales_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
        2
    ) AS moving_avg_3month
FROM monthly_sales
ORDER BY sales_month;


-- ============================================================
-- 3. RECURSIVE CTE: ไล่โครงสร้างหมวดหมู่สินค้าทั้งต้นไม้
-- ============================================================
WITH RECURSIVE category_tree AS (
    SELECT category_id, category_name, parent_id, 0 AS depth,
           CAST(category_name AS CHAR(500)) AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.category_id, c.category_name, c.parent_id, ct.depth + 1,
           CONCAT(ct.path, ' > ', c.category_name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT category_id, depth, path
FROM category_tree
ORDER BY path;


-- ============================================================
-- 4. CUSTOMER SEGMENTATION (RFM แบบง่าย)
-- ============================================================
WITH customer_rfm AS (
    SELECT
        c.customer_id,
        CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
        DATEDIFF(CURRENT_DATE, MAX(o.order_date)) AS recency_days,
        COUNT(DISTINCT o.order_id)                AS frequency,
        SUM(oi.quantity * oi.unit_price)          AS monetary
    FROM customers c
    JOIN orders o        ON o.customer_id = c.customer_id
    JOIN order_items oi  ON oi.order_id = o.order_id
    WHERE o.order_status <> 'cancelled'
    GROUP BY c.customer_id, c.first_name, c.last_name
)
SELECT
    customer_id,
    customer_name,
    recency_days,
    frequency,
    ROUND(monetary, 2) AS monetary,
    CASE
        WHEN recency_days <= 30 AND frequency >= 5 THEN 'VIP / Champion'
        WHEN recency_days <= 90 AND frequency >= 3 THEN 'Loyal Customer'
        WHEN recency_days > 180                    THEN 'At Risk / Churned'
        ELSE 'Regular'
    END AS segment
FROM customer_rfm
ORDER BY monetary DESC
LIMIT 50;


-- ============================================================
-- 5. SELF-JOIN: Market Basket Analysis แบบง่าย
-- ============================================================
WITH order_products AS (
    SELECT DISTINCT o.order_id, p.product_id, p.product_name
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    JOIN product_variants pv ON pv.variant_id = oi.variant_id
    JOIN products p ON p.product_id = pv.product_id
)
SELECT
    a.product_name AS product_a,
    b.product_name AS product_b,
    COUNT(*) AS times_bought_together
FROM order_products a
JOIN order_products b
    ON a.order_id = b.order_id
   AND a.product_id < b.product_id
GROUP BY a.product_name, b.product_name
HAVING COUNT(*) >= 2
ORDER BY times_bought_together DESC
LIMIT 20;


-- ============================================================
-- 6. SUBQUERY + HAVING: ลูกค้าที่ยอดใช้จ่ายสูงกว่าค่าเฉลี่ยของทุกคน
-- ============================================================
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_status <> 'cancelled'
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING SUM(oi.quantity * oi.unit_price) > (
    SELECT AVG(customer_total) FROM (
        SELECT SUM(oi2.quantity * oi2.unit_price) AS customer_total
        FROM orders o2
        JOIN order_items oi2 ON oi2.order_id = o2.order_id
        WHERE o2.order_status <> 'cancelled'
        GROUP BY o2.customer_id
    ) AS sub
)
ORDER BY total_spent DESC;


-- ============================================================
-- 7. สินค้าที่ไม่เคยมีรีวิว แต่ขายได้เยอะ (LEFT JOIN + IS NULL)
-- ============================================================
SELECT
    p.product_id,
    p.product_name,
    COALESCE(SUM(oi.quantity), 0) AS total_units_sold
FROM products p
JOIN product_variants pv ON pv.product_id = p.product_id
JOIN order_items oi      ON oi.variant_id = pv.variant_id
LEFT JOIN reviews r      ON r.product_id = p.product_id
WHERE r.review_id IS NULL
GROUP BY p.product_id, p.product_name
ORDER BY total_units_sold DESC
LIMIT 15;


-- ============================================================
-- 8. AVERAGE RATING + REVIEW COUNT ต่อสินค้า (weighted rating)
-- ============================================================
SELECT
    p.product_id,
    p.product_name,
    COUNT(r.review_id)               AS review_count,
    ROUND(AVG(r.rating), 2)          AS avg_rating,
    ROUND(
        (AVG(r.rating) * COUNT(r.review_id) + 3.0 * 10) / (COUNT(r.review_id) + 10),
        2
    ) AS weighted_rating
FROM products p
JOIN reviews r ON r.product_id = p.product_id
GROUP BY p.product_id, p.product_name
HAVING COUNT(r.review_id) >= 3
ORDER BY weighted_rating DESC
LIMIT 20;


-- ============================================================
-- 9. คำนวณ Conversion / Cancellation Rate ต่อเดือน
--    (MySQL ไม่มี FILTER (WHERE ...) ใช้ SUM(CASE WHEN...) แทน)
-- ============================================================
SELECT
    DATE_FORMAT(order_date, '%Y-%m-01') AS month,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
    ROUND(
        100.0 * SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*),
        2
    ) AS cancellation_rate_pct
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m-01')
ORDER BY month;


-- ============================================================
-- 10. สินค้าที่สต็อกใกล้หมด (Low Stock Alert) พร้อมยอดขาย 30 วันล่าสุด
-- ============================================================
SELECT
    pv.variant_id,
    pv.sku,
    p.product_name,
    pv.variant_name,
    pv.stock_quantity,
    COALESCE(SUM(
        CASE WHEN o.order_date >= CURRENT_DATE - INTERVAL 30 DAY THEN oi.quantity ELSE 0 END
    ), 0) AS units_sold_last_30d
FROM product_variants pv
JOIN products p ON p.product_id = pv.product_id
LEFT JOIN order_items oi ON oi.variant_id = pv.variant_id
LEFT JOIN orders o        ON o.order_id = oi.order_id
WHERE pv.stock_quantity <= 50
GROUP BY pv.variant_id, pv.sku, p.product_name, pv.variant_name, pv.stock_quantity
ORDER BY pv.stock_quantity ASC;


-- ============================================================
-- 11. ANALYZE: เปรียบเทียบ query plan ก่อน/หลังมี index
--     หมายเหตุ: MariaDB ใช้ "ANALYZE SELECT ..." (ไม่ใช่ "EXPLAIN ANALYZE" แบบ MySQL/Postgres)
--     ถ้าใช้ MySQL แท้ๆ (8.0.18+) ให้เปลี่ยนเป็น "EXPLAIN ANALYZE SELECT ..." แทน
-- ============================================================
ANALYZE
SELECT o.order_id, o.order_date, o.order_status
FROM orders o
WHERE o.customer_id = 123
ORDER BY o.order_date DESC;

-- ตัวอย่างการทดสอบ: DROP INDEX idx_orders_customer ON orders; แล้วรันซ้ำ เทียบเวลา
-- จากนั้น CREATE INDEX idx_orders_customer ON orders(customer_id); กลับคืน


-- ============================================================
-- 12. VIEW ที่สร้างไว้ใน mysql_schema.sql: vw_daily_sales
-- ============================================================
SELECT * FROM vw_daily_sales
ORDER BY sales_date DESC
LIMIT 30;