Solution for: https://mystery.knightlab.com

sql_mystery.sql

-- walkthrough of: https://mystery.knightlab.com
--
/* 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. */
SELECT
  *
FROM
  crime_scene_report
WHERE
  date = 20180115
  AND type = "murder"
  AND lower(city) LIKE "sql city";
  /* 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 wit as (
    SELECT
      *
    FROM
      person
    WHERE
      address_street_name = "Northwestern Dr" -- "Franklin Ave"
    ORDER BY
      address_number DESC
    LIMIT
      1
  )
SELECT
  transcript
FROM
  interview
  JOIN wit ON interview.person_id = wit.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". */
  --
  /* The other witness does not have a transcript */
  --
  --
  --
SELECT
  *
FROM
  get_fit_now_member
WHERE
  ID LIKE "48Z%"
  AND membership_status = "gold"
  /* id	person_id	name	membership_start_date	membership_status */
  /* 48Z7A	28819	Joe Germuska	20160305	gold */
  /* 48Z55	67318	Jeremy Bowers	20160101	gold */
SELECT
  *
FROM
  drivers_license
WHERE
  plate_number LIKE "%H42W%"
  and gender = "male"
  /* id	age	height	eye_color	hair_color	gender	plate_number	car_make	car_model */
  /* 423327	30	70	brown	brown	male	0H42W2	Chevrolet	Spark LS */
  /* 664760	21	71	black	black	male	4H42WR	Nissan	Altima */
  /* 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". */
  --
  --
  --
  WITH suspect_drivers AS (
    SELECT
      *
    FROM
      drivers_license
    WHERE
      plate_number LIKE "%H42W%"
      AND gender = "male"
  )
SELECT
  *
FROM
  person
  JOIN suspect_drivers ON person.license_id = suspect_drivers.id
  /* 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 */
  --
  --
  -- Jeremy Bowers is the only one that matches both last 2 queries
  --
  --
  /* Congrats, you found the murderer! But wait,
   * there's more... If you think you're up for a
  * challenge, try querying the interview transcript
  * of the murderer to find the real villian behind
  * this crime. If you feel especially confident in
  * your SQL skills, try to complete this final step
  * with no more than 2 queries. Use this same
  * INSERT statement to check your answer. */
  --
  --
  --
SELECT
  transcript
FROM
  interview
WHERE
  person_id = (
    SELECT
      id
    FROM
      person
    WHERE
      name = "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
  *
FROM
  drivers_license
WHERE
  height BETWEEN 65
  AND 67
  AND LOWER(car_make) = "tesla"
  AND gender = "female";
  /* id	age	height	eye_color	hair_color	gender	plate_number	car_make	car_model */
  /* 202298	68	66	green	red	female	500123	Tesla	Model S */
  /* 291182	65	66	blue	red	female	08CM64	Tesla	Model S */
  /* 918773	48	65	black	red	female	917UU3	Tesla	Model S */
  --
  --
  --
  WITH suspects AS (
    SELECT
      *
    FROM
      drivers_license
    WHERE
      height BETWEEN 65
      AND 67
      AND LOWER(car_make) = "tesla"
      AND gender = "female"
  )
SELECT
  DISTINCT name
FROM
  person
  JOIN suspects ON person.license_id = suspects.id
  JOIN facebook_event_checkin ON person_id = person.id --
  --
  --END