SQL Mystery

Find crime report:

SELECT description
FROM crime_scene_report
WHERE city = "SQL City"
AND date = 20180115
AND type = "murder";

First witness:

SELECT interview.transcript
FROM person
JOIN interview ON interview.person_id = person.id
WHERE person.address_street_name = "Northwestern Dr"
GROUP BY person.name
ORDER BY person.address_number DESC
LIMIT 1;

I heard a gunshot and then saw a man run out. He had a “Get Fit Now Gym” bag. The membership number on the bag started with “48Z”. Only gold members have those bags. The man got into a car with a plate that included “H42W”.

Second witness:

SELECT interview.transcript
FROM person
JOIN interview ON interview.person_id = person.id
WHERE name LIKE "Annabel%"
AND address_street_name = "Franklin Ave";

I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

Finding the murderer:

SELECT person.name
FROM person
JOIN drivers_license ON drivers_license.id = person.license_id
WHERE person.id IN (
	SELECT get_fit_now_member.person_id
	FROM get_fit_now_check_in
	JOIN get_fit_now_member
	ON get_fit_now_member.id = get_fit_now_check_in.membership_id
	WHERE get_fit_now_check_in.check_in_date = 20180109
	AND get_fit_now_member.id LIKE "48Z%"
	AND get_fit_now_member.membership_status = "gold"
	AND NOT get_fit_now_member.name = "Annabel Miller"
)
AND drivers_license.plate_number LIKE "%H42W%";

Checking the solution:

INSERT INTO solution VALUES (1, (
	SELECT person.name
	FROM person
	JOIN drivers_license ON drivers_license.id = person.license_id
	WHERE person.id IN (
		SELECT get_fit_now_member.person_id
		FROM get_fit_now_check_in
		JOIN get_fit_now_member
		ON get_fit_now_member.id = get_fit_now_check_in.membership_id
		WHERE get_fit_now_check_in.check_in_date = 20180109
		AND get_fit_now_member.id LIKE "48Z%"
		AND get_fit_now_member.membership_status = "gold"
		AND NOT get_fit_now_member.name = "Annabel Miller"
	)
	AND drivers_license.plate_number LIKE "%H42W%"
));
 
SELECT value FROM solution;

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.

Checking the transcript for the actual murderer:

SELECT interview.transcript
FROM person
JOIN drivers_license ON drivers_license.id = person.license_id
JOIN interview ON interview.person_id = person.id
WHERE person.id IN (
	SELECT get_fit_now_member.person_id
	FROM get_fit_now_check_in
	JOIN get_fit_now_member
	ON get_fit_now_member.id = get_fit_now_check_in.membership_id
	WHERE get_fit_now_check_in.check_in_date = 20180109
	AND get_fit_now_member.id LIKE "48Z%"
	AND get_fit_now_member.membership_status = "gold"
	AND NOT get_fit_now_member.name = "Annabel Miller"
)
AND drivers_license.plate_number LIKE "%H42W%";

I was hired by a woman with a lot of money. I don’t know her name but I know she’s around 5’5” (65”) or 5’7” (67”). She has red hair and she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.

Finding the actual murderer:

SELECT person.name
FROM person
JOIN drivers_license ON drivers_license.id = person.license_id
WHERE (
	drivers_license.gender = "female"
    AND drivers_license.hair_color = "red"
    AND drivers_license.height BETWEEN 65 AND 67
    AND drivers_license.car_make = "Tesla"
    AND drivers_license.car_model = "Model S"
	AND (
		SELECT COUNT(*)
		FROM facebook_event_checkin
		WHERE person_id = person.id
		AND event_name = "SQL Symphony Concert"
	    AND date BETWEEN 20171201 AND 20171231
	) = 3
);

Warning

This doesn’t actually work in the web version due to the query being too complex, the SQLite database can be downloaded locally from the Github Repository: https://github.com/NUKnightLab/sql-mysteries

Checking the solution:

INSERT INTO solution VALUES (1, (
    SELECT person.name
    FROM person
    JOIN drivers_license ON drivers_license.id = person.license_id
    WHERE (
        drivers_license.gender = "female"
        AND drivers_license.hair_color = "red"
        AND drivers_license.height BETWEEN 65 AND 67
        AND drivers_license.car_make = "Tesla"
        AND drivers_license.car_model = "Model S"
        AND (
	        SELECT COUNT(*)
	        FROM facebook_event_checkin
	        WHERE person_id = person.id
	        AND event_name = "SQL Symphony Concert"
	        AND date BETWEEN 20171201 AND 20171231
        ) = 3
    )
));
 
SELECT value FROM solution;

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!