CLUE #1

There is a murder in SQL City on 2018-01-15.

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

CLUE #2

Witness 1 lives in the last house on Northwestern Dr. Witness 2 is named Annabel and lives somehwere on Franklin Ave.

select p.id, 
       p.name, 
       p.address_number, 
       p.address_street_name, 
       i.transcript
from person p

inner join (select person_id, transcript 
            from interview) i on p.id = i.person_id

where p.address_street_name = 'Northwestern Dr'
or (p.address_street_name = 'Franklin Ave' and name like 'Annabel%')
order by address_number desc

CLUE #3

We now know the killer is a man, spotted at the local gym on 2018-01-09 with membership ID number starting 48Z and car license plate includes H42W.


select gfm.id, 
       gfm.person_id, 
       gfm.name, 
       gfm.membership_status, 
       p.license_id, 
       dl.plate_number
from get_fit_now_member gfm

inner join (select membership_id, check_in_date
            from get_fit_now_check_in
            where check_in_date = '20180109'
            and membership_id like '48Z%') gfc on gfm.id = gfc.membership_id

inner join (select id, name, license_id
            from person) p on gfm.person_id = p.id
            and gfm.name = p.name

inner join (select id, plate_number
            from drivers_license
            where gender = 'male'
            and plate_number like '%H42W%') dl on p.license_id = dl.id

where gfm.membership_status = 'gold'

CLUE #4

Lets find our next clue to see what Jeremy Bowers had to say:

select * from interview where person_id = '67318'

CLUE #5

So the mastermind behind this murder is a woman, height of 65 to 67 inches, has red hair, drives a Tesla Model S and attended the SQL Symphony Concert at least 3x in December 2017.

select dl.id, 
       p.name
from drivers_license dl

inner join (select id, name, license_id 
         from person) p on dl.id = p.license_id

inner join (select person_id, count(*) as total
          from facebook_event_checkin
          where event_name = 'SQL Symphony Concert'
          and date between '20171201' and '20171231'
          group by 1
          having count(*) >= 3) fb on p.id = fb.person_id

where dl.height in (65, 66, 67) 
and dl.gender = 'female' 
and dl.car_make = 'Tesla'