# Exercícios de Backend 21/07/2020

## 1 - Questão

select distinct 
	listed_in,
	count(listed_in)
from
	netflix
	group by listed_in
	order by count(listed_in) desc
	limit 5;

## 2 - Questão

select distinct 
	listed_in,
	avg(split_part(duration, ' ', 1)::integer) as duration 
from
	netflix
	group by listed_in
	order by duration desc
	limit 5;

## 3 - Questão

select
   release_year,
   listed_in,
   count(release_year)
from
   netflix
where type <> 'Movie'
   group by release_year, listed_in 
   order by  count(release_year) desc
   limit 5;
  

## 4 - Questão

 select
	count(date_added)
from
	netflix
where
	DATE_PART('year', date_added)::integer <> release_year::integer;

## 5 - Questão

select
	country,
	type,
	count(type)
from
	netflix
where type <> 'Movie'
	and country <> 'United States'
	group by country, type 
	order by count(type) desc
	limit 5;

## 6 - Questão

select
	listed_in,
	rating,
	count(rating)
from
	netflix
where rating = 'R'
	group by listed_in, rating 
	order by count(rating) desc
	limit 5;

## 7 - Questão

select 
	title,
	casting
from
	netflix
where
	casting like 'Antonio Banderas%';

## 8 - Questão

select 
	unnest(string_to_array(casting, ', ')) as actors
from
	netflix
	limit 5;

## 9 - Questão

select 
	show_id,
	type,
	title
from	
	netflix
where
	title like '%Transformers%'
	or title like  '%Star Wars%'
	or title like '%Harry Potter%'
	or title like '% Lord of the Rings%'

## 10 - Questão

select  
	type,
	count(type)
from
	netflix
where
	description like '%dystopian%'
	or description like '%zombie%'
	group by type;

## 11 - Questão

select
	release_year
from
	netflix
where
	type <> 'Movie'
	and country not like 'United States%'
	and country not like '%United Kingdom%'
	order by release_year::integer asc
	limit 5;

## 12 - Questão

select 
	duration,
	count(duration)
from
	netflix
where
	type = 'Movie'
	and ((SPLIT_PART(duration, ' ', 1))::integer >= 90
	and (SPLIT_PART(duration, ' ', 1))::integer <= 100)
	and (DATE_PART('year', date_added)::integer >= 2015 
	and DATE_PART('year', date_added)::integer <= 2017)
	and listed_in like '%Documentaries%'
	group by duration
	order by count(duration) desc;

## 13 - Questão

select  
	unnest(string_to_array(country, ', ')),
	count(country)
from
	netflix
where
	listed_in like '%Sci-Fi & Fantasy%'
	and country not like '%United States%'
	group by country
	order by count(country) desc
	limit 5
	offset 10;

## 14 - Questão

select
	count(show_id)
from
	netflix
where 
	not (listed_in like '%Comed%' or listed_in like '%Action%')
	and country like '%Brazil%';
----- parte 2 -----
select 
    count(show_id) 
from 
    netflix 
where 
    country like '%Brazil%';

## 15 - Questão

select 
	listed_in,
	country,
	count(listed_in)
from
	netflix
where
	listed_in like '%Music & Musicals%'
	and not (country like 'United States%'
	or country like '%United Kingdom%'
	or country like '%India%')
	group by listed_in, country
	order by count(listed_in) desc
	limit 5;