mystery.knightlab.com - SQL practice

practice.sql

SELECT description
FROM crime_scene_report
WHERE city = 'SQL City' AND type = 'murder' 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 *
FROM interview AS I
WHERE i.person_id IN (
  SELECT p.id
  FROM person as p
  JOIN (
	SELECT address_street_name, max(address_number) as last_house
	  FROM person
	  WHERE address_street_name in ('Northwestern Dr','Franklin Ave')
	  GROUP BY address_street_name
  ) as s
	  ON s.address_street_name = p.address_street_name
  WHERE p.address_street_name = s.address_street_name
	  AND p.address_number = s.last_house
)
+ 
person_id	transcript
24737	
14887	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".
---