mystery.knightlab.com solution

mystery.knightlab.com.sql

-- https://mystery.knightlab.com/

-- get all tables in database
select name, sql
from sqlite_master t
where t.type = 'table';
--
CREATE TABLE crime_scene_report ( date integer, type text, description text, city text )
CREATE TABLE drivers_license ( id integer PRIMARY KEY, age integer, height integer, eye_color text, hair_color text, gender text, plate_number text, car_make text, car_model text )
CREATE TABLE person ( id integer PRIMARY KEY, name text, license_id integer, address_number integer, address_street_name text, ssn integer, FOREIGN KEY (license_id) REFERENCES drivers_license(id) )
CREATE TABLE facebook_event_checkin ( person_id integer, event_id integer, event_name text, date integer, FOREIGN KEY (person_id) REFERENCES person(id) )
CREATE TABLE interview ( person_id integer, transcript text, FOREIGN KEY (person_id) REFERENCES person(id) )
CREATE TABLE get_fit_now_member ( id text PRIMARY KEY, person_id integer, name text, membership_start_date integer, membership_status text, FOREIGN KEY (person_id) REFERENCES person(id) )
CREATE TABLE get_fit_now_check_in ( membership_id text, check_in_date integer, check_in_time integer, check_out_time integer, FOREIGN KEY (membership_id) REFERENCES get_fit_now_member(id) )
CREATE TABLE income ( ssn integer PRIMARY KEY, annual_income integer )
CREATE TABLE solution ( user integer, value text )
--

select description
from crime_scene_report
where type = 'murder'
  and city = 'SQL City'
  and date = 20180115
-- 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 i.transcript
from person    p
join interview i on p.id = i.person_id
where address_street_name like '%Northwestern Dr%'
order by 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".

select i.transcript, c.check_in_time, c.check_out_time
from person               p
join interview            i on p.id = i.person_id
join get_fit_now_member   m on p.id = m.person_id
join get_fit_now_check_in c on m.id = c.membership_id
where p.name like '%Annabel%'
  and p.address_street_name like '%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.
-- 1600 | 1700

select p.id, p.name, i.transcript
from get_fit_now_member   m
join get_fit_now_check_in c on m.id         = c.membership_id
join person               p on m.person_id  = p.id
join drivers_license      d on p.license_id = d.id
join interview            i on p.id         = i.person_id
where m.membership_status = 'gold'
  and m.id like '48Z%'
  and d.plate_number like '%H42W%'
  and c.check_in_date = 20180109
  and c.check_out_time between 1600 and 1700
-- 67318 | Jeremy Bowers | 1530 | 1700
-- 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.id, p.name, i.annual_income
from person                 p
join drivers_license        d on p.license_id = d.id
join facebook_event_checkin e on p.id         = e.person_id
join income                 i on p.ssn        = i.ssn
where d.gender     = 'female'
  and d.height between 65 and 67
  and d.hair_color = 'red'
  and d.car_make   = 'Tesla'
  and d.car_model  = 'Model S'
  and e.event_name = 'SQL Symphony Concert'
  and e.date between 20171201 and 20171231
group by e.event_id
having count(e.event_id) = 3