SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

SELECT
    ticket_id,
    JULIANDAY(resolved_at) - JULIANDAY(created_at) AS resolution_days
FROM tickets
WHERE resolution_days > 1;

Error: no such column: resolution_days

-- 방법 1: 계산식을 WHERE에 직접 작성
WHERE JULIANDAY(resolved_at) - JULIANDAY(created_at) > 1

-- 방법 2: 서브쿼리로 감싸기
SELECT * FROM (
    SELECT ticket_id,
           JULIANDAY(resolved_at) - JULIANDAY(created_at) AS resolution_days
    FROM tickets
) sub
WHERE resolution_days > 1;

-- 쿼리 A: 인덱스 없는 컬럼으로 검색
SELECT * FROM tickets WHERE description LIKE '%error%';

-- 쿼리 B: 인덱스 있는 컬럼으로 검색
SELECT * FROM tickets WHERE priority = 'urgent';

EXPLAIN QUERY PLAN SELECT * FROM tickets WHERE description LIKE '%error%';
-- 결과: SCAN tickets
-- 의미: 100만 건 전체를 하나씩 확인 (Full Table Scan)
-- 예상 시간: 8.5초

EXPLAIN QUERY PLAN SELECT * FROM tickets WHERE priority = 'urgent';
-- 결과: SEARCH tickets USING INDEX idx_tickets_priority
-- 의미: 인덱스로 바로 점프 (Index Scan)
-- 예상 시간: 0.02초

SCAN tickets          → Full Table Scan (느림 🐌)
SEARCH ... USING INDEX → Index Scan (빠름 🚀)
USING COVERING INDEX   → Index Only Scan (가장 빠름 ⚡)

-- ❌ 인덱스 무시됨
SELECT * FROM tickets WHERE YEAR(created_at) = 2024;
-- 결과: SCAN tickets (Full Table Scan)

-- ✅ 인덱스 사용됨
SELECT * FROM tickets
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 결과: SEARCH tickets USING INDEX idx_tickets_created_at

-- ❌ 인덱스 무시됨 (앞에 %)
SELECT * FROM tickets WHERE category LIKE '%billing%';

-- ✅ 인덱스 사용 가능 (뒤에만 %)
SELECT * FROM tickets WHERE category LIKE 'billing%';

-- 복합 인덱스: (category, status)
CREATE INDEX idx_cat_status ON tickets(category, status);

-- ✅ 첫 번째 컬럼 포함 → 인덱스 사용됨
SELECT * FROM tickets WHERE category = 'billing';
SELECT * FROM tickets WHERE category = 'billing' AND status = 'open';

-- ❌ 첫 번째 컬럼 없음 → 인덱스 무시됨
SELECT * FROM tickets WHERE status = 'open';

-- ❌ ticket_id는 INTEGER인데 문자열로 비교
SELECT * FROM tickets WHERE ticket_id = '12345';

-- ✅ 타입 일치
SELECT * FROM tickets WHERE ticket_id = 12345;

-- ❌ OR 조건 → 인덱스 효율 떨어짐
SELECT * FROM tickets
WHERE priority = 'urgent' OR priority = 'high';

-- ✅ IN 조건 → 더 효율적
SELECT * FROM tickets
WHERE priority IN ('urgent', 'high');

-- 카테고리별 티켓 통계
SELECT
    category,
    COUNT(*) as total_tickets,
    COUNT(DISTINCT customer_id) as unique_customers,
    ROUND(AVG(satisfaction_score), 2) as avg_satisfaction
FROM tickets
WHERE created_at >= '2024-01-01'
GROUP BY category
ORDER BY total_tickets DESC;

-- ❌ 비효율: 전체 데이터 집계 후 필터링
SELECT agent_id, COUNT(*) as ticket_count
FROM tickets
GROUP BY agent_id
HAVING agent_id IN (1, 2, 3, 4, 5);

-- ✅ 효율적: 먼저 필터링 후 집계
SELECT agent_id, COUNT(*) as ticket_count
FROM tickets
WHERE agent_id IN (1, 2, 3, 4, 5)  -- 먼저 필터!
GROUP BY agent_id;

-- "50건 이상 처리하고 평균 만족도 4.0 이상인 상담원"
SELECT
    agent_id,
    COUNT(*) as ticket_count,
    ROUND(AVG(satisfaction_score), 2) as avg_score
FROM tickets
WHERE created_at >= '2024-01-01'    -- 행 필터 (WHERE)
  AND status = 'resolved'
GROUP BY agent_id
HAVING COUNT(*) >= 50               -- 그룹 필터 (HAVING)
   AND AVG(satisfaction_score) >= 4.0
ORDER BY avg_score DESC;

-- ❌ 모든 컬럼 조회
SELECT * FROM tickets WHERE status = 'open';

-- ✅ 필요한 컬럼만
SELECT ticket_id, category, priority, created_at
FROM tickets WHERE status = 'open';

-- ❌ 항상 0건 반환
SELECT * FROM tickets WHERE resolved_at = NULL;

-- ✅ IS NULL 사용
SELECT * FROM tickets WHERE resolved_at IS NULL;

-- ❌ ticket_id는 이미 PK라서 유일함
SELECT DISTINCT ticket_id FROM tickets WHERE status = 'open';

-- ✅ DISTINCT 불필요
SELECT ticket_id FROM tickets WHERE status = 'open';

SELECT
    COUNT(*) as total_rows,           -- 전체 행 수
    COUNT(resolved_at) as resolved,   -- NULL 제외 (해결된 티켓만)
    COUNT(satisfaction_score) as rated -- 평점 있는 것만
FROM tickets;

-- ❌ 느림: 행마다 서브쿼리 실행 (Correlated Subquery)
SELECT t.*
FROM tickets t
WHERE t.agent_id IN (
    SELECT agent_id FROM agents WHERE department = 'support'
);

-- ✅ 빠름: JOIN 한 번
SELECT t.*
FROM tickets t
JOIN agents a ON t.agent_id = a.agent_id
WHERE a.department = 'support';

-- ❌ 인덱스 무시됨
WHERE DATE(created_at) = '2024-06-15'

-- ✅ 인덱스 사용됨
WHERE created_at >= '2024-06-15' AND created_at < '2024-06-16'

-- ❌ 100만 건 한 번에 조회
SELECT * FROM tickets;

-- ✅ 페이지네이션
SELECT * FROM tickets ORDER BY ticket_id LIMIT 100 OFFSET 0;

SELECT
    DATE(created_at) as date,
    COUNT(*) as new_tickets,
    SUM(CASE WHEN status IN ('resolved', 'closed') THEN 1 ELSE 0 END) as closed,
    ROUND(AVG(satisfaction_score), 2) as avg_satisfaction
FROM tickets
WHERE created_at >= DATE('now', '-30 days')
GROUP BY DATE(created_at)
ORDER BY date DESC;

SELECT
    agent_id,
    COUNT(*) as total_tickets,
    SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) as resolved,
    ROUND(100.0 * SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) / COUNT(*), 1) as resolution_rate,
    ROUND(AVG(JULIANDAY(resolved_at) - JULIANDAY(created_at)) * 24, 1) as avg_hours,
    ROUND(AVG(satisfaction_score), 2) as avg_score
FROM tickets
WHERE created_at >= DATE('now', '-30 days')
GROUP BY agent_id
HAVING COUNT(*) >= 10
ORDER BY resolution_rate DESC, avg_score DESC;

WITH sla_check AS (
    SELECT
        priority,
        ticket_id,
        CASE
            WHEN resolved_at IS NULL THEN 'pending'
            WHEN priority = 'urgent' AND
                 (JULIANDAY(resolved_at) - JULIANDAY(created_at)) * 24 <= 4 THEN 'met'
            WHEN priority = 'high' AND
                 (JULIANDAY(resolved_at) - JULIANDAY(created_at)) * 24 <= 24 THEN 'met'
            WHEN priority = 'medium' AND
                 (JULIANDAY(resolved_at) - JULIANDAY(created_at)) * 24 <= 72 THEN 'met'
            WHEN priority = 'low' AND
                 (JULIANDAY(resolved_at) - JULIANDAY(created_at)) * 24 <= 168 THEN 'met'
            ELSE 'breached'
        END as sla_status
    FROM tickets
    WHERE created_at >= DATE('now', '-30 days')
)
SELECT
    priority,
    COUNT(*) as total,
    SUM(CASE WHEN sla_status = 'met' THEN 1 ELSE 0 END) as met,
    SUM(CASE WHEN sla_status = 'breached' THEN 1 ELSE 0 END) as breached,
    ROUND(100.0 * SUM(CASE WHEN sla_status = 'met' THEN 1 ELSE 0 END) /
          NULLIF(SUM(CASE WHEN sla_status != 'pending' THEN 1 ELSE 0 END), 0), 1) as compliance_rate
FROM sla_check
GROUP BY priority
ORDER BY
    CASE priority WHEN 'urgent' THEN 1 WHEN 'high' THEN 2
                  WHEN 'medium' THEN 3 WHEN 'low' THEN 4 END;