A game SQL by setting with the story as detective to find out murder mystery using the information from SQL database; source: https://mystery.knightlab.com

murder-mystery.sql

/*
A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City​. Start by retrieving the corresponding crime scene report from the police department’s database.
*/

-- check the available tables
SELECT name 
  FROM sqlite_master
 where type = 'table';

-- crime_scene_report
-- drivers_license;
-- person;
-- facebook_event_checkin;
-- interview;
-- get_fit_now_member;
-- get_fit_now_check_in;
-- income;
-- solution;

-- check the create table definition
--CREATE TABLE crime_scene_report ( date integer, type text, description text, city text )
SELECT sql 
  FROM sqlite_master
 where name = 'crime_scene_report'

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

/*
date        type    description                                 city
20180115    murder  Security footage shows that there were      SQL city
                    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 address_street_number = 'Northwestern Dr' or
      address_street_number = 'Franklin Ave';

-- first witness
select *
from person
where address_street_number = 'Northwestern Dr'
order by address_number desc
limit 1;

-- witnesses' testimony
with
first_witness as (
    select id, name from person
    where address_street_name = 'Northwestern Dr'
    order by address_number desc
    limit 1),
other_witnesses as (
    select id, name from person
    where address_street_name = 'Franklin Ave' and
    name like '%Annabel%'
)
select person_id, transcript, coalesce(fw.name, ow.name, '') as name
from interview
join first_witness as fw on person_id = fw.id
left join other_witnesses as ow on person_id = ow.id
where fw.name is not null or ow.name is not null;
/*
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 *
from get_fit_now_member
where id like '48Z%' and membership_status = 'gold';

select *
from drivers_license
where plate_number like '%H42W%' and gender = 'male';

-- interview transcript based on witness' testimony
with
dvlicense as (
    select person.id, name
    from drivers_license as dv join person on dv.id = person.license_id
    where gender = 'male' and plate_number like '%H42W%'),
fitnow as (
    select person_id, name from get_fit_now_member
    where id like '48Z%' and membership_status = 'gold')
select interview.person_id, dvlicense.name, transcript from interview
join dvlicense on dvlicense.id = interview.person_id,
fitnow where fitnow.name = dvlicense.name;
/*
person_id   transcript
67318       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.

name: Jeremy Bowers
*/

-- find out from Jeremy's testimony
select * from drivers_license where
hair_color = 'red' and
height in (65, 66, 67) and
car_make = 'Tesla' and
car_model = 'Model S' and
gender = 'female';

/* getting the name from Jeremy's testimony
1. A woman, gender = 'female'
2. A lot of money, annual income > 100_000
3. Around 65" or 67", height in (65, 66, 67)
4. red hair, hair_color = 'red'
5. drive tesla, car_make = 'Tesla'
6. the car model Model S, car_model = 'Model S'
7. event count is 3, 
*/

with
divp as (
    select name, person.id, ssn
    from drivers_license as dv join person on dv.id = person.license_id
    where hair_color = 'red' and
    height in (65, 66, 67) and
    car_make = 'Tesla' and
    car_model = 'Model S' and
    gender = 'female'),
target_income as (select annual_income from income, divp where
    income.ssn = divp.ssn),
event as (
    select * from divp
    join facebook_event_checkin as fb on person_id = id
    where event_name = 'SQL Symphony Concert' and
    cast(fb.date as text) like '201712%'
),
event_count as (select count(*) from event as `count`),
female_name as (select * from event where 3 in event_count)
/*
select name, interview.person_id, transcript
from interview join female_name
on female_name.person_id = interview.person_id,
target_income where target_income.annual_income > 100000;
*/
select distinct(name) from female_name, target_income
where target_income.annual_income > 100000;