solution to sql murder mystery https://mystery.knightlab.com/walkthrough.html

sql-murder-mystery-solution.sql

-- 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