Lab 7: Murder in SQL City

join + filter + stringr + ludridate

For this lab, you will be joining and filtering related datasets to solve a murder mystery!

1 Part 1: GitHub Workflow

1.1 Step 1: Accessing the Lab

Here is the Quarto document. Data will be imported from an external site.

lab-7-student.qmd

1.2 Step 2: Making a Small Change

At the top of the document (in the YAML) there is an author line that says "Your name here!". Change this to be your name and save your file either by clicking on the blue floppy disk or with a shortcut (command / control + s).

1.3 Step 3: Pushing Your Lab to GitHub

Now for our last step, we need to commit the files to our repo.

1.4 Step 4: Let’s get started!

2 Part 2: Some Words of Advice

Set chunk options carefully.
Make sure you don’t print out more output than you need.
Make sure you don’t assign more objects than necessary—avoid “object junk” in your environment.
Make your code readable and nicely formatted.
Think through your desired result before writing any code.

3 Part 3: Finding the Killer

Northwestern University’s Knight Lab wanted to help sharpen users’ database skills, so they created a murder mystery. Can you solve this crime in SQL City??

The relational data you will be working with contains tables with different pieces of information pertinent to the crime - people, social media check-ins, driver’s licenses, crime scene reports, police interviews, and more!

This is an entity-relationship diagram that showcases multiple interconnected tables. The central table is labeled 'person' with attributes including id, name, license_id, address_number, address_street_name, and ssn. It connects to various other tables. One connection leads to 'drivers_license,' which contains id, age, height, eye_color, hair_color, gender, plate_number, car_make, and car_model. Another connection leads to the 'income' table, which includes ssn and annual_income. Additionally, the 'person' table is linked to 'get_fit_now_member,' which has id, person_id, name, membership_start_date, and membership_status attributes. The 'get_fit_now_member' table connects to 'get_fit_now_check_in,' which lists membership_id, check_in_date, check_in_time, and check_out_time. Another linked table is 'facebook_event_checkin,' containing person_id, event_id, event_name, and date. The diagram also includes an 'interview' table with person_id and transcript, a 'crime_scene_report' table with date, type, description, and city, and a small table labeled 'solution' with user and value attributes. The diagram visualizes relationships and interactions across these entities. — Database schema

Tip

You may find the pull() function handy for displaying each clue as you go along.

3.1 Access the Data

This code chunk will read in all of the tables of data for you. Don’t modify or remove this! Take some time to look at each file type so that

library(tidyverse)

# If purrr is not detected, install the package
if (!"purrr" %in% installed.packages()) install.packages("purrr")

source("https://raw.githubusercontent.com/jcanner/stat_210_2025_website/main/labs/instructions/lab-7-setup.R")

3.2 Solve the Crime

3.2.1 Crime Scene Report

Detective Wickham reaches out to you…

A crime has taken place and I need your help! There was a murder in SQL City sometime on January 15, 2018. Could you retrieve the crime scene report from the police department’s database and follow the clues to find the person responsible for the murder?!

Step 1: Find the police report in crime_scene_report. Then used the information in the report to move on to the next data source based on the information learned.

# Code for looking at the relevant crime scene report.

Next Steps: Follow the evidence to the person responsible for the murder, building a report as you go.

Solve the murder mystery, showing all of your work in this document. Your document and code must be well organized, easy to follow, and reproducible.

Use headers and written descriptions to indicate what you are doing.
Use dplyr verbs and join functions rather than just looking through the tables manually. Functions from stringr and lubridate will also be useful.
Use good code formatting practices.
Comment your code.
Cite any external sources you use to solve the mystery.

Caution

Make sure you check for interviews with any suspects!

And the final suspect is…

put the name of the person responsible for the murder here.

--- title: "Lab 7: Murder in SQL City" subtitle: "`join` + `filter` + `stringr` + `ludridate`" format: html: code-tools: true toc: true number-sections: true editor: source execute: error: true echo: true message: false warning: false --- For this lab, you will be joining and filtering related datasets to solve a murder mystery! # Part 1: GitHub Workflow ## Step 1: Accessing the Lab Here is the Quarto document. Data will be imported from an external site. + [lab-7-student.qmd](../student/lab-7-student.qmd) ## Step 2: Making a Small Change At the top of the document (in the YAML) there is an `author` line that says `"Your name here!"`. Change this to be your name and save your file either by clicking on the blue floppy disk or with a shortcut (command / control + s). ## Step 3: Pushing Your Lab to GitHub Now for our last step, we need to [commit the files to our repo](https://happygitwithr.com/existing-github-first#stage-and-commit). ## Step 4: Let's get started! # Part 2: Some Words of Advice - Set chunk options carefully. - Make sure you don't print out more output than you need. - Make sure you don't assign more objects than necessary---avoid "object junk" in your environment. - Make your code readable and nicely formatted. - Think through your desired result **before** writing any code. # Part 3: Finding the Killer Northwestern University's Knight Lab wanted to help sharpen users' database skills, so they created a murder mystery. Can you solve this crime in SQL City?? The relational data you will be working with contains tables with different pieces of information pertinent to the crime - people, social media check-ins, driver's licenses, crime scene reports, police interviews, and more! ![Database schema](https://mystery.knightlab.com/schema.png){fig-alt="This is an entity-relationship diagram that showcases multiple interconnected tables. The central table is labeled 'person' with attributes including id, name, license_id, address_number, address_street_name, and ssn. It connects to various other tables. One connection leads to 'drivers_license,' which contains id, age, height, eye_color, hair_color, gender, plate_number, car_make, and car_model. Another connection leads to the 'income' table, which includes ssn and annual_income. Additionally, the 'person' table is linked to 'get_fit_now_member,' which has id, person_id, name, membership_start_date, and membership_status attributes. The 'get_fit_now_member' table connects to 'get_fit_now_check_in,' which lists membership_id, check_in_date, check_in_time, and check_out_time. Another linked table is 'facebook_event_checkin,' containing person_id, event_id, event_name, and date. The diagram also includes an 'interview' table with person_id and transcript, a 'crime_scene_report' table with date, type, description, and city, and a small table labeled 'solution' with user and value attributes. The diagram visualizes relationships and interactions across these entities."} ::: callout-tip You may find the `pull()` function handy for displaying each clue as you go along. ::: ## Access the Data This code chunk will read in **all** of the tables of data for you. Don't modify or remove this! Take some time to look at each file type so that ```{r} #| message: false #| warning: false #| label: read-in-data library(tidyverse) # If purrr is not detected, install the package if (!"purrr" %in% installed.packages()) install.packages("purrr") source("https://raw.githubusercontent.com/jcanner/stat_210_2025_website/main/labs/instructions/lab-7-setup.R") ``` ## Solve the Crime ### Crime Scene Report Detective Wickham reaches out to you... > A crime has taken place and I need your help! There was a murder in SQL City sometime on January 15, 2018. Could you retrieve the crime scene report from the police department’s database and follow the clues to find the person responsible for the murder?! **Step 1: Find the police report in `crime_scene_report`. Then used the information in the report to move on to the next data source based on the information learned.** ```{r} # Code for looking at the relevant crime scene report. ``` **Next Steps: Follow the evidence to the person responsible for the murder, building a report as you go.** Solve the murder mystery, showing **all of your work in this document**. Your document and code must be well organized, easy to follow, and reproducible. + Use headers and written descriptions to indicate what you are doing. + Use `dplyr` verbs and `join` functions rather than just looking through the tables manually. Functions from `stringr` and `lubridate` will also be useful. + Use good code formatting practices. + Comment your code. + Cite any external sources you use to solve the mystery. ::: callout-caution Make sure you check for interviews with any suspects! ::: ::: callout-note ## And the final suspect is... *put the name of the person responsible for the murder here.* :::