EXISTS and Anti-Patterns

SELECT
    c.id,
    c.name,
    c.grade,
    c.created_at AS signup_date
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
      AND o.ordered_at LIKE '2024%'
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
)
ORDER BY c.id
LIMIT 20;

SELECT
    c.id,
    c.name,
    c.grade,
    c.created_at AS signup_date,
    ROUND(JULIANDAY('2025-12-31') - JULIANDAY(c.created_at), 0) AS days_since_signup
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
)
ORDER BY c.created_at
LIMIT 20;

SELECT
    c.id,
    c.name,
    c.grade,
    COUNT(o.id) AS confirmed_orders
FROM customers AS c
INNER JOIN orders AS o
    ON c.id = o.customer_id
   AND o.status = 'confirmed'
WHERE NOT EXISTS (
    SELECT 1
    FROM reviews AS r
    WHERE r.customer_id = c.id
)
GROUP BY c.id, c.name, c.grade
ORDER BY confirmed_orders DESC
LIMIT 15;

SELECT
    w.customer_id,
    c.name     AS customer_name,
    w.product_id,
    p.name     AS product_name,
    p.price,
    w.created_at AS wishlisted_at
FROM wishlists AS w
INNER JOIN customers AS c ON w.customer_id = c.id
INNER JOIN products  AS p ON w.product_id  = p.id
WHERE w.is_purchased = 0
  AND NOT EXISTS (
      SELECT 1
      FROM order_items AS oi
      INNER JOIN orders AS o ON oi.order_id = o.id
      WHERE o.customer_id = w.customer_id
        AND oi.product_id = w.product_id
        AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
  )
ORDER BY w.created_at DESC
LIMIT 15;

SELECT
    c.id,
    c.name,
    c.grade,
    COUNT(o.id) AS order_count,
    ROUND(SUM(o.total_amount), 0) 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')
  AND NOT EXISTS (
      SELECT 1
      FROM complaints AS cp
      WHERE cp.customer_id = c.id
  )
GROUP BY c.id, c.name, c.grade
ORDER BY total_spent DESC
LIMIT 10;

SELECT
    c.id,
    c.name,
    c.grade
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    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.customer_id = c.id
      AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    GROUP BY o.customer_id
    HAVING COUNT(DISTINCT p.category_id) >= 3
)
ORDER BY c.grade DESC, c.name
LIMIT 20;

SELECT
    o.id,
    o.order_number,
    o.total_amount,
    o.ordered_at,
    o.status
FROM orders AS o
WHERE EXISTS (
    SELECT 1
    FROM payments AS p
    WHERE p.order_id = o.id
      AND p.status = 'completed'
)
AND NOT EXISTS (
    SELECT 1
    FROM payments AS p
    WHERE p.order_id = o.id
      AND p.status IN ('failed', 'refunded')
)
AND o.ordered_at LIKE '2024%'
ORDER BY o.ordered_at DESC
LIMIT 15;

SELECT
    p.id    AS product_id,
    p.name  AS product_name,
    p.price,
    COUNT(DISTINCT oi.order_id) AS co_purchase_count
FROM order_items AS oi
INNER JOIN products AS p ON oi.product_id = p.id
WHERE oi.product_id != 1
  AND EXISTS (
      SELECT 1
      FROM order_items AS oi2
      WHERE oi2.order_id   = oi.order_id
        AND oi2.product_id = 1
  )
GROUP BY p.id, p.name, p.price
ORDER BY co_purchase_count DESC
LIMIT 10;

WITH RECURSIVE months AS (
    SELECT '2024-01' AS ym
    UNION ALL
    SELECT SUBSTR(DATE(ym || '-01', '+1 month'), 1, 7)
    FROM months
    WHERE ym < '2024-12'
)
SELECT
    c.id,
    c.name,
    c.grade
FROM customers AS c
WHERE NOT EXISTS (
    -- 주문이 없는 월이 하나라도 있으면 제외
    SELECT 1
    FROM months AS m
    WHERE NOT EXISTS (
        SELECT 1
        FROM orders AS o
        WHERE o.customer_id = c.id
          AND SUBSTR(o.ordered_at, 1, 7) = m.ym
          AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
    )
)
ORDER BY c.grade DESC, c.name;

SELECT
    c.id,
    c.name,
    c.grade,
    MAX(o.ordered_at) AS last_order_date,
    COUNT(o.id) AS orders_in_2024,
    ROUND(SUM(o.total_amount), 0) AS spent_in_2024
FROM customers AS c
INNER JOIN orders AS o
    ON c.id = o.customer_id
   AND o.ordered_at LIKE '2024%'
   AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o2
    WHERE o2.customer_id = c.id
      AND o2.ordered_at LIKE '2025%'
      AND o2.status NOT IN ('cancelled', 'returned', 'return_requested')
)
GROUP BY c.id, c.name, c.grade
ORDER BY spent_in_2024 DESC
LIMIT 15;

SELECT
    c.id,
    c.name,
    c.grade,
    COUNT(DISTINCT o.id) AS order_count,
    ROUND(SUM(o.total_amount), 0) 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')
  -- 카드가 아닌 결제가 한 건도 없어야 함
  AND NOT EXISTS (
      SELECT 1
      FROM payments AS p
      INNER JOIN orders AS o2 ON p.order_id = o2.id
      WHERE o2.customer_id = c.id
        AND p.method != 'card'
  )
  -- 결제 이력이 있어야 함
  AND EXISTS (
      SELECT 1
      FROM payments AS p
      INNER JOIN orders AS o3 ON p.order_id = o3.id
      WHERE o3.customer_id = c.id
        AND p.method = 'card'
  )
GROUP BY c.id, c.name, c.grade
ORDER BY total_spent DESC
LIMIT 15;

SELECT
    c.id,
    c.name,
    c.grade,
    (SELECT COUNT(DISTINCT cp.category) FROM complaints AS cp WHERE cp.customer_id = c.id) AS category_count
FROM customers AS c
WHERE NOT EXISTS (
    -- 문의가 없는 카테고리가 하나라도 있으면 제외
    SELECT DISTINCT cp_all.category
    FROM complaints AS cp_all
    WHERE NOT EXISTS (
        SELECT 1
        FROM complaints AS cp
        WHERE cp.customer_id = c.id
          AND cp.category = cp_all.category
    )
)
ORDER BY c.name;

SELECT
    c.id,
    c.name,
    c.grade,
    COUNT(DISTINCT o.id) AS order_count,
    ROUND(SUM(o.total_amount), 0) AS total_spent,
    ROUND(AVG(o.total_amount), 0) AS avg_order_value
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
  -- 100만원 미만 상품 구매가 없어야 함
  AND NOT EXISTS (
      SELECT 1
      FROM order_items AS oi
      INNER JOIN orders AS o2 ON oi.order_id = o2.id
      INNER JOIN products AS p ON oi.product_id = p.id
      WHERE o2.customer_id = c.id
        AND p.price < 1000000
        AND o2.status NOT IN ('cancelled', 'returned', 'return_requested')
  )
GROUP BY c.id, c.name, c.grade
ORDER BY total_spent DESC
LIMIT 10;

SELECT
    c.name     AS customer_name,
    p.name     AS product_name,
    COUNT(DISTINCT oi.order_id) AS purchase_count,
    SUM(oi.quantity) AS total_qty
FROM order_items AS oi
INNER JOIN orders    AS o ON oi.order_id   = o.id
INNER JOIN customers AS c ON o.customer_id = c.id
INNER JOIN products  AS p ON oi.product_id = p.id
WHERE o.status NOT IN ('cancelled', 'returned', 'return_requested')
  AND EXISTS (
      -- 같은 고객이 같은 상품을 다른 주문에서 구매
      SELECT 1
      FROM order_items AS oi2
      INNER JOIN orders AS o2 ON oi2.order_id = o2.id
      WHERE o2.customer_id = o.customer_id
        AND oi2.product_id = oi.product_id
        AND oi2.order_id   != oi.order_id
        AND o2.status NOT IN ('cancelled', 'returned', 'return_requested')
  )
GROUP BY c.id, c.name, p.id, p.name
ORDER BY purchase_count DESC
LIMIT 15;

-- 방법 1: NOT EXISTS
SELECT p.id, p.name
FROM products AS p
WHERE p.is_active = 1
  AND NOT EXISTS (
      SELECT 1
      FROM reviews AS r
      WHERE r.product_id = p.id
        AND r.created_at LIKE '2024%'
  )
ORDER BY p.id;

-- 방법 2: LEFT JOIN ... IS NULL
SELECT p.id, p.name
FROM products AS p
LEFT JOIN reviews AS r
    ON r.product_id = p.id
   AND r.created_at LIKE '2024%'
WHERE p.is_active = 1
  AND r.id IS NULL
ORDER BY p.id;

-- 동일성 검증: 차집합이 비어야 함
SELECT p.id, p.name
FROM products AS p
WHERE p.is_active = 1
  AND NOT EXISTS (
      SELECT 1 FROM reviews AS r
      WHERE r.product_id = p.id AND r.created_at LIKE '2024%'
  )

EXCEPT

SELECT p.id, p.name
FROM products AS p
LEFT JOIN reviews AS r
    ON r.product_id = p.id AND r.created_at LIKE '2024%'
WHERE p.is_active = 1
  AND r.id IS NULL;