SQL Murder Mystery – Solução

Passos que eu segui para solucionar o desafio SQL Murder Mystery, proposto no site:
The SQL Murder Mystery (knightlab.com)

1. Verifiquei quem foi à academia no dia 18/01/2018 e quais os seus horários de saída.

SELECT A.membership_id, A.check_out_time, 
       B.person_id, B.name
FROM get_fit_now_check_in AS A
  INNER JOIN get_fit_now_member AS B
    ON A.membership_id = B.id 
WHERE A.check_in_date = 20180118 

2. Descobri todos os assassinatos que ocorreram naquela data em SQL City, consultando a tabela crime_scene_report, e lá acabei encontrando a descrição do crime que estamos tentando resolver.

SELECT date, type, description 
FROM crime_scene_report 
WHERE city = 'SQL City'
  AND type = 'murder' 

3. Descobri a primeira testemunha. Segundo a dica deixada lá em crime_scene_report, a primeira testemunha mora na última casa da “Northwestern Dr”.

SELECT id, name 
FROM person 
WHERE address_street_name = 'Northwestern Dr' 
ORDER BY address_number DESC

4. Descobri a segunda testemunha. Segundo a dica deixada lá em crime_scene_report, a segunda testemunha se chama “Annabel” e mora na “Franklin Ave”.

SELECT id,name 
FROM person 
WHERE address_street_name = 'Franklin Ave' 
  AND name LIKE 'Annabel%'

5. Utlizando a tabela de eventos, descobri que as duas testemunhas participaram de um mesmo evento no dia do crime.

SELECT A.person_id, A.event_id, A.event_name, 
       B.id, B.name
FROM facebook_event_checkin AS A
  INNER JOIN person AS B
    ON A.person_id = B.id
WHERE date = 20180115
  AND person_id IN (16371,14887)

6. Daí, verifiquei todos os participantes daquele evento para chegar a uma relação de suspeitos.

SELECT A.person_id, A.event_id, A.event_name, 
       B.id, B.name
FROM facebook_event_checkin AS A
  INNER JOIN person AS B
    ON A.person_id = B.id
WHERE date = 20180115
  AND event_id = 4719

7. Com isso, descobri que apenas três pessoas participaram do evento “The Funky Grooves Tour”. Como as duas primeiras eram as testemunhas, essa terceira pessoa passou a ser a principal suspeita.

8. Voltando ao passo 1, pude perceber que nenhuma das três pessoas foi à academia naquele dia. Daí pude inferir que o principal suspeito era realmente o assassino, recebendo a seguinte resposta do sistema:

Congrats, you found the murderer! But wait, there’s more… If you think you’re up for a challenge, try querying the interview transcript of the murderer to find the real villain behind this crime. If you feel especially confident in your SQL skills, try to complete this final step with no more than 2 queries. Use this same INSERT statement with your new suspect to check your answer.

KnightLab.com

Conforme visto na mensagem acima, ao concluir o desafio, ele abre uma segunda etapa. Dessa vez para descobrir quem foi o mandante do crime. Ele ainda deixa a dica para consultar a tabela de entrevistas. Então, segue a segunda etapa:

1. Seguindo a dica do desafio, o primeiro passo foi ver o depoimento do assassino com a seguinte query:

SELECT person_id, transcript, name 
FROM interview A
  INNER JOIN person B
    ON A.person_id = B.id
WHERE person_id = 67318 

2. O assassino disse que não sabe o nome do mandante mas deixou algumas dicas: é mulher, financeiramente rica, tem a altura entre 5’5″ (65″) e 5’7″ (67″), tem cabelo vermelho, dirigia um Tesla S e participou do evento “SQL Symphony Concert” 3 vezes em dezembro de 2017. Então fiz a query abaixo até a linha 13 para descobrir as características de todos os participantes do referido evento, e logo em seguinda refinei a consulta acrescentando as linhas 14 em diante. Fazendo isso, o nome da mandante do crime aparece imediatamente!

SELECT event_id, 
       B.id, B.name, 
	   height, hair_color, car_make, car_model, 
	   annual_income, 
	   COUNT(1) AS Quantidade
FROM facebook_event_checkin A
  INNER JOIN person B
    ON A.person_id = B.id
  INNER JOIN drivers_license C
    ON B.license_id = C.id 
  INNER JOIN income D
    ON B.ssn = D.ssn
WHERE date BETWEEN 20171201 AND 20171231 
  AND event_name = 'SQL Symphony Concert'
  AND hair_color = 'red'
  AND car_make = 'Tesla'
  AND car_model = 'Model S'
  AND height BETWEEN 65 AND 67
GROUP BY event_id, 
       B.id, B.name, 
	   height, hair_color, car_make, car_model, 
	   annual_income
HAVING Quantidade = 3

Congrats, you found the brains behind the murder! Everyone in SQL City hails you as the greatest SQL detective of all time. Time to break out the champagne!

KnightLab.com