#install.packages(c("odbc","DBI","RSQLite"))
#install.packages("tidyverse")
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.2.0     ✔ readr     2.2.0
## ✔ forcats   1.0.1     ✔ stringr   1.6.0
## ✔ ggplot2   4.0.2     ✔ tibble    3.3.1
## ✔ lubridate 1.9.5     ✔ tidyr     1.3.2
## ✔ purrr     1.2.1     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(odbc)
library(DBI)
library(RSQLite)
host_city_locations <- read_csv("~/Downloads/host_city_locations.csv")
## Rows: 52 Columns: 5
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (2): Season, City
## dbl (3): Year, lon, lat
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
athlete_events <- read_csv("~/Downloads/athlete_events.csv") 
## Rows: 271116 Columns: 15
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (10): Name, Sex, Team, NOC, Games, Season, City, Sport, Event, Medal
## dbl  (5): ID, Age, Height, Weight, Year
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
olympics <- dbConnect(SQLite(), ":memory:")
copy_to(olympics, host_city_locations)
copy_to(olympics, athlete_events)
SELECT COUNT(*) FROM athlete_events
1 records
COUNT(*)
271116 
SELECT COUNT(DISTINCT ID)
  FROM athlete_events
  WHERE City = "London";
1 records
COUNT(DISTINCT ID)
16941 
SELECT City, lat
  FROM host_city_locations
  ORDER BY lat DESC
  LIMIT 1
1 records
City lat
Lillehammer 61.11527 
cities <- dbGetQuery(
  olympics, '
  SELECT DISTINCT(City)
  FROM host_city_locations
  '
)

write_csv(cities, "host_cities_MichaelDesir.csv")
dbDisconnect(olympics)