log.sql (Fiftyville) Code / Dokumentation

-- Keep a log of any SQL queries you execute as you solve the mystery.
-- took place: July 28, 2025, Humphrey Street
-- I see crime_scene_reports as a data of table, lets see if it coontains some useful infomration:
SELECT description FROM crime_scene_reports WHERE day = 28 AND month = 7 AND year = 2025 AND street = "Humphrey Street";
-- addtionaly I have now the exact timestamp (10:15 am) and "bakery"
-- alongsside the others tables is also the security logs of bakery acsessable so, lets see both license plate and the activity:
SELECT activity, license_plate FROM bakery_security_logs WHERE day = 28 AND month = 7 AND year = 2025 AND hour = 10 AND minute = 15;
--delivers nothing so im assuming it happend around that time, considerable is the nearst, im going to go with quering for
--the minute additionaly and asking for the hour only:
SELECT activity, license_plate FROM bakery_security_logs WHERE day = 28 AND month = 7 AND year = 2025 AND hour = 10 AND minute = 10;
--seeing those two suspicious enterance both at hour 10 one is minute 8 and one is minute 14.
-- Minute 8 persons' license_plate: R3G7486
-- Minute 14 persons' license_plate: 13FNH73
-- lets see who those people, with their phone numbers if they suspciosly called around that time:
SELECT name, phone_number FROM people WHERE license_plate =
(SELECT license_plate FROM bakery_security_logs WHERE hour = 10 AND minute = 8)
OR license_plate = (SELECT license_plate FROM bakery_security_logs WHERE hour = 10 AND minute = 14) --getting also the other guys name and phone number
-- seeing two people: Lauren (phone number: (707) 555-7535) and Sophia (phone number: (027) 555-1068) doesnt seem to suffice since they entred not leave

-- let me see if the interviews table might give some useful information:
SELECT transcript FROM interviews
WHERE day = 28 AND month = 7 AND year = 2025;
-- got some useful mentioning about bakery theft, one is advicing to check for secruity footage from the bakery parking lot
-- and another saw the theft withdrawing some money
-- in particular a very useful hint was given by someone, who realised that the theft called someone for less than a minute;
-- and he heard them saying they were taking the earliest flight out of fiftville tomorrow, the call was for the other person to purchase a fligt out

-- summary:
--1. within ten minutes of the theft the thief drove away (from the bakery parking lot) (meaning if theft occoured 10:15 -> 10:25)
--2. withdrew money before the act (Leggett Street)
--3. called someone for less than a minute to book a flight out of fiftyville

SELECT transaction_type, amount, account_number FROM atm_transactions
WHERE day = 28 AND month = 7 AND year = 2025 AND atm_location = "Leggett Street";
-- Seeing 9 results 1 of which deposited the others withdrew on that specfic date,
-- 2 of which withdrew a high amount:
-- 60 (amount) 76054385 (accout_number)
-- 80( amount) 16153065 (accout_number)
-- no evidenz that those guys did anything so let me try to connect the Interviews' information, meaning
-- connecting the dots:

SELECT p.name, pc.caller, pc.receiver, pc.duration FROM people as p --getting their names
 JOIN phone_calls as pc ON p.phone_number = pc.caller  -- connecting phone calls
 JOIN bakery_security_logs as b ON p.license_plate = b.license_plate   -- connecting plates
 JOIN bank_accounts as bacc ON p.id = bacc.person_id                    -- connecting bank accounts
 WHERE pc.duration < 60  AND pc.day = 28 AND pc.month = 7 AND pc.year = 2025 -- phone calls on the date of theft, that didnt lasted less than a minute
 AND b.minute < 25 AND b.hour = 10 AND b.activity = "exit" -- people who left before 10:25
 AND bacc.account_number IN (SELECT account_number FROM atm_transactions
 WHERE transaction_type = "withdraw" AND atm_location = "Leggett Street" AND day = 28 AND month = 7 AND year = 2025); -- people that did withdraw at Leggett Street on that specfic date

-- results in 2 people Bruce and Diana:
-- looking into diana she did fly twice on 29, and 30, which look like its a bussiness trip or the like
SELECT fl.day, p.name, fl.destination_airport_id FROM passengers as pg
JOIN people as p ON p.passport_number = pg.passport_number
JOIN flights as fl ON pg.flight_id = fl.id
WHERE fl.origin_airport_id = (SELECT id FROM airports WHERE city = "Fiftyville") AND fl.month = 7 AND fl.year = 2025 AND p.name = "Diana";

-- lets see Bruce
SELECT fl.day, p.name, fl.destination_airport_id FROM passengers as pg
JOIN people as p ON p.passport_number = pg.passport_number
JOIN flights as fl ON pg.flight_id = fl.id
WHERE fl.origin_airport_id = (SELECT id FROM airports WHERE city = "Fiftyville") AND fl.month = 7 AND fl.year = 2025 AND p.name = "Bruce";

-- he flew to the id airport 4
SELECT city FROM airports WHERE id = 4;

-- New York City and he gone there didnt take another flight
-- lets see who his ACCOMPLICE is whic was (375) 555-8161
SELECT name FROM people WHERE phone_number = "(375) 555-8161";
-- its Robin