mystery.knightlab.com_solution.sql

select *
from crime_scene_report
where city = 'SQL City'
and date = '20180115'
and type = 'murder'

-- Security footage shows that there were 2 witnesses. The first witness lives at the last house on "Northwestern Dr". The second witness, named Annabel, lives somewhere on "Franklin Ave".

select *
from person
where name like '%Annabel%'
and address_street_name = 'Franklin Ave'

-- id       name              license_id    address_number     address_street_name ssn
-- 16371    Annabel Miller    490173        103                Franklin Ave        318771143

select *
from person
where address_street_name = 'Northwestern Dr'
order by address_number desc
limit 1

-- id       name              license_id    address_number  address_street_name    ssn
-- 14887    Morty Schapiro    118009        4919            Northwestern Dr        111564949

select name, transcript
from interview
join person on person.id = interview.person_id
where person_id in (14887, 16371)

-- name              transcript
-- Morty Schapiro    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".
-- Annabel Miller    I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

select *
from get_fit_now_check_in c
join get_fit_now_member m on c.membership_id = m.id
where check_in_date = '20180109'
and m.id like '48Z%'

-- membership_id    check_in_date   check_in_time   check_out_time  id       person_id    name            membership_start_date   membership_status
-- 48Z7A            20180109        1600            1730            48Z7A    28819        Joe Germuska    20160305                gold
-- 48Z55            20180109        1530            1700            48Z55    67318        Jeremy Bowers   20160101                gold

select p.*, l.*
from drivers_license l
join person p on p.license_id = l.id
where plate_number like '%H42W%'

-- id       name              license_id    address_number    address_street_name        ssn          id        age   height    eye_color    hair_color   gender    plate_number    car_make    car_model
-- 51739    Tushar Chandra    664760        312                Phi St                    137882671    664760    21    71        black        black        male      4H42WR          Nissan      Altima
-- 67318    Jeremy Bowers     423327        530                Washington Pl, Apt 3A     871539279    423327    30    70        brown        brown        male      0H42W2          Chevrolet   Spark LS
-- 78193    Maxine Whitely    183779        110                Fisk Rd                   137882671    183779    21    65        blue         blonde       female    H42W0X          Toyota      Prius

select name, transcript
from interview
join person on person.id = interview.person_id
where person_id = 67318

-- name             transcript
-- Jeremy Bowers    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.

select p.name, p.ssn, p.id, l.*
from drivers_license l
join person p on p.license_id = l.id
where car_make = 'Tesla'
and car_model = 'Model S'
and hair_color = 'red'
and gender = 'female'

-- name                ssn          id        age   height    eye_color    hair_color     gender    plate_number    car_make    car_model
-- Red Korb            961388910    918773    48    65        black        red            female    917UU3          Tesla       Model S
-- Regina George       337169072    291182    65    66        blue         red            female    08CM64          Tesla       Model S
-- Miranda Priestly    987756388    202298    68    66        green        red            female    500123          Tesla       Model S

select p.id, p.name, i.annual_income
from income i
join person p on i.ssn = p.ssn
where i.ssn in (961388910, 337169072, 987756388)

-- id       name                annual_income
-- 78881    Red Korb            278000
-- 99716    Miranda Priestly    310000

select p.name, e.event_name, e.date
from facebook_event_checkin e
join person p on e.person_id = p.id
where p.id in (78881, 99716)

-- name                event_name              date
-- Miranda Priestly    SQL Symphony Concert    20171206
-- Miranda Priestly    SQL Symphony Concert    20171212
-- Miranda Priestly    SQL Symphony Concert    20171229

INSERT INTO solution VALUES (1, 'Miranda Priestly');

        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!