SQL Debugging -- Intermediate

SELECT c.name, o.order_number, o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

SELECT c.name, o.order_number, oi.quantity
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
INNER JOIN order_items AS oi ON o.id = oi.order_id
LIMIT 10;

SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY order_count ASC
LIMIT 20;

SELECT p.name, cat.name, s.company_name
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
INNER JOIN suppliers AS s ON p.supplier_id = s.id
LIMIT 10;

SELECT s.company_name, COUNT(*) AS product_count
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
GROUP BY s.id, s.company_name;

SELECT c.name, c.email, MAX(o.ordered_at) AS last_order
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email;

-- 방법 1: 명시적 JOIN + Aggregation
SELECT p.name, p.price, ROUND(AVG(r.rating), 1) AS avg_rating
FROM products AS p
INNER JOIN reviews AS r ON p.id = r.product_id
WHERE p.price > 100000
GROUP BY p.id, p.name, p.price
ORDER BY p.price DESC
LIMIT 10;

-- Approach 2: EXISTS to check existence only
SELECT p.name, p.price
FROM products AS p
WHERE p.price > 100000
  AND EXISTS (SELECT 1 FROM reviews WHERE product_id = p.id)
ORDER BY p.price DESC
LIMIT 10;

-- Only need customers with in-transit orders, so INNER JOIN is appropriate
SELECT c.name, o.order_number, sh.tracking_number
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
INNER JOIN shipping AS sh ON o.id = sh.order_id
WHERE sh.status = 'shipped';

SELECT
    p.name,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    sub_r.avg_rating
FROM products AS p
INNER JOIN order_items AS oi ON p.id = oi.product_id
LEFT JOIN (
    SELECT product_id, ROUND(AVG(rating), 1) AS avg_rating
    FROM reviews
    GROUP BY product_id
) AS sub_r ON p.id = sub_r.product_id
GROUP BY p.id, p.name, sub_r.avg_rating
ORDER BY revenue DESC
LIMIT 10;

SELECT c.name, SUM(o.total_amount) AS total_spent
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING SUM(o.total_amount) >= 1000000
ORDER BY total_spent DESC;

SELECT p.name, p.price
FROM products AS p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products AS p2
    WHERE p2.category_id = p.category_id
)
ORDER BY p.price DESC;

-- Approach 1: Include time end in the end value
SELECT COUNT(*) AS q3_orders
FROM orders
WHERE ordered_at BETWEEN '2024-07-01' AND '2024-09-30 23:59:59';

-- Approach 2: Month pattern matching with LIKE (safer)
SELECT COUNT(*) AS q3_orders
FROM orders
WHERE ordered_at >= '2024-07-01'
  AND ordered_at < '2024-10-01';

SELECT
    order_number,
    ordered_at,
    CAST(JULIANDAY('2025-01-01') - JULIANDAY(ordered_at) AS INTEGER) AS days_elapsed
FROM orders
LIMIT 10;

SELECT
    STRFTIME('%Y-%m', created_at) AS month,
    COUNT(*) AS new_customers
FROM customers
GROUP BY STRFTIME('%Y-%m', created_at)
HAVING COUNT(*) > 100
ORDER BY month;

-- 전체 상품 중 상위 10%
SELECT name, price
FROM products
WHERE price >= (
    SELECT price FROM products
    ORDER BY price DESC
    LIMIT 1 OFFSET (SELECT COUNT(*) / 10 FROM products)
)
ORDER BY price DESC;

SELECT cat.name,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       COUNT(r.id) AS review_count
FROM categories AS cat
INNER JOIN products AS p ON cat.id = p.category_id
INNER JOIN reviews AS r ON p.id = r.product_id
GROUP BY cat.id, cat.name
HAVING AVG(r.rating) >= 4.0
   AND COUNT(r.id) >= 10
ORDER BY avg_rating DESC;

SELECT '주문' AS event_type,
       order_number AS reference,
       total_amount AS amount,
       status,
       ordered_at AS event_at
FROM orders
UNION ALL
SELECT '결제' AS event_type,
       CAST(order_id AS TEXT) AS reference,
       amount,
       status,
       paid_at AS event_at
FROM payments
WHERE paid_at IS NOT NULL
ORDER BY event_at DESC
LIMIT 20;

SELECT p.name, p.price, COUNT(r.id) AS review_count
FROM products AS p
LEFT JOIN reviews AS r ON p.id = r.product_id AND r.rating >= 3
GROUP BY p.id, p.name, p.price
HAVING COUNT(r.id) <= 5
ORDER BY p.price DESC
LIMIT 20;

SELECT
    c.name,
    c.email,
    c.grade,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email, c.grade
ORDER BY total_spent DESC
LIMIT 20;

INSERT INTO products (category_id, supplier_id, name, sku, brand, price, cost_price, stock_qty, is_active, created_at, updated_at)
VALUES (1, 1, '테스트 키보드', 'TEST-KB-001', 'TestBrand', 89000, 45000, 100, 1, '2025-01-01', '2025-01-01');

SELECT
    SUBSTR(ordered_at, 1, 7) AS month,
    SUM(total_amount) AS revenue,
    COUNT(*) AS order_count
FROM orders
WHERE status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY SUBSTR(ordered_at, 1, 7)
ORDER BY month;

SELECT
    p.method,
    COUNT(*) AS payment_count,
    SUM(p.amount) AS total_amount
FROM payments AS p
WHERE p.status = 'completed'
GROUP BY p.method
ORDER BY total_amount DESC;

-- Approach 1: Use NOT EXISTS (NULL-safe)
SELECT c.name, c.email, c.grade
FROM customers AS c
WHERE c.grade = 'VIP'
  AND NOT EXISTS (
      SELECT 1 FROM orders AS o
      WHERE o.customer_id = c.id
        AND o.ordered_at >= DATE('now', '-6 months')
  );

-- Approach 2: Exclude NULL from NOT IN
SELECT c.name, c.email, c.grade
FROM customers AS c
WHERE c.grade = 'VIP'
  AND c.id NOT IN (
      SELECT customer_id
      FROM orders
      WHERE ordered_at >= DATE('now', '-6 months')
        AND customer_id IS NOT NULL
  );

SELECT
    s.company_name,
    COUNT(DISTINCT oi.id) AS total_sales,
    COUNT(DISTINCT ret.id) AS return_count,
    ROUND(100.0 * COUNT(DISTINCT ret.id)
        / NULLIF(COUNT(DISTINCT oi.id), 0), 2) AS return_rate
FROM suppliers AS s
LEFT JOIN products AS p ON s.id = p.supplier_id
LEFT JOIN order_items AS oi ON p.id = oi.product_id
LEFT JOIN returns AS ret ON ret.order_id = oi.order_id
GROUP BY s.id, s.company_name
ORDER BY return_rate DESC;

SELECT
    c.grade,
    COUNT(DISTINCT o.id) AS order_count,
    ROUND(SUM(o.total_amount) * 1.0 / NULLIF(COUNT(DISTINCT o.id), 0), 0) AS avg_order_amount,
    sub_r.avg_rating,
    sub_r.review_count
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
LEFT JOIN (
    SELECT customer_id,
           ROUND(AVG(rating), 2) AS avg_rating,
           COUNT(*) AS review_count
    FROM reviews
    GROUP BY customer_id
) AS sub_r ON c.id = sub_r.customer_id
GROUP BY c.grade, sub_r.avg_rating, sub_r.review_count
ORDER BY avg_order_amount DESC;

SELECT
    c.grade,
    COUNT(DISTINCT o.id) AS order_count,
    ROUND(AVG(DISTINCT o.total_amount), 0) AS avg_order_amount,
    (SELECT ROUND(AVG(r.rating), 2)
     FROM reviews AS r
     INNER JOIN customers AS c2 ON r.customer_id = c2.id
     WHERE c2.grade = c.grade) AS avg_rating
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.grade
ORDER BY avg_order_amount DESC;