library(tidyverse)
library(nycflights13)

flights
airports
planes
weather
airlines

### Unpünktlichste Flüge
queryResult = arrange(flights,-(arr_delay))
select(queryResult, flight, month, day, carrier, origin, dest, arr_delay)

### Am meisten aufgeholte Verspätung
flights.mit.delta.delay = mutate(flights, delta.delay = arr_delay - dep_delay)
select(flights.mit.delta.delay, dep_delay, arr_delay, delta.delay)
queryResult = arrange(flights.mit.delta.delay, delta.delay)
select(queryResult, flight, month, day, carrier, origin, dest, delta.delay)

### Zusammengebaute Query
bayAreaFlights = filter(flights, dest %in% c("SFO", "OAK"))
bayAreaFlights.january = filter(bayAreaFlights, month==1)
filter(bayAreaFlights.january, arr_delay >= 60, dep_time <=600)


### Destinationen mit größter Verspätung
flights %>%
  filter(!is.na(arr_delay)) %>%
  group_by(origin) %>%
  summarise(avg.delay = mean(arr_delay),
            anzahl = n(),
            avg.flighttime = mean(air_time ),
            perecentage.delay = avg.delay / avg.flighttime) %>%
  filter(anzahl > 500) %>%
  arrange(-perecentage.delay)

###Flüge jeden tag
flights %>%
  group_by(carrier, flight) %>%
  summarise(anzahl = n(),
            dest = first(dest)) %>%
  filter(anzahl == 365)

###Pünktlichste Carrier

flights %>%
  filter(!is.na(arr_delay)) %>%
  group_by(carrier) %>%
  summarise(avg.delay = mean(arr_delay),
            anzahl = n()) %>%
  arrange(avg.delay)