주문 기초

SELECT
    COUNT(*) AS total_orders,
    ROUND(SUM(total_amount), 2) AS total_revenue
FROM orders;

SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
ORDER BY cnt DESC;

SELECT
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 2) AS revenue
FROM orders
WHERE ordered_at LIKE '2024%';

SELECT
    COUNT(*) AS cancelled_count,
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM orders), 2) AS cancel_rate
FROM orders
WHERE status = 'cancelled';

SELECT order_number, total_amount, ordered_at
FROM orders
ORDER BY total_amount DESC
LIMIT 10;

SELECT
    SUBSTR(ordered_at, 1, 7) AS month,
    COUNT(*) AS orders,
    ROUND(SUM(total_amount), 2) AS revenue
FROM orders
WHERE ordered_at LIKE '2024%'
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY month;

SELECT
    ROUND(AVG(total_amount), 2) AS avg_all,
    ROUND(AVG(CASE
        WHEN status NOT IN ('cancelled') THEN total_amount
    END), 2) AS avg_excl_cancelled
FROM orders;

SELECT
    SUM(CASE WHEN shipping_fee = 0 THEN 1 ELSE 0 END) AS free_shipping,
    COUNT(*) AS total,
    ROUND(100.0 * SUM(CASE WHEN shipping_fee = 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct
FROM orders;

SELECT
    COUNT(*) AS orders_with_points,
    ROUND(AVG(point_used), 0) AS avg_points_used,
    MAX(point_used) AS max_points_used
FROM orders
WHERE point_used > 0;

SELECT
    method,
    COUNT(*) AS tx_count,
    ROUND(SUM(amount), 2) AS total_amount
FROM payments
GROUP BY method
ORDER BY total_amount DESC;

SELECT
    COUNT(*) AS card_payments,
    SUM(CASE WHEN installment_months > 0 THEN 1 ELSE 0 END) AS installment_count,
    ROUND(100.0 * SUM(CASE WHEN installment_months > 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS installment_pct,
    ROUND(AVG(CASE WHEN installment_months > 0 THEN installment_months END), 1) AS avg_months
FROM payments
WHERE method = 'card';

SELECT
    CASE CAST(STRFTIME('%w', ordered_at) AS INTEGER)
        WHEN 0 THEN '일'
        WHEN 1 THEN '월'
        WHEN 2 THEN '화'
        WHEN 3 THEN '수'
        WHEN 4 THEN '목'
        WHEN 5 THEN '금'
        WHEN 6 THEN '토'
    END AS day_name,
    COUNT(*) AS order_count
FROM orders
GROUP BY STRFTIME('%w', ordered_at)
ORDER BY STRFTIME('%w', ordered_at);

SELECT
    SUM(CASE WHEN notes IS NOT NULL THEN 1 ELSE 0 END) AS with_notes,
    COUNT(*) AS total,
    ROUND(100.0 * SUM(CASE WHEN notes IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct
FROM orders;

SELECT
    SUBSTR(ordered_at, 1, 4) AS year,
    COUNT(*) AS orders,
    ROUND(SUM(total_amount), 2) AS revenue,
    ROUND(AVG(total_amount), 2) AS avg_order
FROM orders
WHERE status NOT IN ('cancelled')
GROUP BY SUBSTR(ordered_at, 1, 4)
ORDER BY year;

SELECT
    COUNT(*) AS refund_count,
    ROUND(SUM(amount), 2) AS total_refunded
FROM payments
WHERE status = 'refunded';