Built with datascienceportfol.io

INVESTIGATIVE ANALYSIS PROJECT

Project photo

Project links

Skills

About this project

The Crime Scene

 

A murder occurred on January 15, 2018, in SQL City.

 

Initial Investigation

1. Retrieved crime scene report: SELECT * FROM crime_scene_report

2. Filtered report by date, city, and crime type: SELECT * FROM crime_scene_report WHERE date = 2018-01-15 AND city = 'SQL City' AND crime_type = 'murder'

Morty Schapiro, a local resident, witnessed the crime. He recounted hearing a gunshot and seeing a man flee with a "Get Fit Now Gym" bag. The membership number on the bag started with "48Z." This crucial detail led investigators to Joe Germuska and Jeremy Bowers, two gym members with matching membership numbers.

 

Witness Identification

1. Retrieved person data: SELECT * FROM person

2. Identified first witness (Morty Schapiro): SELECT * FROM person WHERE address_street_name = 'Northwestern Dr' ORDER BY address_number DESC

3. Identified second witness (Annabel Miller): SELECT * FROM person WHERE name LIKE 'Annabel%' AND address_street_name = 'Franklin Ave'

Further inquiry revealed Annabel Miller, another witness, who recognized the killer from her gym visit on January 9. Her testimony solidified Jeremy Bowers as the prime suspect.

 

Witness Interviews

1. Retrieved interview transcripts: SELECT * FROM interview WHERE person_id IN (14887, 16371)

2. Morty's testimony: Heard gunshot, saw man run out with "Get Fit Now Gym" bag (membership number started with "48Z").

3. Annabel's testimony: Recognized killer from gym on January 9, 2018.

A search of car registrations linked Jeremy to a vehicle with plate number H42W. The evidence mounted against him.

 

Suspect Identification

1. Created table for suspects: CREATE TABLE Suspect AS (SELECT * FROM get_fit_now_member WHERE id LIKE '48Z%' AND membership_status = 'gold')

2. Identified two suspects: Joe Germuska (ID 48Z7A) and Jeremy Bowers (ID 48Z55)

3. Checked gym check-ins: SELECT * FROM get_fit_now_check_in WHERE check_in_date = 2018-01-09 AND membership_id IN ('48Z55', '48Z7A')

4. Identified Jeremy Bowers as owner of car plate number like H42W

 

Final Investigation

1. Retrieved interview transcript of murderer (Jeremy Bowers): SELECT * FROM interview WHERE person_id = 67318

2. Identified real villain: Woman, 5'5"-5'7", red hair, drives Tesla Model S, attended SQL Symphony Concert 3 times in December 2017.

3. Created table for new suspects: CREATE TABLE suspect AS (SELECT * FROM drivers_license WHERE height BETWEEN 65 AND 67 AND hair_color = 'red' AND gender = 'female' AND car_make = 'Tesla' AND car_model = 'Model S')

4. Joined persons and suspect tables: SELECT s.id, s.age, s.height, as person_id, p.name, p.address_street_name, p.ssn FROM suspect AS s RIGHT JOIN person AS p ON s.id = p.license_id

5. Identified three suspects: Miranda Priestly, Regina George, and Red Korb.

6. Checked Facebook event check-ins: SELECT * FROM facebook_event_checkin WHERE event_name = 'SQL Symphony Concert' AND DATE BETWEEN 20171201 AND 20171231 AND person_id IN (99716, 90700, 78881)

7. Confirmed killer: Miranda Priestly (person_id 99716).

In a stunning twist, Jeremy's interview revealed he was hired by a mysterious woman. She was described as 5'5"-5'7" with red hair, driving a Tesla Model S. She had attended the SQL Symphony Concert three times in December 2017. The investigation narrowed down to three suspects: Miranda Priestly, Regina George, and Red Korb. Facebook event check-ins confirmed Miranda's presence at the concert, sealing her fate as the mastermind behind the murder.

 

This investigation demonstrated advanced SQL skills, including:

- Filtering and sorting data

- Joining tables

- Using LIKE and ORDER BY clauses

- Creating temporary tables

  • Analyzing interview transcripts and evidence