Anil Goyal anilyayavar

anilyayavar / sql7.r

Created April 12, 2024 08:28

	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)
	) %>%

anilyayavar / sql6.r

Created April 12, 2024 08:03

	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 / sql5.r

Created April 12, 2024 07:59

	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 / sql4.r

Created April 12, 2024 07:50

	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 / sql3.r

Created April 12, 2024 07:44

	# 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 / sql2.r

Created April 12, 2024 07:41

	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 / sql1.r

Created April 12, 2024 07:34

	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 / dummy.r

Created April 11, 2024 07:26

	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 / gist:8d976f21402eb3d9aeb002b6fbc11ad9

Created April 11, 2024 07:20

LETTERS