Answering the Customer's Question in SQL

목표

남의 스키마 위에서 고객의 질문을 쿼리로 옮기고, 에러 없이 틀리는 함정을 피할 수 있게 됩니다.

왜 중요한가

문법이 틀린 쿼리는 즉시 알려 주지만, 위험한 것은 실행되는 쿼리입니다. 고객 앞에서 읽은 숫자가 틀렸다면 그 프로젝트에서 이후에 말하는 모든 숫자가 의심받습니다.

이 실습은 그중 두 가지를 직접 만나게 합니다. COUNT(*) 와 COUNT(열) 의 차이 — 후자는 그 열이 NULL 이 아닌 행만 셉니다. 그리고 없는 행은 조인되지 않는다는 것 — 티켓이 한 번도 없는 고객은 INNER JOIN 결과에 아예 나타나지 않으므로, 그런 고객을 세려면 LEFT JOIN 후 NULL 을 찾거나 NOT EXISTS 를 써야 합니다.

한 가지 더. NOT IN 서브쿼리에 NULL 이 하나라도 있으면 전체 결과가 0 건이 되는데, 에러도 경고도 없습니다. 사업적으로 그럴듯한 결론이 그대로 리포트에 실립니다. 습관적으로 NOT EXISTS 를 쓰는 편이 안전합니다.

스키마

• customers(id, name, plan, signed_at) — plan 은 free / pro / enterprise
• orders(id, customer_id, amount, status, created_at) — status 는 paid / pending / refunded
• tickets(id, customer_id, severity, opened_at, closed_at) — 닫히지 않은 티켓은 closed_at 이 NULL

단계

1. /root/sql 을 만들고 /opt/data/support.sql 을 /root/sql/support.db 로 적재하세요.
2. 전체 고객 수를 /root/sql/q1.txt 에 적으세요.
3. status 가 paid 인 주문의 금액 합계를 /root/sql/q2.txt 에 적으세요.
4. 확정 매출이 가장 큰 고객의 이름을 /root/sql/q3.txt 에 적으세요.
5. 아직 닫히지 않은 티켓 수를 /root/sql/q4.txt 에 적으세요.
6. SELECT COUNT(closed_at) FROM tickets; 의 값을 /root/sql/q5.txt 에 적으세요.
7. 티켓을 한 번도 연 적 없는 고객 수를 /root/sql/q6.txt 에 적으세요.
8. 확정 매출 합계가 가장 큰 요금제 이름을 /root/sql/q7.txt 에 적으세요.

참고

• 적재: sqlite3 /root/sql/support.db < /opt/data/support.sql
• 조회: sqlite3 /root/sql/support.db "SELECT COUNT(*) FROM customers;"
• 흔한 실수 1: 5번에서 closed_at = '' 로 비교하는 것. NULL 은 어떤 비교와도 참이 되지 않으므로 IS NULL 을 써야 합니다.
• 흔한 실수 2: 8번에서 결과가 직관과 다르다고 쿼리를 의심하는 것. 요금제 등급과 매출 규모는 다른 이야기이고, 고객에게 이 사실을 설명하는 것 자체가 가치 있는 발견입니다.

스냅샷 적재하기

/root/sql 을 만들고 /opt/data/support.sql 을 /root/sql/support.db 로 적재하세요.

sqlite3 는 표준입력으로 SQL 을 받습니다. /opt/data/support.sql 을 /root/sql/support.db 로 적재하세요.

전체 고객 수 세기

전체 고객 수를 /root/sql/q1.txt 에 적으세요.

가장 단순한 질문으로 시작합니다. 이 숫자가 이후 모든 비율의 분모가 됩니다.

확정 매출 합계 구하기

status 가 paid 인 주문의 금액 합계를 /root/sql/q2.txt 에 적으세요.

모든 주문이 매출은 아닙니다. status 열에 어떤 값들이 있는지 먼저 보세요.

최대 매출 고객 찾기

확정 매출이 가장 큰 고객의 이름을 /root/sql/q3.txt 에 적으세요.

고객 이름이 필요하므로 조인이 필요합니다. 확정 매출 기준으로 묶어 정렬하세요.

미해결 티켓 수 세기

아직 닫히지 않은 티켓 수를 /root/sql/q4.txt 에 적으세요.

닫히지 않은 티켓은 closed_at 이 NULL 입니다. 빈 문자열과 NULL 은 다르므로 IS NULL 을 써야 합니다.

COUNT(closed_at) 값 구하기

SELECT COUNT(closed_at) FROM tickets; 의 값을 /root/sql/q5.txt 에 적으세요.

COUNT(열) 은 그 열이 NULL 이 아닌 행만 셉니다. 전체가 80인데 이 값이 왜 다른지 생각해 보세요.

티켓 없는 고객 수 세기

티켓을 한 번도 연 적 없는 고객 수를 /root/sql/q6.txt 에 적으세요.

없는 행은 조인되지 않습니다. LEFT JOIN 후 오른쪽이 NULL 인 것을 세거나 NOT EXISTS 를 쓰세요.

요금제별 매출 1위 찾기

확정 매출 합계가 가장 큰 요금제 이름을 /root/sql/q7.txt 에 적으세요.

요금제로 묶어 확정 매출을 더합니다. 결과가 예상과 다를 수 있는데, 그것이 이 단계의 요점입니다.