---
# System prepended metadata

title: Exercícios Back-End 01/08/2020

---

# Exercícios Back-End 01/08/2020

```sql
select
	purpose,
	avg(date.end - date.start)
from (
	select
	purpose,
	end_date as end,
	start_date as start
	from
	uber_drives
) as date
group by purpose;
```

------

```sql
select
	purpose,
	avg(miles)
from uber_drives
group by purpose;
```


------


```sql
select 
	category,
	count(*)
from uber_drives
group by category 
order by count(*) desc;
```

------

```sql
select distinct 
	start
from uber_drives
limit 5;
```

-----

```sql
select distinct 
	stop 
from uber_drives
limit 5;
```

------

```sql
select 
	max(miles) as max,
	min(miles) as min
from uber_drives
```


------

```sql
select distinct 
	purpose
from uber_drives;
```

----- 

```sql
select  
	purpose,
	count(*)
from uber_drives
group by purpose;
```


--------

```sql
select  
	purpose,
	count(*)
from uber_drives
where
purpose is not null
group by purpose
order by count(*) desc;
```

-----------

```sql
select 
	*
from (
	select  
		purpose,
		count(*)
	from uber_drives
	where
	purpose is not null
	group by purpose
	order by count(*) desc
) as purpose
where count between 100 and 170
```

-----------

```sql
select 
	*
from uber_drives
where purpose in (select 
			purpose 
			from (
			select  
				purpose,
				count(*)
			from uber_drives
			where
			purpose is not null
			group by purpose
			order by count(*) desc
		) as purpose
		where count between 100 and 170)
limit 5;
```


---------------

```sql
select 
	count(purpose)
from uber_drives
where purpose in ( 
			select 
			purpose 
			from (
			select  
				purpose,
				count(*)
			from uber_drives
			where
			purpose is not null
			group by purpose
			order by count(*) desc
		) as purpose
		where count between 100 and 170
		)
and miles between 5 and 10
```

--------------

```sql
select 
	*
from uber_drives
where purpose in ( 
			select 
			purpose 
			from (
			select  
				purpose,
				count(*)
			from uber_drives
			where
			purpose is not null
			group by purpose
			order by count(*) desc
		) as purpose
		where count between 100 and 170
		)
and miles between 5 and 10
and (date_part('hour', end_date) - date_part('hour', start_date)) <= 1
order by start_date
limit 5;
```
-------
```sql
select 
	*
from uber_drives
where purpose in ( 
			select 
			purpose 
			from (
			select  
				purpose,
				count(*)
			from uber_drives
			where
			purpose is not null
			group by purpose
			order by count(*) desc
		) as purpose
		where count between 100 and 170
		)
and miles between 5 and 10
and (date_part('hour', end_date) - date_part('hour', start_date)) <= 1
and start <> 'Unknown Location'
and stop <> 'Unknown Location'
and start = stop
order by start_date
```
--------

```sql
select 
	*
from uber_drives
where purpose in ( 
			select 
			purpose 
			from (
			select  
				purpose,
				count(*)
			from uber_drives
			where
			purpose is not null
			group by purpose
			order by count(*) desc
		) as purpose
		where count between 100 and 170
		)
and miles between 5 and 10
and (date_part('hour', end_date) - date_part('hour', start_date)) <= 1
and start <> stop and stop	<> 'Unknown Location' and start <> 'Unknown Location'
and start_date between '2016-03-01' and '2016-03-15'
```
--------
```sql
select 
	*
from uber_drives
where purpose in ( 
			select 
			purpose 
			from (
			select  
				purpose,
				count(*)
			from uber_drives
			where
			purpose is not null
			group by purpose
			order by count(*) desc
		) as purpose
		where count between 100 and 170
		)
and miles between 5 and 10
and (date_part('hour', end_date) - date_part('hour', start_date)) <= 1
and start <> stop and stop	<> 'Unknown Location' and start <> 'Unknown Location'
and start_date between '2016-03-01' and '2016-03-15'
order by miles desc limit 1
;

```