Sales Analysis

SELECT
    SUBSTR(ordered_at, 1, 7)   AS year_month,
    COUNT(*)                   AS order_count,
    ROUND(SUM(total_amount))   AS revenue,
    ROUND(AVG(total_amount))   AS avg_order_value
FROM orders
WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
  AND ordered_at >= '2022-01-01'
  AND ordered_at < '2025-01-01'
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY year_month;

WITH category_revenue AS (
    SELECT
        COALESCE(top_cat.name, mid_cat.name, cat.name) AS top_category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    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 categories AS mid_cat ON cat.parent_id = mid_cat.id
    LEFT JOIN categories AS top_cat ON mid_cat.parent_id = top_cat.id
    WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY COALESCE(top_cat.name, mid_cat.name, cat.name)
)
SELECT
    top_category,
    ROUND(revenue) AS revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS revenue_pct
FROM category_revenue
ORDER BY revenue DESC;

SELECT
    RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS ranking,
    c.name          AS customer_name,
    c.grade,
    COUNT(*)        AS order_count,
    ROUND(SUM(o.total_amount)) AS total_spent
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY c.id, c.name, c.grade
ORDER BY total_spent DESC
LIMIT 20;

WITH daily_stats AS (
    SELECT
        DATE(ordered_at) AS order_date,
        CAST(strftime('%w', ordered_at) AS INTEGER) AS dow,
        COUNT(*)               AS order_count,
        SUM(total_amount)      AS revenue
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY DATE(ordered_at)
)
SELECT
    CASE dow
        WHEN 0 THEN '일요일'
        WHEN 1 THEN '월요일'
        WHEN 2 THEN '화요일'
        WHEN 3 THEN '수요일'
        WHEN 4 THEN '목요일'
        WHEN 5 THEN '금요일'
        WHEN 6 THEN '토요일'
    END AS day_of_week,
    ROUND(AVG(order_count)) AS avg_daily_orders,
    ROUND(AVG(revenue))     AS avg_daily_revenue
FROM daily_stats
GROUP BY dow
ORDER BY dow;

WITH quarterly AS (
    SELECT
        SUBSTR(ordered_at, 1, 4) AS year,
        'Q' || ((CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) + 2) / 3) AS quarter,
        SUBSTR(ordered_at, 1, 4) || '-Q' || ((CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) + 2) / 3) AS yq,
        ROUND(SUM(total_amount)) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
      AND ordered_at >= '2022-01-01' AND ordered_at < '2025-01-01'
    GROUP BY SUBSTR(ordered_at, 1, 4),
             (CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) + 2) / 3
)
SELECT
    yq,
    revenue,
    order_count,
    LAG(revenue, 1) OVER (ORDER BY yq) AS prev_quarter_revenue,
    ROUND(100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY yq))
        / LAG(revenue, 1) OVER (ORDER BY yq), 1) AS qoq_growth_pct
FROM quarterly
ORDER BY yq;

WITH monthly_method AS (
    SELECT
        SUBSTR(o.ordered_at, 1, 7) AS year_month,
        pm.method,
        ROUND(SUM(pm.amount)) AS revenue
    FROM payments AS pm
    INNER JOIN orders AS o ON pm.order_id = o.id
    WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
      AND pm.status = 'paid'
    GROUP BY SUBSTR(o.ordered_at, 1, 7), pm.method
)
SELECT
    year_month,
    method,
    revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY year_month), 1) AS method_pct
FROM monthly_method
ORDER BY year_month, revenue DESC;

WITH product_sales AS (
    SELECT
        COALESCE(top_cat.name, mid_cat.name, cat.name) AS top_category,
        p.name AS product_name,
        SUM(oi.quantity)                        AS units_sold,
        ROUND(SUM(oi.quantity * oi.unit_price)) AS revenue
    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 categories AS mid_cat ON cat.parent_id = mid_cat.id
    LEFT JOIN categories AS top_cat ON mid_cat.parent_id = top_cat.id
    WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY COALESCE(top_cat.name, mid_cat.name, cat.name), p.name
),
ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY top_category ORDER BY revenue DESC) AS rn
    FROM product_sales
)
SELECT top_category, rn AS rank, product_name, units_sold, revenue
FROM ranked
WHERE rn <= 3
ORDER BY top_category, rn;

WITH monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 4) AS year,
        SUBSTR(ordered_at, 6, 2) AS month,
        SUBSTR(ordered_at, 1, 7) AS year_month,
        ROUND(SUM(total_amount)) AS revenue
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
      AND ordered_at >= '2022-01-01' AND ordered_at < '2025-01-01'
    GROUP BY SUBSTR(ordered_at, 1, 7)
)
SELECT
    cur.year_month,
    cur.revenue                AS current_revenue,
    prev.revenue               AS prev_year_revenue,
    ROUND(100.0 * (cur.revenue - prev.revenue) / prev.revenue, 1) AS yoy_growth_pct
FROM monthly AS cur
INNER JOIN monthly AS prev
    ON cur.month = prev.month
    AND CAST(cur.year AS INTEGER) = CAST(prev.year AS INTEGER) + 1
WHERE cur.year IN ('2023', '2024')
ORDER BY cur.year_month;

WITH monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS year_month,
        ROUND(SUM(total_amount)) AS revenue
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
      AND ordered_at >= '2023-01-01' AND ordered_at < '2025-01-01'
    GROUP BY SUBSTR(ordered_at, 1, 7)
)
SELECT
    year_month,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY year_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )) AS moving_avg_3m,
    ROUND(AVG(revenue) OVER (
        ORDER BY year_month
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    )) AS moving_avg_6m
FROM monthly
ORDER BY year_month;

WITH product_revenue AS (
    SELECT
        p.id,
        p.name AS product_name,
        ROUND(SUM(oi.quantity * oi.unit_price)) AS revenue
    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
    WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY p.id, p.name
),
cumulative AS (
    SELECT
        product_name,
        revenue,
        SUM(revenue) OVER (ORDER BY revenue DESC) AS cum_revenue,
        SUM(revenue) OVER () AS total_revenue
    FROM product_revenue
)
SELECT
    product_name,
    revenue,
    ROUND(100.0 * cum_revenue / total_revenue, 1) AS cum_pct,
    CASE
        WHEN 100.0 * cum_revenue / total_revenue <= 70 THEN 'A'
        WHEN 100.0 * cum_revenue / total_revenue <= 90 THEN 'B'
        ELSE 'C'
    END AS abc_class
FROM cumulative
ORDER BY revenue DESC
LIMIT 30;

WITH first_order AS (
    SELECT
        customer_id,
        SUBSTR(MIN(ordered_at), 1, 7) AS first_month
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY customer_id
),
classified AS (
    SELECT
        SUBSTR(o.ordered_at, 1, 7) AS year_month,
        CASE
            WHEN SUBSTR(o.ordered_at, 1, 7) = fo.first_month THEN '신규'
            ELSE '재구매'
        END AS customer_type,
        o.total_amount
    FROM orders AS o
    INNER JOIN first_order AS fo ON o.customer_id = fo.customer_id
    WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
)
SELECT
    year_month,
    customer_type,
    COUNT(*)                  AS order_count,
    ROUND(SUM(total_amount)) AS revenue
FROM classified
GROUP BY year_month, customer_type
ORDER BY year_month, customer_type;

SELECT
    SUBSTR(o.ordered_at, 1, 7) AS year_month,
    c.grade,
    COUNT(*)                   AS order_count,
    ROUND(AVG(o.total_amount)) AS avg_order_value
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY SUBSTR(o.ordered_at, 1, 7), c.grade
ORDER BY year_month, 
    CASE c.grade
        WHEN 'VIP' THEN 1
        WHEN 'GOLD' THEN 2
        WHEN 'SILVER' THEN 3
        WHEN 'BRONZE' THEN 4
    END;

SELECT
    s.carrier,
    COUNT(*)                                                        AS delivery_count,
    ROUND(AVG(JULIANDAY(s.delivered_at) - JULIANDAY(s.shipped_at)), 1) AS avg_days,
    MIN(ROUND(JULIANDAY(s.delivered_at) - JULIANDAY(s.shipped_at), 1)) AS min_days,
    MAX(ROUND(JULIANDAY(s.delivered_at) - JULIANDAY(s.shipped_at), 1)) AS max_days,
    ROUND(100.0 * SUM(CASE
        WHEN JULIANDAY(s.delivered_at) - JULIANDAY(s.shipped_at) <= 2 THEN 1
        ELSE 0
    END) / COUNT(*), 1) AS within_2days_pct
FROM shipping AS s
INNER JOIN orders AS o ON s.order_id = o.id
WHERE s.status = 'delivered'
  AND s.shipped_at IS NOT NULL
  AND s.delivered_at IS NOT NULL
  AND o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
GROUP BY s.carrier
ORDER BY avg_days;

WITH order_discount AS (
    SELECT
        id,
        total_amount,
        discount_amount,
        CASE
            WHEN discount_amount = 0 THEN 0
            ELSE ROUND(100.0 * discount_amount / (total_amount + discount_amount), 1)
        END AS discount_pct
    FROM orders
    WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
)
SELECT
    CASE
        WHEN discount_pct = 0    THEN '할인 없음'
        WHEN discount_pct <= 5   THEN '1~5%'
        WHEN discount_pct <= 10  THEN '6~10%'
        WHEN discount_pct <= 20  THEN '11~20%'
        ELSE '20% 초과'
    END AS discount_range,
    COUNT(*)                    AS order_count,
    ROUND(AVG(total_amount))    AS avg_order_value,
    ROUND(SUM(total_amount))    AS total_revenue
FROM order_discount
GROUP BY CASE
    WHEN discount_pct = 0    THEN '할인 없음'
    WHEN discount_pct <= 5   THEN '1~5%'
    WHEN discount_pct <= 10  THEN '6~10%'
    WHEN discount_pct <= 20  THEN '11~20%'
    ELSE '20% 초과'
END
ORDER BY
    CASE
        WHEN discount_pct = 0    THEN 1
        WHEN discount_pct <= 5   THEN 2
        WHEN discount_pct <= 10  THEN 3
        WHEN discount_pct <= 20  THEN 4
        ELSE 5
    END;

WITH promo_sales AS (
    SELECT
        pr.id           AS promo_id,
        pr.name         AS promo_name,
        pr.type         AS promo_type,
        pr.discount_type,
        pr.discount_value,
        COUNT(DISTINCT o.id) AS order_count,
        ROUND(SUM(oi.quantity * oi.unit_price)) AS gross_revenue,
        ROUND(SUM(oi.discount_amount))          AS total_discount
    FROM promotions AS pr
    INNER JOIN promotion_products AS pp ON pr.id = pp.promotion_id
    INNER JOIN order_items AS oi ON pp.product_id = oi.product_id
    INNER JOIN orders AS o ON oi.order_id = o.id
    WHERE o.ordered_at >= pr.started_at
      AND o.ordered_at <= pr.ended_at
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY pr.id, pr.name, pr.type, pr.discount_type, pr.discount_value
)
SELECT
    promo_name,
    promo_type,
    discount_type || ' ' || discount_value AS discount_info,
    order_count,
    gross_revenue,
    total_discount,
    gross_revenue - total_discount AS net_revenue,
    CASE
        WHEN total_discount > 0
        THEN ROUND(100.0 * (gross_revenue - total_discount) / total_discount, 1)
        ELSE NULL
    END AS roi_pct
FROM promo_sales
ORDER BY roi_pct DESC;

WITH cart_products AS (
    SELECT
        c.customer_id,
        ci.product_id,
        cat.name AS category
    FROM carts AS c
    INNER JOIN cart_items AS ci ON c.id = ci.cart_id
    INNER JOIN products AS p ON ci.product_id = p.id
    INNER JOIN categories AS cat ON p.category_id = cat.id
),
purchased AS (
    SELECT DISTINCT
        o.customer_id,
        oi.product_id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id = oi.order_id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
)
SELECT
    cp.category,
    COUNT(*) AS cart_items_total,
    SUM(CASE WHEN pur.product_id IS NOT NULL THEN 1 ELSE 0 END) AS converted,
    ROUND(100.0 * SUM(CASE WHEN pur.product_id IS NOT NULL THEN 1 ELSE 0 END)
        / COUNT(*), 1) AS conversion_rate_pct
FROM cart_products AS cp
LEFT JOIN purchased AS pur
    ON cp.customer_id = pur.customer_id
    AND cp.product_id = pur.product_id
GROUP BY cp.category
ORDER BY conversion_rate_pct DESC;

SELECT
    p1.name AS product_a,
    p2.name AS product_b,
    COUNT(*) AS co_purchase_count
FROM order_items AS oi1
INNER JOIN order_items AS oi2
    ON oi1.order_id = oi2.order_id
    AND oi1.product_id < oi2.product_id
INNER JOIN products AS p1 ON oi1.product_id = p1.id
INNER JOIN products AS p2 ON oi2.product_id = p2.id
INNER JOIN orders AS o ON oi1.order_id = o.id
WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY oi1.product_id, oi2.product_id, p1.name, p2.name
HAVING COUNT(*) >= 5
ORDER BY co_purchase_count DESC
LIMIT 20;

WITH product_metrics AS (
    SELECT
        p.id,
        p.name,
        AVG(r.rating)                          AS avg_rating,
        COUNT(DISTINCT r.id)                    AS review_count,
        ROUND(SUM(oi.quantity * oi.unit_price)) AS revenue
    FROM products AS p
    INNER JOIN reviews AS r ON p.id = r.product_id
    INNER JOIN order_items AS oi ON p.id = oi.product_id
    INNER JOIN orders AS o ON oi.order_id = o.id
    WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY p.id, p.name
    HAVING COUNT(DISTINCT r.id) >= 3
)
SELECT
    CASE
        WHEN avg_rating < 2 THEN '1.0~1.9'
        WHEN avg_rating < 3 THEN '2.0~2.9'
        WHEN avg_rating < 4 THEN '3.0~3.9'
        ELSE '4.0~5.0'
    END AS rating_range,
    COUNT(*)                    AS product_count,
    ROUND(AVG(revenue))         AS avg_revenue,
    ROUND(AVG(review_count))    AS avg_reviews,
    ROUND(AVG(avg_rating), 2)   AS avg_rating_in_range
FROM product_metrics
GROUP BY CASE
    WHEN avg_rating < 2 THEN '1.0~1.9'
    WHEN avg_rating < 3 THEN '2.0~2.9'
    WHEN avg_rating < 4 THEN '3.0~3.9'
    ELSE '4.0~5.0'
END
ORDER BY rating_range;

WITH order_classified AS (
    SELECT
        customer_id,
        total_amount,
        CASE WHEN point_used > 0 THEN '포인트 사용' ELSE '미사용' END AS point_type
    FROM orders
    WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
),
customer_stats AS (
    SELECT
        point_type,
        customer_id,
        COUNT(*) AS order_count,
        AVG(total_amount) AS avg_amount
    FROM order_classified
    GROUP BY point_type, customer_id
)
SELECT
    point_type,
    COUNT(DISTINCT customer_id)    AS customer_count,
    ROUND(AVG(avg_amount))         AS avg_order_value,
    ROUND(100.0 * SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END)
        / COUNT(*), 1)            AS repeat_rate_pct
FROM customer_stats
GROUP BY point_type;

WITH sales AS (
    SELECT
        COUNT(*)                            AS total_orders,
        COUNT(DISTINCT customer_id)         AS unique_customers,
        ROUND(SUM(total_amount))            AS total_revenue,
        ROUND(AVG(total_amount))            AS avg_order_value
    FROM orders
    WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'
      AND status NOT IN ('cancelled', 'returned', 'return_requested')
),
returns_stat AS (
    SELECT
        ROUND(100.0 * COUNT(*) / (
            SELECT COUNT(*) FROM orders
            WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'
        ), 1) AS return_rate_pct
    FROM orders
    WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'
      AND status IN ('returned', 'return_requested')
),
shipping_stat AS (
    SELECT
        ROUND(AVG(JULIANDAY(s.delivered_at) - JULIANDAY(s.shipped_at)), 1) AS avg_delivery_days
    FROM shipping AS s
    INNER JOIN orders AS o ON s.order_id = o.id
    WHERE s.status = 'delivered'
      AND s.shipped_at IS NOT NULL
      AND s.delivered_at IS NOT NULL
      AND o.ordered_at >= '2024-01-01' AND o.ordered_at < '2025-01-01'
),
review_stat AS (
    SELECT
        ROUND(AVG(rating), 2) AS avg_rating,
        COUNT(*) AS review_count
    FROM reviews
    WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
)
SELECT
    s.total_revenue,
    s.total_orders,
    s.unique_customers,
    s.avg_order_value,
    r.return_rate_pct,
    sh.avg_delivery_days,
    rv.avg_rating,
    rv.review_count
FROM sales AS s
CROSS JOIN returns_stat AS r
CROSS JOIN shipping_stat AS sh
CROSS JOIN review_stat AS rv;