Aprende R y tidyverse mientras resuelves un
crimen
Adaptado por Rosana Ferrero
Resuelve el misterio
El “SQL Murder Mystery” es un ejercicio interactivo diseñado para poner a prueba y mejorar
tus habilidades de análisis de datos mediante la resolución de un crimen ficticio. Fue creado
por Joon Park y Cathy He mientras eran becarios del Knight Lab, y existe un repositorio en
Github que los usuarios pueden explorar para obtener más información.
En este desafío, asumirás el rol de un detective que debe analizar una base de datos para
identificar al culpable de un asesinato ocurrido en SQL City. Aunque el ejercicio original está
basado en SQL, lo he adaptado para su desarrollo en R (según el código de Naidoo 2019 y Goyal
2024), aprovechando las capacidades de manipulación y análisis de datos de este lenguaje. Al
1
utilizar R, podrás aplicar técnicas de manipulación de datos, filtrado y análisis para resolver
el misterio, lo que te permitirá profundizar en el uso de R para tareas de análisis de datos y
consultas a bases de datos.
A lo largo de este ejercicio, explorarás diversas tablas relacionadas con personas, vehículos,
licencias de conducir y eventos, entre otras. Mediante consultas y análisis, podrás identificar
patrones, relaciones y pistas que te conducirán a la resolución del caso.
Este desafío no solo te permitirá practicar tus habilidades en R, sino que también te brindará
una comprensión más profunda de cómo interactuar con bases de datos y realizar análisis
complejos en un entorno de programación.
Ha ocurrido un crimen y el detective necesita tu ayuda.
Inicialmente, se nos dan tres pistas:
• El crimen fue un asesinato,
• que ocurrió en algún momento el 15 de enero de 2018, y
• que tuvo lugar en SQL City.
El resto de las pistas de este misterio están enterradas en una enorme base de datos, y necesitas
usar tus habilidades de R para navegar a través de esta vasta red de información. ¡Buena
suerte!
Los conjuntos de datos disponibles se encuentran en el paquete reclues que debes instalar y
abrir, para obtener la lista de bases de datos.
# [Link]("DBI")
# [Link]("devtools")
# devtools::install_github("sciencificity/reclues")
library(reclues) #contiene los datos del crimen
library(tidyverse) #para manipular datos
library(gt) #para crear una tabla
# Obtener la lista de datasets del paquete reclues
datasets <- data(package = "reclues")$results[, c("Item", "Title")] |>
as_tibble()
gt(datasets)
Los conjuntos de datos disponibles son:
2
Item Title
crime_scene_report Crime scene report
drivers_license Drivers license
facebook_event_checkin Facebook checkin event
get_fit_now_check_in Fit Now checkin details
get_fit_now_member Fit Now member details
income Income
interview Interview
person Person
solution Solution
• ‘crime_scene_report‘: contiene información sobre el crimen, incluyendo la fecha, la ciu-
dad y la hora del crimen.
• ‘persons_of_interest‘: contiene información sobre las personas de interés, incluyendo su
nombre, edad y ocupación.
• ‘phone_records‘: contiene registros telefónicos de las personas de interés, incluyendo el
número de teléfono y la fecha de la llamada.
• ‘financial_records‘: contiene registros financieros de las personas de interés, incluyendo
el nombre del banco y el saldo de la cuenta.
• ‘crime_scene_photos‘: contiene fotos de la escena del crimen, incluyendo la ubicación y
la fecha de la foto.
Paso 1: Recupera el informe de la escena del crimen.
Comienza recuperando el informe de la escena del crimen correspondiente de la base de datos
del departamento de policía. Dado que recordamos que el crimen fue un asesinato ocurrido
el 15 de enero de 2018 en SQL City, comenzamos buscando en la base de datos el informe
correspondiente.
crime_scene_report %>%
filter(date == '20180115', # Filtrar la fecha del crimen,
type == "murder", # Tipo de crimen, y
city == "SQL City") %>% # Ciudad del crimen
pull(description) %>% # Obtener la descripción
cat()
3
Security footage shows that there were 2 witnesses. The first witness lives
↪ at the last house on "Northwestern Dr". The second witness, named
↪ Annabel, lives somewhere on "Franklin Ave".
Hallazgo: El informe indica que hubo dos testigos:
• El primer testigo vive en la última casa de “Northwestern Dr”.
• El segundo testigo, llamado Annabel, reside en algún lugar de “Franklin Ave”.
Paso 2: Identifica a los testigos.
Para encontrar al primer testigo, buscamos a la persona que vive en el número más alto de
“Northwestern Dr”. Luego, buscamos a una persona llamada Annabel que viva en “Franklin
Ave”, y unimos la información.
witnesses <- person %>%
filter(str_detect(address_street_name, "Northwestern Dr") & # Filtrar el
↪ primer testigo
address_number == max(address_number),
.by = address_street_name) %>%
# Unir con la información de los testigos
bind_rows(
person %>%
filter(str_detect(name, "Annabel") & # Filtrar el
↪ segundo testigo
str_detect(address_street_name, "Franklin Ave"))
) %>%
(\(x) { print(x); x })() %>% # Mostrar los pasos intermedios
pull(id) # Nos quedamos con la columna id
# A tibble: 2 x 6
id name license_id address_number address_street_name ssn
<int> <chr> <int> <int> <chr> <int>
1 14887 Morty Schapiro 118009 4919 Northwestern Dr 111564949
2 16371 Annabel Miller 490173 103 Franklin Ave 318771143
Hallazgo: El informe nos da la información de los dos testigos:
• Identificamos a Morty Schapiro, con ID 14887, como el primer testigo.
• Encontramos a Annabel Miller, con ID 16371, como la segunda testigo.
4
Paso 3: Obtén las declaraciones de los testigos
Consultamos las entrevistas de ambos testigos para obtener más detalles sobre el crimen.
interview %>%
filter(person_id %in% witnesses) %>% # Filtrar los testigos
pull(transcript) %>% # Obtener su testimonio
cat(sep = "\n")
I heard a gunshot and then saw a man run out. He had a "Get Fit Now Gym" bag.
↪ The membership number on the bag started with "48Z". Only gold members
↪ have those bags. The man got into a car with a plate that included
↪ "H42W".
I saw the murder happen, and I recognized the killer from my gym when I was
↪ working out last week on January the 9th.
Hallazgo: Las declaraciones revelan que el sospechoso es un hombre que llevaba una bolsa
del “Get Fit Now Gym” con un número de membresía que comenzaba con “48Z”, indicando
que es un miembro gold. Además, conducía un coche con una matrícula que incluía “H42W”
y fue visto en el gimnasio el 9 de enero de 2018.
• El sospechoso tiene una membresía dorada y
• El número de miembro comienza con “48Z”.
• Fue registrado en el gimnasio el 9 de enero de 2018.
• Subió al auto con matrícula que incluía “H43W”.
Paso 4: Identifica a los sospechosos
Buscamos en los registros del gimnasio a miembros gold y con IDs que comiencen con “48Z”.
get_fit_now_member %>%
filter(membership_status == "gold", # Filtrar las características del
↪ sospechoso
str_detect(id, "^48Z"))
# A tibble: 2 x 5
id person_id name membership_start_date membership_status
<chr> <int> <chr> <int> <chr>
1 48Z7A 28819 Joe Germuska 20160305 gold
2 48Z55 67318 Jeremy Bowers 20160101 gold
5
Hallazgo: Encontramos dos miembros que cumplen con los criterios: IDs 48Z7A y 48Z55.
Profundicemos un poco más, buscamos que hayan registrado entrada el 9 de enero de 2018.
get_fit_now_check_in %>%
filter(check_in_date == "20180109") %>% # Filtrar la fecha del crimen
right_join( # Unir con la información de los
↪ sospechosos
get_fit_now_member %>%
filter(membership_status == "gold", # Filtrar las características del
↪ sospechoso
str_detect(id, "^48Z")),
by = join_by(membership_id == id)
)
# A tibble: 2 x 8
membership_id check_in_date check_in_time check_out_time person_id name
<chr> <int> <int> <int> <int> <chr>
1 48Z7A 20180109 1600 1730 28819 Joe Germus~
2 48Z55 20180109 1530 1700 67318 Jeremy Bow~
# i 2 more variables: membership_start_date <int>, membership_status <chr>
Pero de nuevo dos sospechosos, los mismos que el anterior.
Así que tenemos que utilizar información adicional proporcionada por el primer testigo: El
hombre subió al auto con matrícula que incluía “H43W”.
Para ello, tenemos que comprobar la tabla drivers license. Pero hay un problema: tenemos
que unir la tabla utilizando la tabla person con la clave license_id .
get_fit_now_check_in %>%
filter(check_in_date == "20180109") %>% # Filtrar la fecha del crimen
right_join( # Unir con la información de los
↪ sospechosos
get_fit_now_member %>%
filter(membership_status == "gold", # Filtrar las características del
↪ sospechoso
str_detect(id, "^48Z")),
by = join_by(membership_id == id)
) %>%
left_join( # Unir con la información de las
↪ personas
person,
6
by = join_by(person_id == id)
) %>%
inner_join( # Unir con la información de las
↪ matrículas
drivers_license %>%
filter(str_detect(plate_number, "H42W")),
by = join_by(license_id == id)
)
# A tibble: 1 x 21
membership_id check_in_date check_in_time check_out_time person_id name.x
<chr> <int> <int> <int> <int> <chr>
1 48Z55 20180109 1530 1700 67318 Jeremy Bow~
# i 15 more variables: membership_start_date <int>, membership_status <chr>,
# name.y <chr>, license_id <int>, address_number <int>,
# address_street_name <chr>, ssn <int>, age <int>, height <int>,
# eye_color <chr>, hair_color <chr>, gender <chr>, plate_number <chr>,
# car_make <chr>, car_model <chr>
Hallazgo: Jeremy Bowers, con ID 67318, posee una matrícula que incluye “H42W”, coincidi-
endo con la descripción. Tenemos al culpable.
Conclusión: Jeremy Bowers es identificado como el asesino.
¡Felicitaciones, encontraste al asesino! Pero espera, hay más… Si crees que estás preparado para
un nuevo desafío, intenta consultar la transcripción de la entrevista del asesino para encontrar
al verdadero villano detrás de este crimen.
Paso 5: Descubre al autor intelectual
Con el resultado anterior, podemos ver que 67318 es el id del asesino. Busquemos la transcrip-
ción y leámosla.
interview %>%
filter( person_id == 67318 ) %>% # Filtrar al sospechoso
pull( transcript ) %>% # Obtener su testimonio
cat( )
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.
7
Hallazgo: Jeremy fue contratado por una mujer adinerada, pelirroja, de aproximadamente 65
a 67 pulgadas de altura, que conduce un Tesla Model S y asistió al “SQL Symphony Concert”
tres veces en diciembre de 2017.
Determinamos cuál de las sospechosas asistió al “SQL Symphony Concert” tres veces en di-
ciembre de 2017. Esto requerirá la lectura de una tabla más facebook_event_checkin.
facebook_event_checkin %>%
mutate( date = lubridate :: ymd ( date ) ) %>% # Convertir la fecha a un
↪ formato adecuado
filter( str_detect ( event_name , "SQL Symphony" ) ,
↪ # Filtrar el evento
between ( date , ymd ( "20171201" ) , ymd ( "20171231" ) ) ) %>%
↪ # Filtrar la fecha
filter( n ( ) >= 3 , # Filtrar los eventos con
↪ al menos 3 asistentes
.by = person_id ) %>%
select( person_id ) %>% # Seleccionar la columna
↪ person_id
distinct( ) %>% # Eliminar duplicados
# Hay dos personas que encajan en la descripción
left_join( person , # Unir con la información
↪ de las personas
by = join_by ( person_id == id ) ) %>%
# Solo la persona tiene licencia de conducir
inner_join( drivers_license , # Unir con la información
↪ de las matrículas
by = join_by ( license_id == id ) ) %>%
pull(name)
[1] "Miranda Priestly"
Hallazgo: Miranda Priestly, con ID 99716, asistió al concierto tres veces en diciembre de
2017.
Conclusión: Basándonos en la evidencia, identificamos a Miranda Priestly como la autora
intelectual del asesinato.
¡Felicitaciones! ¡Has encontrado al cerebro detrás del asesinato! Todos en SQL City te aclaman
como el mejor detective de SQL de todos los tiempos. ¡ Es hora de descorchar el champán !
8
Referencias
[Link]
[Link]
[Link]
[Link]
[Link]
