Set Operations

SELECT c.name, c.email
FROM customers AS c
WHERE c.id IN (
    SELECT customer_id FROM reviews
    UNION
    SELECT customer_id FROM complaints
)
ORDER BY c.name
LIMIT 20;

SELECT COUNT(*) AS total_with_dup
FROM (
    SELECT customer_id FROM reviews
    UNION ALL
    SELECT customer_id FROM complaints
);

SELECT '2024' AS year,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
WHERE ordered_at LIKE '2024%'
  AND status NOT IN ('cancelled')
UNION ALL
SELECT '2025' AS year,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
WHERE ordered_at LIKE '2025%'
  AND status NOT IN ('cancelled');

SELECT COUNT(*) AS total_products
FROM (
    SELECT product_id FROM wishlists
    UNION
    SELECT product_id FROM order_items
);

SELECT '취소' AS event_type,
       order_number AS reference,
       cancelled_at AS event_date
FROM orders
WHERE status = 'cancelled' AND cancelled_at IS NOT NULL
UNION ALL
SELECT '반품' AS event_type,
       CAST(order_id AS TEXT) AS reference,
       requested_at AS event_date
FROM returns
WHERE requested_at IS NOT NULL
ORDER BY event_date DESC
LIMIT 20;

SELECT
    SUBSTR(ordered_at, 1, 7) AS month,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 2) AS revenue
FROM orders
WHERE ordered_at LIKE '2025%'
  AND status NOT IN ('cancelled')
GROUP BY SUBSTR(ordered_at, 1, 7)
UNION ALL
SELECT
    '== 합계 ==' AS month,
    COUNT(*) AS order_count,
    ROUND(SUM(total_amount), 2) AS revenue
FROM orders
WHERE ordered_at LIKE '2025%'
  AND status NOT IN ('cancelled')
ORDER BY month;

SELECT
    method,
    COUNT(*) AS tx_count,
    ROUND(SUM(amount), 2) AS total_amount
FROM payments
WHERE status = 'completed'
GROUP BY method
UNION ALL
SELECT
    '== 합계 ==' AS method,
    COUNT(*) AS tx_count,
    ROUND(SUM(amount), 2) AS total_amount
FROM payments
WHERE status = 'completed'
ORDER BY
    CASE WHEN method = '== 합계 ==' THEN 1 ELSE 0 END,
    tx_count DESC;

SELECT c.name, c.email, c.grade
FROM customers AS c
WHERE c.id IN (
    SELECT customer_id FROM reviews
    INTERSECT
    SELECT customer_id FROM complaints
)
ORDER BY c.name
LIMIT 15;

SELECT p.name, p.price
FROM products AS p
WHERE p.id IN (
    SELECT product_id FROM wishlists
    EXCEPT
    SELECT product_id FROM order_items
)
ORDER BY p.price DESC
LIMIT 15;

SELECT c.name, c.grade, c.email
FROM customers AS c
WHERE c.id IN (
    SELECT customer_id
    FROM orders
    WHERE ordered_at LIKE '2024%'
      AND status NOT IN ('cancelled')
    EXCEPT
    SELECT customer_id
    FROM orders
    WHERE ordered_at LIKE '2025%'
      AND status NOT IN ('cancelled')
)
ORDER BY c.name
LIMIT 20;

SELECT '주문' AS activity_type, COUNT(*) AS total_count
FROM orders
WHERE status NOT IN ('cancelled')
UNION ALL
SELECT '리뷰', COUNT(*)
FROM reviews
UNION ALL
SELECT '불만 접수', COUNT(*)
FROM complaints
UNION ALL
SELECT '위시리스트', COUNT(*)
FROM wishlists
ORDER BY total_count DESC;

SELECT
    c.name,
    c.grade,
    (SELECT COUNT(*) FROM orders
     WHERE customer_id = c.id
       AND ordered_at LIKE '2024%'
       AND status NOT IN ('cancelled')) AS orders_2024,
    (SELECT COUNT(*) FROM orders
     WHERE customer_id = c.id
       AND ordered_at LIKE '2025%'
       AND status NOT IN ('cancelled')) AS orders_2025
FROM customers AS c
WHERE c.id IN (
    SELECT customer_id FROM orders
    WHERE ordered_at LIKE '2024%' AND status NOT IN ('cancelled')
    INTERSECT
    SELECT customer_id FROM orders
    WHERE ordered_at LIKE '2025%' AND status NOT IN ('cancelled')
)
ORDER BY orders_2025 DESC, orders_2024 DESC
LIMIT 15;

SELECT o.order_number, o.total_amount, o.ordered_at
FROM orders AS o
WHERE o.id IN (
    SELECT order_id FROM complaints WHERE order_id IS NOT NULL
    EXCEPT
    SELECT order_id FROM returns
)
ORDER BY o.ordered_at DESC
LIMIT 15;

SELECT
    p.name,
    COUNT(*) AS interest_score
FROM (
    SELECT product_id FROM reviews
    UNION ALL
    SELECT product_id FROM wishlists
) AS combined
INNER JOIN products AS p ON combined.product_id = p.id
GROUP BY p.id, p.name
ORDER BY interest_score DESC
LIMIT 10;

SELECT
    'Q' || ((CAST(SUBSTR(created_at, 6, 2) AS INTEGER) - 1) / 3 + 1) AS quarter,
    '신규가입' AS metric,
    COUNT(*) AS value
FROM customers
WHERE created_at LIKE '2024%'
GROUP BY quarter
UNION ALL
SELECT
    'Q' || ((CAST(SUBSTR(ordered_at, 6, 2) AS INTEGER) - 1) / 3 + 1) AS quarter,
    '주문고객' AS metric,
    COUNT(DISTINCT customer_id) AS value
FROM orders
WHERE ordered_at LIKE '2024%'
  AND status NOT IN ('cancelled')
GROUP BY quarter
ORDER BY quarter, metric;