---
title: "Lab 9.1 - dplyr &amp; SQL"
author: "Author Goes Here"
date: "Semester Goes Here"
output:
  html_document:
    code_download: true
---

```{r include = FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

```{r message = FALSE, warning = FALSE}
library(tidyverse)
library(DBI)
```

The goal of this exercise is to learn about how to work with databases and particularly the SQL query language.

## Loading the Data

We'll be working with the flights data from our textbook. Here's the code to connect to the database:

```{r}
db <- DBI::dbConnect(
  RMariaDB::MariaDB(), 
  dbname = "airlines", 
  host = "mdsr.cdc7tgkkqd0n.us-east-1.rds.amazonaws.com", 
  user = "mdsr_public", 
  password = "ImhsmflMDSwR",
  )
```

And we'll get references to two specific tables we'll be working with.

```{r}
flights <- tbl(db, "flights")
carriers <- tbl(db, "carriers")
```

Each of those *behaves* like a `tibble` (data frame), but it's actually a reference to remote data.

```{r}
flights %>% head()
```

The tool that makes this possible is called [`dbplyr`](https://dbplyr.tidyverse.org/). The objects it gives us to work with are *lazy*: they don't actually fetch the data until we absolutely need it. They're lazy *queries* in that to get the data we're going to ask the database for data in a language called "SQL". (Structured Query Language).

We can ask for the query that would be executed using `show_query`.

```{r}
flights %>% show_query()
```


```{r}
flights %>% 
  filter(year == 2016) %>% 
  show_query()
```

## Filtering Records

🚧
Based only on looking at the above, what SQL expression corresponds to `filter`?

## Summarizing Data

Because queries are lazy, even finding out how many rows is expensive, since we'd have to run that query. So, somewhat surprisingly:

```{r}
nrow(flights)
```

But we can use summary functions!

```{r}
flights %>% 
  filter(year == 2016, month == 11, distance > 1000) %>% 
  summarize(
    n = n(),
    mean_dep_delay = mean(dep_delay, na.rm = TRUE)
  ) %>% show_query()
```

```{r}
flights %>% 
  filter(year == 2016, month == 11, distance > 2000) %>% 
  summarize(n = n(), mean_dep_delay = mean(dep_delay, na.rm = TRUE)) 
```

🚧
Based on the above, how does SQL express `n` and `mean`?

🚧
What years of data are stored in the `flights` table? Do this by summarizing the `min` and `max` of the `year` column. (Before actually executing this query, get the instructor to verify your query so that you don't overload the shared server.)

```{r}
flights %>% 
  summarize(first_year = min(year, na.rm = TRUE), last_year = max(year, na.rm = TRUE)) %>% 
  collect()
```


🚧
How many flights arrived in JFK (`dest` is `JFK`) in November of 2016?

🚧
In that period (November of 2016 for JFK), how many flights arrived for each `carrier`? Sort the result by decreasing number of flights.

## Grouping Recordsh

🚧
Look at the SQL query for the previous exercise. How does SQL express `group_by`? How does SQL express `arrange`?

🚧
In that same period (November of 2016 for JFK), what fraction of flights from each carrier were on time? Consider a flight to be on time if its `arr_delay` is no more than 15 (minutes).

The easiest way to do this is the "sum-as-count" pattern: `sum(arr_delay <= 15)`.

## Joining Tables

🚧
Modify the query that lists flight counts into JFK grouped by carrier to join in the carrier code with the full name of the carrier from the `carriers`.
