---
title: "Lab 7: Murder in SQL City"
subtitle: "`join` + `filter` + `stringr` + `ludridate`"
author: "Your name here!"
format: gfm
editor: source
execute: 
  echo: true
  warning: false
---

# Set-up

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

<!-- See instructions for words of advice on completing the assignment IN THE INSTRUCTIONS! -->


# 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!


## 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/statistical-computing-r/spring-2026/main/labs/instructions/lab-7-setup.R")
```


## Solve the Crime

### Step 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?!


**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 Step

**Follow the evidence to the person responsible for the murder, building a report as you go.**
There are accomplices, some knowingly and some unknowingly, but there is only one mastermind.


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 separate **labeled** code-chunks for each complete pipe/output  
+ 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-note
## And the final suspect is...

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