---
title: "Flights"
author: "Tomasz Lech"
date: "5/21/2019"
output: html_document
---
```{r, echo=FALSE}
library(RODBC)
library(formattable)
polacz <- odbcConnect("FlightProject", uid = "dbad_s444483", pwd = "dXKeqA6K25")
```

**Dany raport przedstawia zapytania do bazy amerykańskich lini lotniczych związanych z opóźnieniami lotów**

**zad.1 Jakie było średnie opóźnienie przylotu?**
```{r}
sqlQuery(polacz, "select avg(arr_delay_new)
from Flight_delays")
```

**zad.2 Jakie było maksymalne opóźnienie przylotu?**
```{r}
sqlQuery(polacz, "select max(arr_delay)
from Flight_delays")
```

**zad.3 Który lot miał największe opóźnienie przylotu?**
```{r}
sqlQuery(polacz, "select carrier,fl_num,origin_city_name,dest_city_name, fl_date
from Flight_delays
where arr_delay_new = (select max(arr_delay_new) from Flight_delays)")
```

**zad.4 Które dni tygodnia są najgorsze do podróżowania?**
```{r}
sqlQuery(polacz, "select W.weekday_name, AVG(F.arr_delay_new) as 'delay'
from Flight_delays F join Weekdays W on W.weekday_id = F.day_of_week
group by W.weekday_name")
```

**zad.5 Które linie lotnicze latające z San Francisco (SFO) mają najmniejsze opóźnienia przylotu?**
```{r}
sqlQuery(polacz, "select distinct airline_name, avg_delay
from (select    A.airline_name, F.airline_id, avg(F.arr_delay_new) as 'avg_delay'
from Flight_delays F join Airlines A on F.airline_id = A.airline_id
group by  A.airline_name, F.airline_id) as T join Flight_delays F on T.airline_id = F.airline_id
where F.origin like'SFO'
order by avg_delay desc")

```

**zad.6 Jaka część linii lotniczych ma regularne opóźnienia, tj. jej lot ma średnio co najmniej 10 min. opóźnienia?**
```{r}
x <- sqlQuery(polacz, "select count(distinct unique_carrier)
from Flight_delays
where unique_carrier
in(select unique_carrier
from Flight_delays
group by unique_carrier
having avg( arr_delay_new) > 10)")

y <- sqlQuery(polacz, "select COUNT(distinct unique_carrier)
from Flight_delays")
z <- (x/y)
print(z)

```

**zad.7 Jak opóźnienia wylotów wpływają na opóźnienia przylotów?**
```{r}
x <- sqlQuery(polacz,"select dep_delay_new from Flight_delays")
y <- sqlQuery(polacz,"select arr_delay_new from Flight_delays")
wspPear <- cor( x,y , use= "complete.obs",method= "pearson")
print(wspPear)
```

**zad..8 Która linia lotnicza miała największy wzrost (w wartościach bezwzględnych) średniego opóźnienia przylotów w ostatnim tygodniu miesiąca, tj. między 1-23 a 24-31 lipca?**
```{r}
x <- sqlQuery(polacz, "
with srednia_1
as
(select airline_name,
        avg_delay_1
from (select A.airline_name, AVG(arr_delay_new) as 'avg_delay_1'
from (select airline_id, arr_delay_new from Flight_delays
where day_of_month  between 1 and 23 and month like'7') as T join Airlines A on A.airline_id = T.airline_id
group by A.airline_name) as K
),

srednia_2
as
(select airline_name, avg_delay_2
from (select A.airline_name, AVG(arr_delay_new) as 'avg_delay_2'
from (select   airline_id, arr_delay_new
from Flight_delays
where day_of_month between 24 and 31 and month like'7') as P join Airlines A on A.airline_id = P.airline_id
group by A.airline_name) as R
)

select top 1 srednia_1.airline_name,
(srednia_2.avg_delay_2 - srednia_1.avg_delay_1) as 'increase'
from srednia_1 join srednia_2 on srednia_1.airline_name = srednia_2.airline_name
where srednia_2.avg_delay_2 > srednia_1.avg_delay_1
order by increase desc;
")
```

**zad.9 Które linie lotnicze latają zarówno na trasie SFO → PDX (Portland), jak i SFO → EUG (Eugene)?**
```{r}
sqlQuery(polacz, "select  distinct airline_name
from (select  distinct A.airline_name, F.airline_id
from Flight_delays F join Airlines A on F.airline_id = A.airline_id
where F.origin like'SFO' and F.dest like'PDX') as T join Flight_delays F1 on F1.airline_id = T.airline_id
where F1.origin like'SFO' and F1.dest like'EUG';")
```

**zad.10 Jak najszybciej dostać się z Chicago do Stanfordu, zakładając wylot po 14:00 czasu lokalnego?**
```{r}
sqlQuery(polacz, "select origin,dest,
avg(arr_delay_new) as 'avg_delay'
from Flight_delays
where crs_dep_time between 1401 and 2359
group by origin, dest
having origin in ('MDW', 'ORD') and dest in ('SFO', 'SJC', 'OAK')
order by avg_delay desc")
```