SQL 게임이 있다고 해서, 특히 추리 게임이라는 게 흥미로워서 풀어봤다.
SQLite 기반인데, 자주 사용한 MySQL이랑 유사해서 금방 풀었다.
전체 ERD가 주어지고, 그걸 보고 알아서 sql문을 작성해서 solution 테이블에 insert하고 최종 확인하는 그런 방식이다.
ERD는 다음과 같이 주어진다.
사건 개요
2018년 1월 15일, SQL City에서 살인 사건이 발생했다. 단서는 한 줄도 주어지지 않고, 오직 SQL 쿼리만으로 범인을 추적해야 한다.
Step 1. 범죄 현장 보고서 조회
처음에 주어진게 날짜와 장소밖에 없어서, 먼저 리포트를 조회해보기로 했다.
먼저 crime_scene_report 테이블에서 사건 당일, 해당 도시의 기록을 조회했다.
SELECT description
FROM crime_scene_report
WHERE city LIKE '%SQL City%'
AND date = 20180115;여기서 사건 유형(murder)과 목격자 단서를 확인했다.
CCTV 분석 결과 목격자는 두 명:
- 첫 번째 목격자:
Northwestern Dr의 마지막 집 거주자 - 두 번째 목격자:
Franklin Ave에 사는 Annabel
Step 2. Annabel의 진술 확보
이름 단서가 있는 Annabel부터. person 테이블에서 ID를 찾고 interview 테이블과 조인했다.
SELECT it.transcript
FROM interview it
JOIN person p ON p.id = it.person_id
WHERE p.name LIKE '%Annabel%';진술:
I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.
(살인이 일어나는 걸 봤고, 지난주 1월 9일에 운동하던 헬스장에서 범인을 알아봤어요.)
→ 범인은 Get Fit Now Gym 회원이며, 1월 9일에 헬스장에 있었다.
Step 3. 첫 번째 목격자의 진술
Northwestern Dr의 마지막 집 거주자도 같은 방식으로 인터뷰를 조회했다.
진술:
총소리가 들린 후 한 남자가 뛰쳐나오는 것을 봤다. "Get Fit Now Gym" 가방을 들고 있었고, 가방의 회원 번호는 "48Z"로 시작했다. 골드 회원만 그 가방을 사용한다. 그 남자는 번호판에 "H42W"가 포함된 차에 탔다.
이제 결정적인 단서가 모두 모였다:
| 단서 | 값 |
|---|---|
| 회원권 등급 | gold |
| 회원 번호 prefix | 48Z |
| 차량 번호판 포함 | H42W |
| 헬스장 방문일 | 2018-01-09 |
Step 4. 범인 특정
세 테이블을 조인해서 조건을 만족하는 사람을 찾았다.
SELECT p.id, p.name
FROM get_fit_now_member gfm
JOIN person p ON gfm.person_id = p.id
JOIN drivers_license d ON p.license_id = d.id
WHERE d.plate_number LIKE '%H42W%'
AND gfm.membership_status = 'gold';get_fit_now_member → person → drivers_license로 이어지는 조인 한 번으로 범인이 특정됐다.
더 엄밀하게 가려면
gfm.id LIKE '48Z%'조건과get_fit_now_check_in테이블에서check_in_date = 20180109까지 걸어주면 좋다. 단서를 다 활용해야 운에 의존하지 않는다. 그렇지만 이렇게 하고 풀기는 했다.. ^^
이렇게 해서 범인 이름이 나왔다!
음.. 그런데 풀고 나니까 배후가 있단다.
축하합니다, 범인을 찾았네요! 하지만 끝이 아닙니다... 도전할 준비가 됐다면, 범인의 인터뷰 기록을 조회해서 이 범죄의 진짜 흑막을 찾아보세요. SQL 실력에 자신 있다면, 이 마지막 단계를 쿼리 2개 이내로 완료해보세요. 새 용의자로 동일한 INSERT 문을 사용해 정답을 확인할 수 있습니다.
Step 5: 사건의 전말 찾기
일단 범인이 배후를 불었을 테니? 인터뷰 내용을 찾아봤다.
SELECT transcript
FROM interview
WHERE person_id = 67318;돈 많은 여자한테 고용됐다. 이름은 모르지만 키는 약 5'5"(65인치) ~ 5'7"(67인치), 빨간 머리, 테슬라 모델 S를 몬다. 그 여자가 2017년 12월에 SQL Symphony Concert에 3번 참석했다는 건 안다.
진짜 배후를 특정할 단서는 다음과 같다.
| 속성 | 값 |
|---|---|
| 성별 | female |
| 키 | 65 ~ 67 inch |
| 머리색 | red |
| 차량 | Tesla Model S |
| 참석 이벤트 | SQL Symphony Concert |
| 참석 시기 | 2017년 12월, 총 3회 |
쿼리 1개로 해결하는 방법이 있다.
- 외형 단서(키, 머리색, 성별, 차량)는 person + drivers_license 조인으로 거른다.
- 콘서트 단서는 facebook_event_checkin에서 event_name = 'SQL Symphony Concert'이고 date가 2017년 12월(20171201 ~ 20171231)인 row를 person_id로 GROUP BY해서 COUNT(*) = 3인 사람을 찾는다.
- 수익은 income을 묶어서 order by로 높은 순서로 정렬한다. 돈이 많은 여자이기 때문이다.!
- income은 없는 사람도 있어서, 아니면 진범이 수익 테이블에 안나와 있을 수도 있어서 left 조인으로 했다. 없으면 null로 뜬다.
SELECT p.id, p.name, i.annual_income
FROM person p
JOIN drivers_license d ON p.license_id = d.id
JOIN facebook_event_checkin fec ON fec.person_id = p.id
LEFT JOIN income i ON i.ssn = p.ssn
WHERE d.gender = 'female'
AND d.hair_color = 'red'
AND d.car_make = 'Tesla'
AND d.car_model = 'Model S'
AND d.height BETWEEN 65 AND 67
AND fec.event_name = 'SQL Symphony Concert'
AND fec.date BETWEEN 20171201 AND 20171231
GROUP BY p.id, p.name, i.annual_income
HAVING COUNT(*) = 3
ORDER BY i.annual_income DESC;이렇게 최종 SQL문을 입력했더니 범인이 딱 나왔다.
흑막까지 맞추니까 축하해줬다.
배운 점 , 후기
LIKE '%...%'는 텍스트 단서를 다룰 때 강력하다. 부분 일치 하나로 후보를 빠르게 좁힐 수 있다.- 도메인 모델이 잘 잡혀 있으면 복잡한 조사도 결국 JOIN 몇 번이다.
person ↔ membership ↔ license처럼 식별자 중심으로 연결돼 있으면 추적 경로가 한눈에 보인다. - 음.. SQL 코테 보는 것 같기도 하고, 재밌었다 .. ^^
