Sharing my process to track down the suspect in the SQL Murder Mystery, the first in a series of challenges I intend to participate in over the coming weeks. Thanks Will Sutton for compiling these awhile back.

Details on the data project can be found here: https://github.com/wjsutton/the_summer_of_sql

Here’s the schema diagram for the SQL Murder Mystery:

Checking the crime_scene_report as our starting point, we can see one murder that happened on January 15, 2018 and it provides our first clues as to what to check next:  There are 2 witnesses who, presumably, have more information.  We’ll need to find them next.

Find the first witness – the ‘last house’ on Northwestern Dr is presumably the highest house number, hence a sort by descending – looks like it’s Morty Schapiro:

Find the second witness – turns out there’s only one Annabel on Franklin Ave:

Get the ids for Morty and Annabel and find what they said in the interview table to get some more clues for what to check next:

Using these clues from the two witnesses, if we look for gold membership status and membership ids that contain the string “48Z” in the get_fit_now_member table, that gives us two men named Joe Germuska and Jeremy Bowers:

Both men were there on January 9th (as per the second witness’ tip), so we still need to narrow it down between these two guys.

We were able to see their person id (FK) in the get_fit_now_member table, which can be joined to the person table on the id (PK), so that we can see whatever info is available about these two guys in the person table… which might get us to the next clue.

Let’s get the id from the person table when we know it’s either Joe or Jeremy:

…and then do a left join to get info from drivers_license.  We can see that this doesn’t give us anything (the nulls), but this is because I’ve used the PK from both in error (I too-quickly grabbed ‘id’ from both but that wasn’t quite right!).

Let’s check the license plate tip from the first witness (they must have really good eyes to see and remember those characters!) – and when I do this, I realize that the id from drivers_license joins to person, but we must use the FK from person and the PK from drivers_license to join correctly:

Now that I’ve joined person with drivers_license properly, we can see that the killer is Jeremy Bowers (and not Joe G.)!

But now there is a follow-up bonus question – it looks like Jeremy was hired by someone else to do it.

First let’s find out what Jeremy said in his interview:

“I was hired by a woman with a lot of money. I don’t know her name but I know she’s around 5’5″ (65″) or 5’7″ (67″). She has red hair and she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.” This is our main clue to find out who hired Jeremy.

Find the events from the facebook_event_checkin table that contain the text ‘symphony’ – I chose that as it seemed like could be a word that is likely to stand out / be unique.

Then narrow it down by the dates mentioned by Jeremy during his interview – she attended this SQL Symphony Concert three times during December 2017.

We can summarize this list (group by and then filter via having) to see the person_id of the people who attended the event 3 or more times (I was curious if anyone went more than 3 times – they must really like that SQL Symphony) – we can then take these person ids and join on the info from the person table to see who they are:

So it looks like there are two people who were there 3 times in December 2017. Let’s join everything from the person table to see who they are:

It looks like Bryan Pardo and Miranda Priestly were both at the SQL Symphony Concert 3 times during December 2017.  Considering that Jeremy said it was a female, I’d be checking if Miranda drives a Tesla….

She sure does!

In summary:  It was Miranda Priestly who hired Jeremy Bowers to commit the murder in SQL City on January 15, 2018:

Looking forward to the next challenge!