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

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;

------

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

------

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

------

select distinct 
	start
from uber_drives
limit 5;

-----

select distinct 
	stop 
from uber_drives
limit 5;

------

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

------

select distinct 
	purpose
from uber_drives;

-----

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

--------

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

-----------

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

-----------

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;

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

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

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

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;

-------

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

--------

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'

--------

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
;