anilyayavar

LETTERS

anilyayavar

library(readxl)
library(janitor)

df <- read_xlsx("PQ/PQ_Challenge_169.xlsx", range = cell_cols(LETTERS[1]))
df %>% 
  mutate(Codes = map_chr(String, ~ str_extract_all(.x, pattern = "(?<=\\b)[A-Z]+\\d+[A-Z0-9]+(?=\\b)") %>% 
                           unlist() %>% 
                           str_c(collapse = ", ")))

anilyayavar

library(tidyverse)

csr <- read_csv("https://raw.githubusercontent.com/anilyayavar/sqlmurdermystery/main/data/crime_scene_report.csv")

csr %>%
  filter(date == '20180115',
         type == "murder",
         city == "SQL City") %>%
  pull(description) %>%
  cat()

anilyayavar

person <- read_csv("https://raw.githubusercontent.com/anilyayavar/sqlmurdermystery/main/data/person.csv")

# First Witness
person %>% 
  filter(str_detect(address_street_name, "Northwestern Dr") &
           address_number == max(address_number), 
         .by = address_street_name) %>% 
  # Join with Second Witness
  bind_rows(
    person %>% 

anilyayavar

# First Witness
witnesses <- person %>% 
  filter(str_detect(address_street_name, "Northwestern Dr") &
           address_number == max(address_number), 
         .by = address_street_name) %>% 
  # Join with Second Witness
  bind_rows(
    person %>% 
      filter(str_detect(name, "Annabel") &
               str_detect(address_street_name, "Franklin Ave"))

anilyayavar

interviews <- read_csv("https://raw.githubusercontent.com/anilyayavar/sqlmurdermystery/main/data/interview.csv")

interviews %>% 
  filter(person_id %in% witnesses) %>% 
  pull(transcript) %>% 
  cat(sep = "\n")

anilyayavar

gfn_member <- read_csv("https://raw.githubusercontent.com/anilyayavar/sqlmurdermystery/main/data/get_fit_now_member.csv")

gfn_member %>% 
  filter(membership_status == "gold",
         str_detect(id, "^48Z"))

anilyayavar

gfn_checkins <- read_csv("https://raw.githubusercontent.com/anilyayavar/sqlmurdermystery/main/data/get_fit_now_check_in.csv")

gfn_checkins %>% 
  filter(check_in_date == "20180109") %>% 
  right_join(
    gfn_member %>% 
      filter(membership_status == "gold",
             str_detect(id, "^48Z")),
    by = join_by(membership_id == id)
  )

anilyayavar

dl <- read_csv("https://raw.githubusercontent.com/anilyayavar/sqlmurdermystery/main/data/drivers_license.csv")

gfn_checkins %>% 
  filter(check_in_date == "20180109") %>% 
  right_join(
    gfn_member %>% 
      filter(membership_status == "gold",
             str_detect(id, "^48Z")),
    by = join_by(membership_id == id)
  ) %>%