SQL Murder Mystery

[Thor]

There’s been a Murder in SQL City!

SQL Murder Mystery

Use SQL queries to solve the murder mystery. Suitable for beginners or experienced SQL sleuths.

mystery.knightlab.com

 

[Moosedrool]

I have a life thanks.

 

[Kosmik]

I have a life thanks.

lol, oh comeon.

 

[poffle]

Lol that was pretty fun, very easy but fun Got both the murderers1st time

 

[prod]

Was fun!

Spoiler: 1st solution

select * from person a
inner join get_fit_now_member b on a.id = b.person_id
inner join get_fit_now_check_in c on c.membership_id = b.id
left join drivers_license d on d.id = a.license_id
where b.id like '48Z%' and b.membership_status ='gold'
and plate_number like '%H42W%'

Spoiler: 2nd solution

select a.id,a.name,count(f.event_id) as event_count from person a
left join get_fit_now_member b on a.id = b.person_id
left join get_fit_now_check_in c on c.membership_id = b.id
left join drivers_license d on d.id = a.license_id
left join interview e on e.person_id = a.id
left join facebook_event_checkin f on f.person_id = a.id
where d.car_make = 'Tesla'
and d.car_model='Model S'
and d.height >= 65 and d.height <=67
and hair_color ='red'
and f.event_name = 'SQL Symphony Concert'
group by a.id,a.name
having count(f.event_id) >= 3

 

[The_Ogre]

I got the wrong person for the first riddle! Still not sure what I did wrong, but I started from scratch and got it the second time aroudn.

 

[SkippyRamirez]

Hehe, that was fun

 

[Hamster]

Nom

Spoiler

select * from crime_scene_report
where date = '20180115'
and city = 'SQL City'
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".

with witnesses as (
  select * from (
    select id
    from person
    where address_street_name = 'Northwestern Dr'
    order by address_number desc
    limit 1
  )
  union
  select id
  from person
  where address_street_name = 'Franklin Ave'
  and name like 'Annabel%'
)
select i.transcript
from interview i
inner join witnesses w on w.id = i.person_id;

-- 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".
-- 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 p.name
from get_fit_now_check_in c
inner join get_fit_now_member m on m.id = c.membership_id
inner join person p on p.id = m.person_id
inner join drivers_license l on l.id = p.license_id
           and l.plate_number like '%H42W%'
where c.check_in_date = '20180109';

 

[schuits]

Dammit, kept hitting F5 to run my queries and then had to redo them..lol

 

[Kosmik]

Was nice.

 

[Genisys]

Nom

Spoiler

select * from crime_scene_report
where date = '20180115'
and city = 'SQL City'
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".

with witnesses as (
  select * from (
    select id
    from person
    where address_street_name = 'Northwestern Dr'
    order by address_number desc
    limit 1
  )
  union
  select id
  from person
  where address_street_name = 'Franklin Ave'
  and name like 'Annabel%'
)
select i.transcript
from interview i
inner join witnesses w on w.id = i.person_id;

-- 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".
-- 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 p.name
from get_fit_now_check_in c
inner join get_fit_now_member m on m.id = c.membership_id
inner join person p on p.id = m.person_id
inner join drivers_license l on l.id = p.license_id
           and l.plate_number like '%H42W%'
where c.check_in_date = '20180109';

Oh, that is bound to piss someone off for being "too complicated to understand"