# exercícios back-end 01/08
## sessão 01
### exercício 01
``` sql
select purpose, avg(end_date - start_date)
from uber_drives ud
group by purpose;
```
### exercício 02
``` sql
select purpose, avg(miles)
from uber_drives ud
group by purpose;
```
### exercício 03
``` sql
select category, count(*)
from uber_drives ud
group by category
order by count desc;
```
### exercício 04
``` sql
select distinct start
from uber_drives ud;
```
### exercício 05
``` sql
select distinct stop
from uber_drives ud;
```
### exercício 06
``` sql
select max(miles), min(miles)
from uber_drives ud;
```
## sessão 02
### exercício 7.A
``` sql
select distinct purpose
from uber_drives ud
```
### exercício 7.B
``` sql
select distinct purpose, count(*)
from uber_drives ud
group by purpose
```
### exercício 7.C
``` sql
select distinct purpose, count(*)
from uber_drives ud
group by purpose
order by count desc
```
### exercício 7.D
``` sql
select * from (
select distinct purpose, count(*)
from uber_drives ud
group by purpose
order by count desc
) as dados
where dados.count between 100 and 170;
```
### exercício 7.E
``` sql
select *
from uber_drives ud
where purpose in (select purpose from (select * from (
select distinct purpose, count(*)
from uber_drives ud
group by purpose
order by count desc
) as dados
where dados.count between 100 and 170) as purposes)
```
### exercício 7.F
``` sql
select count(*) from (select *
from uber_drives ud
where purpose in (select purpose from (select * from (
select distinct purpose, count(*)
from uber_drives ud
group by purpose
order by count desc
) as dados
where dados.count between 100 and 170) as purposes)) as viagens
where miles between 5 and 10
```
### exercício 7.G
``` sql
select * from (select *
from uber_drives ud
where purpose in (select purpose from (select * from (
select distinct purpose, count(*)
from uber_drives ud
group by purpose
order by count desc
) as dados
where dados.count between 100 and 170) as purposes)) as viagens
where miles between 5 and 10
and date_part('hour', end_date - start_date ) <= 1;
```
### exercício 7.H
Como a questão G estava com problemas, fiz as demais sem a condição acrescentada por ela.
``` sql
select * from (select *
from uber_drives ud
where purpose in (select purpose from (select * from (
select distinct purpose, count(*)
from uber_drives ud
group by purpose
order by count desc
) as dados
where dados.count between 100 and 170) as purposes)) as viagens
where miles between 5 and 10
and stop <> 'Unknown Location'
and start <> stop;
```
### exercício 7.I
``` sql
select * from (select *
from uber_drives ud
where purpose in (select purpose from (select * from (
select distinct purpose, count(*)
from uber_drives ud
group by purpose
order by count desc
) as dados
where dados.count between 100 and 170) as purposes)) as viagens
where miles between 5 and 10
and stop <> 'Unknown Location'
and start <> stop
and start_date between '2016-04-01%' and '2016-04-15';
```
### exercício 7.J
``` sql
select * from (select *
from uber_drives ud
where purpose in (select purpose from (select * from (
select distinct purpose, count(*)
from uber_drives ud
group by purpose
order by count desc
) as dados
where dados.count between 100 and 170) as purposes)) as viagens
where miles between 5 and 10
and stop <> 'Unknown Location'
and start <> stop
and start_date between '2016-04-01%' and '2016-04-15'
order by miles desc
limit 1
```
Sign in with Wallet
Connect another wallet