Skip to content

Instantly share code, notes, and snippets.

@draptik
Created January 15, 2020 21:06
Show Gist options
  • Save draptik/a1ce7256fcb52fa7d4303851dacfa4d0 to your computer and use it in GitHub Desktop.
Save draptik/a1ce7256fcb52fa7d4303851dacfa4d0 to your computer and use it in GitHub Desktop.
solution to sql murder mystery https://mystery.knightlab.com/walkthrough.html
-- https://mystery.knightlab.com/walkthrough.html
-- witness 1
--SELECT * from interview where person_id = 14887
--select * from get_fit_now_member where id like '48Z%' and membership_status = 'gold'
--select * from drivers_license where plate_number like '%H42W%'
-- witness 1
--select p.* from person p
--join drivers_license dl on p.license_id = dl.id
--join get_fit_now_member gf on p.id = gf.person_id
--where gf.id like '48Z%' and gf.membership_status = 'gold'
--and dl.plate_number like '%H42W%'
-- witness 2
--SELECT * FROM person WHERE name like 'Annabel%'AND address_street_name = 'Franklin Ave'
--SELECT * from interview where person_id = 16371
--select * from get_fit_now_check_in where check_in_date = '20180109'
--select * from person p
--join get_fit_now_member gf
--on gf.person_id = p.id
--join get_fit_now_check_in gfci
--on gfci.membership_id = gf.id
--where gfci.check_in_date = '20180109'
-- real villain
--select * from facebook_event_checkin where person_id = 67318
--select * from facebook_event_checkin where event_id = 4719 and date = '20180115'
--select * from interview where person_id in (14887, 67318) -- 67318
select * from person p
join drivers_license dl on dl.id = p.license_id
join facebook_event_checkin fb on fb.person_id = p.id
where dl.height in ('65', '66', '67')
and dl.hair_color = 'red' and car_make = 'Tesla' and car_model = 'Model S' and dl.gender = 'female'
and event_name like 'SQL Symphony Concert' and date like '201712%'
order by person_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment