Hi, my name is
Timothy Williston
SQL Example
A Mystery in Fiftyville
The CS50 Duck has been stolen! The town of Fiftyville has called upon you to solve the mystery of the stolen duck. Authorities believe that the thief stole the duck and then, shortly afterwards, took a flight out of town with the help of an accomplice. Your goal is to identify:
- Who the thief is,
- What city the thief escaped to, and
- Who the thief’s accomplice is who helped them escape
All you know is that the theft took place on July 28, 2021 and that it took place on Humphrey Street.
A few years ago I took an online course called CS50x. It is an online course from Harvard University on computer science. It is a notoriously difficult course, but it was a great learning experience. Below is my solution to the SQL mystery:
-- Keep a log of any SQL queries you execute as you solve the mystery.
-- Find crime scene description:
SELECT description
FROM crime_scene_reports
WHERE month = 7
AND day = 28
AND street = 'Humphrey Street';
-- Theft of the CS50 duck took place at 10:15am at the Humphrey Street bakery.
-- Interviews were conducted today with three witnesses who were present at the time –
-- each of their interview transcripts mentions the bakery.
-- Littering took place at 16:36. No known witnesses.
-- 10:15: time of theft
-- bakery: place of theft
-- Find the three interviews:
SELECT name, transcript
FROM interviews
WHERE month = 7
AND day = 28;
-- Ruth said check footage within 10 min of theft
-- Eugene recognized the thief who used the ATM on Leggett st earlier that morning
-- Raymond said as thief left, they made a phone call (less than a min)... they wanted to take the
-- earliest flight out of Fiftyville the next day. Thief asked for them to purchase ticket.
-- Check ATM logs:
SELECT * FROM atm_transactions
WHERE month = 7
AND day = 28
AND atm_location = 'Leggett Street'
AND transaction_type = 'withdraw';
-- Found 8 account numbers... check names from bank_accounts:
SELECT id, name FROM people
WHERE id IN
(SELECT person_id FROM bank_accounts
WHERE account_number IN
(SELECT account_number FROM atm_transactions
WHERE month = 7
AND day = 28
AND atm_location = 'Leggett Street'
AND transaction_type = 'withdraw'));
-- Suspects:
-- id | name
-- 395717 | Kenny |
-- 396669 | Iman |
-- 438727 | Benista |
-- 449774 | Taylor |
-- 458378 | Brooke |
-- 467400 | Luca |
-- 514354 | Diana |
-- 686048 | Bruce |
-- Check phone records for date, less than 60 seconds:
SELECT caller FROM phone_calls
WHERE month = 7
AND day = 28
AND duration < 60;
/*
| (130) 555-0289 | Sofia
| (499) 555-9472 | Kelsey
| (367) 555-5533 | Bruce
| (499) 555-9472 | Kelsey
| (286) 555-6063 |
| (770) 555-1861 |
| (031) 555-6622 |
| (826) 555-1652 |
| (338) 555-6650 |*/
-- 9 records... get caller names:
SELECT name from people
WHERE phone_number IN
(SELECT caller FROM phone_calls
WHERE month = 7
AND day = 28
AND duration < 60);
/*
| Kenny | (826) 555-1652 |
| Sofia | (130) 555-0289 |-
| Benista | (338) 555-6650 |
| Taylor | (286) 555-6063 |
| Diana | (770) 555-1861 |
| Kelsey | (499) 555-9472 |: made two phone calls
| Bruce | (367) 555-5533 |-
| Carina | (031) 555-6622 |*/
Check INTERSECTS in two names tables:
SELECT name FROM people
WHERE id IN
(SELECT person_id FROM bank_accounts
WHERE account_number IN
(SELECT account_number FROM atm_transactions
WHERE month = 7
AND day = 28
AND atm_location = 'Leggett Street'
AND transaction_type = 'withdraw'))
INTERSECT
SELECT name from people
WHERE phone_number IN
(SELECT caller FROM phone_calls
WHERE month = 7
AND day = 28
AND duration < 60);
-- Down to 5 suspects:
/*
| Benista |
| Bruce |
| Diana |
| Kenny |
| Taylor |*/
-- Let's create a table with suspects:
CREATE TABLE suspects
AS
SELECT * FROM people
WHERE id IN
(SELECT person_id FROM bank_accounts
WHERE account_number IN
(SELECT account_number FROM atm_transactions
WHERE month = 7
AND day = 28
AND atm_location = 'Leggett Street'
AND transaction_type = 'withdraw'))
INTERSECT
SELECT * from people
WHERE phone_number IN
(SELECT caller FROM phone_calls
WHERE month = 7
AND day = 28
AND duration < 60);
-- Now, who did suspects call:
SELECT id, name FROM people
WHERE phone_number IN
(SELECT receiver FROM phone_calls
WHERE month = 7
AND day = 28
AND duration < 60);
-- Let's create an accomplices table too:
CREATE TABLE accomplices
AS
SELECT * FROM people
WHERE phone_number IN
(SELECT receiver FROM phone_calls
WHERE month = 7
AND day = 28
AND duration < 60);
-- Check flight info for 7/29 out of Fiftyville and grab earliest flight:
SELECT * from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1;
-- Get passport_numbers from passengers for that flight:
SELECT * from passengers
WHERE flight_id IN
(SELECT id from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1);
-- Cross reference the passport numbers with suspects table:
SELECT passport_number from passengers
WHERE flight_id IN
(SELECT id from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1)
INTERSECT
SELECT passport_number FROM suspects;
-- Check security logs for date and time between 10:15 and 10:25:
SELECT license_plate FROM bakery_security_logs
WHERE day = 28
AND month = 7
AND hour = 10
AND minute BETWEEN 15 AND 25;
-- Find suspects info who were on the flight:
SELECT * from suspects
WHERE passport_number IN
(SELECT passport_number from passengers
WHERE flight_id IN
(SELECT id from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1)
INTERSECT
SELECT passport_number FROM suspects);
-- Cross reference with previous security log:
SELECT license_plate from suspects
WHERE passport_number IN
(SELECT passport_number from passengers
WHERE flight_id IN
(SELECT id from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1)
INTERSECT
SELECT passport_number FROM suspects)
INTERSECT
SELECT license_plate FROM bakery_security_logs
WHERE day = 28
AND month = 7
AND hour = 10
AND minute BETWEEN 15 AND 25;
-- FOUND ONE!!!
-- Get info for suspect:
SELECT name from suspects
WHERE license_plate =
(SELECT license_plate from suspects
WHERE passport_number IN
(SELECT passport_number from passengers
WHERE flight_id IN
(SELECT id from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1)
INTERSECT
SELECT passport_number FROM suspects)
INTERSECT
SELECT license_plate FROM bakery_security_logs
WHERE day = 28
AND month = 7
AND hour = 10
AND minute BETWEEN 15 AND 25);
-- Bruce committed the crime!!!
-- Get city the thief escaped to:
SELECT city from airports
WHERE id =
(SELECT destination_airport_id FROM flights
WHERE id =
(SELECT id from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1));
-- Who did Bruce call that morning?
-- Get Phone Num of suspect
SELECT phone_number from suspects
WHERE license_plate =
(SELECT license_plate from suspects
WHERE passport_number IN
(SELECT passport_number from passengers
WHERE flight_id IN
(SELECT id from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1)
INTERSECT
SELECT passport_number FROM suspects)
INTERSECT
SELECT license_plate FROM bakery_security_logs
WHERE day = 28
AND month = 7
AND hour = 10
AND minute BETWEEN 15 AND 25);
-- Get accomplice:
SELECT name from people
WHERE phone_number =
(SELECT receiver from phone_calls
WHERE month = 7
AND day = 28
AND duration < 60
AND caller =
(SELECT phone_number from suspects
WHERE license_plate =
(SELECT license_plate from suspects
WHERE passport_number IN
(SELECT passport_number from passengers
WHERE flight_id IN
(SELECT id from flights
WHERE month = 7
AND day = 29
AND origin_airport_id =
(SELECT id from airports
WHERE city = 'Fiftyville')
ORDER BY hour, minute
LIMIT 1)
INTERSECT
SELECT passport_number FROM suspects)
INTERSECT
SELECT license_plate FROM bakery_security_logs
WHERE day = 28
AND month = 7
AND hour = 10
AND minute BETWEEN 15 AND 25)));
And the solution is:
The THIEF is: Bruce
The city the thief ESCAPED TO: New York City
The ACCOMPLICE is: Robin