Three Data-Driven Murder Mysteries For You To Solve With Various Tools

I’m in “travel mode”, and have just made a quite harrowing trip from ME to DE (stopover to hang with #2.1) thanks to climate change — many major passageways in CT and NY were more “stream” than “road”. I am quite glad I had the Wrangler as my ride.

As such, this will (hopefully) be a fun Bonus Drop, but will also lack significant individual expository.

Let me know if you solve any or all of them!

Each of the resources presents a murder mystery for you to solve.

One via the command line (and only using generally available “coreutils”).

One that riffs from the command line one, but is focused on SQL (I rather liked this one).

And, one that riffs from the SQL one, but has you do proper data analysis in R.

Command-line Murder Mystery;

There’s been a murder in Terminal City, and TCPD needs your help!

To figure out whodunit, you need access to a command line.

Once you’re ready, clone this repo, put on your best raincoat (assuming you are a Columbo fan), cat instructions (ref: below) and find the killer!

To figure out whodunit, go to the 'mystery' 
subdirectory and start working from there.

You'll want to start by collecting all the clues 
at the crime scene (the 'crimescene' file).

The officers on the scene are pretty meticulous, 
so they've written down EVERYTHING in their 
officer reports.

Fortunately the sergeant went through and marked 
the real clues with the word "CLUE" in all caps.

If you get stuck, open one of the hint files 
(from the CL, type 'cat hint1', 'cat hint2', etc.).

To check your answer or find out the solution, open 
the file 'solution' (from the CL, type 'cat solution').

To get started on how to use the command line, 
open cheatsheet.md or cheatsheet.pdf (from the 
command line, you can type 'nano cheatsheet.md').

Don't use a text editor to view any files except 
these instructions, the cheatsheet, and hints.

SQL Murder Mystery

There’s been a Murder in SQL City!

The SQL Murder Mystery is designed to be both a self-directed lesson to learn SQL concepts and commands and a fun game for experienced SQL users to solve an intriguing crime.

There are complete instructions at the above link, so please head there to get started in SQLite mode.

For those that would like to use this as an opportunity to learn DuckDB (it has support for a more modern/advanced SQL dialect), you can!

After cloning, just do:

$ duckdb
D INSTALL sqlite;
D LOAD sqlite;
D CALL sqlite_attach('sql-murder-mystery.db');
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ 0 rows  │
└─────────┘
D SHOW TABLES;
┌────────────────────────┐
│          name          │
│        varchar         │
├────────────────────────┤
│ crime_scene_report     │
│ drivers_license        │
│ facebook_event_checkin │
│ get_fit_now_check_in   │
│ get_fit_now_member     │
│ income                 │
│ interview              │
│ person                 │
│ solution               │
└────────────────────────┘

and use some of the fancier SQL to solve the case!

{recluse}

A crime has taken place and the detective needs your help!

The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City. All the clues to this mystery are buried in a “huge” database, and you need to use your R skills to navigate through this vast network of information. Your first step to solving the mystery is to retrieve the corresponding crime scene report from the police department’s database.

The reclues R package mimics the aforementioned SQL Murder Mystery.

While the SQL game is set up to use SQL to solve the mystery, {reclues} makes that data available directly in R to solve the mystery using R.

Might I also suggest using the {tidyverse} with the SQLite database vs. the internal datasets (just for practice, ofc).

FIN

As Minsc says, “Cities always teem with evil and decay. Let’s give them a good shakedata analysis and see what falls out!” ☮