매출 분석

SELECT
    SUBSTR(ordered_at, 1, 7)        AS year_month,
    COUNT(*)                         AS order_count,
    ROUND(SUM(total_amount), 2)      AS revenue,
    ROUND(AVG(total_amount), 2)      AS avg_order_value
FROM orders
WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
  AND ordered_at BETWEEN '2022-01-01' AND '2024-12-31 23:59:59'
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY year_month;

SELECT
    p.name                                  AS product_name,
    cat.name                                AS category,
    SUM(oi.quantity)                        AS units_sold,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_revenue,
    COUNT(DISTINCT r.id)                    AS review_count,
    ROUND(AVG(r.rating), 2)                 AS avg_rating
FROM order_items AS oi
INNER JOIN orders     AS o   ON oi.order_id   = o.id
INNER JOIN products   AS p   ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
LEFT  JOIN reviews    AS r   ON r.product_id  = p.id
WHERE o.ordered_at LIKE '2024%'
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY p.id, p.name, cat.name
ORDER BY total_revenue DESC
LIMIT 10;

WITH monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 7)  AS year_month,
        SUBSTR(ordered_at, 6, 2)  AS month_num,
        SUM(total_amount)         AS revenue
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY SUBSTR(ordered_at, 1, 7)
)
SELECT
    CASE month_num
        WHEN '01' THEN '1월'  WHEN '02' THEN '2월'
        WHEN '03' THEN '3월'  WHEN '04' THEN '4월'
        WHEN '05' THEN '5월'  WHEN '06' THEN '6월'
        WHEN '07' THEN '7월'  WHEN '08' THEN '8월'
        WHEN '09' THEN '9월'  WHEN '10' THEN '10월'
        WHEN '11' THEN '11월' WHEN '12' THEN '12월'
    END AS month_name,
    month_num,
    COUNT(*) AS years_of_data,
    ROUND(AVG(revenue), 2)  AS avg_monthly_revenue,
    ROUND(MIN(revenue), 2)  AS min_revenue,
    ROUND(MAX(revenue), 2)  AS max_revenue
FROM monthly
GROUP BY month_num
ORDER BY month_num;

WITH payment_totals AS (
    SELECT
        p.method,
        COUNT(*)            AS transaction_count,
        SUM(p.amount)       AS total_collected,
        AVG(p.amount)       AS avg_transaction
    FROM payments AS p
    WHERE p.status = 'completed'
    GROUP BY p.method
),
grand_total AS (
    SELECT SUM(total_collected) AS grand FROM payment_totals
)
SELECT
    pt.method,
    pt.transaction_count,
    ROUND(pt.total_collected, 2)  AS total_collected,
    ROUND(pt.avg_transaction, 2)  AS avg_transaction,
    ROUND(100.0 * pt.total_collected / gt.grand, 1) AS pct_of_revenue
FROM payment_totals AS pt
CROSS JOIN grand_total AS gt
ORDER BY pt.total_collected DESC;

WITH category_revenue AS (
    SELECT
        cat.name AS category,
        ROUND(SUM(CASE
            WHEN o.ordered_at LIKE '2023%'
            THEN oi.quantity * oi.unit_price ELSE 0
        END), 2) AS revenue_2023,
        ROUND(SUM(CASE
            WHEN o.ordered_at LIKE '2024%'
            THEN oi.quantity * oi.unit_price ELSE 0
        END), 2) AS revenue_2024
    FROM order_items AS oi
    INNER JOIN orders     AS o   ON oi.order_id   = o.id
    INNER JOIN products   AS p   ON oi.product_id = p.id
    INNER JOIN categories AS cat ON p.category_id = cat.id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
      AND o.ordered_at BETWEEN '2023-01-01' AND '2024-12-31 23:59:59'
    GROUP BY cat.name
)
SELECT
    category,
    revenue_2023,
    revenue_2024,
    ROUND(revenue_2024 - revenue_2023, 2) AS absolute_change,
    ROUND(
        100.0 * (revenue_2024 - revenue_2023)
              / NULLIF(revenue_2023, 0),
        1
    ) AS yoy_growth_pct
FROM category_revenue
ORDER BY yoy_growth_pct DESC;

SELECT
    cat.name AS category,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='01' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS jan,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='02' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS feb,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='03' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS mar,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='04' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS apr,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='05' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS may,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='06' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS jun,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='07' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS jul,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='08' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS aug,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='09' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS sep,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='10' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS oct,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='11' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS nov,
    ROUND(SUM(CASE WHEN SUBSTR(o.ordered_at,6,2)='12' THEN oi.quantity*oi.unit_price ELSE 0 END),0) AS dec
FROM order_items AS oi
INNER JOIN orders     AS o   ON oi.order_id   = o.id
INNER JOIN products   AS p   ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE o.ordered_at LIKE '2024%'
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY cat.name
ORDER BY cat.name;