Skip to content

Instantly share code, notes, and snippets.

@AndreiChenchik
Last active December 20, 2019 09:03
Show Gist options
  • Save AndreiChenchik/31d681f7524a15e8043da6f575a9746e to your computer and use it in GitHub Desktop.
Save AndreiChenchik/31d681f7524a15e8043da6f575a9746e to your computer and use it in GitHub Desktop.
mystery.knightlab.com - SQL practice
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".
---
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment