# Back-end 28/07/2020

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

------

select * from (
	select 
	category, count(*)
	from times
	group by category
	order by count(*) desc
) as honras
where count between 3 and 6;

--------

select * from times
	where category in (
			select category from (
			select 
			category, count(*)
			from times
			group by category
			order by count(*) desc
		) as honras
		where count between 3 and 6
		order by count desc
		limit 2
)

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

select * from (
	select
	birth_country, count(*)
	from
	nobel
	group by birth_country
) as honras
where count between 10 and 50
order by count desc

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

select prize_share from (
	select
		prize_share, count(*)
	from nobel 
	group by prize_share
	order by count asc
) as prize

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

select 
	full_name, category, year
from nobel
where prize_share in (
	select prize_share from (
		select
		prize_share, count(*)
		from nobel 
		group by prize_share
		order by count asc
	)  as prize
	limit 1
)
limit 5

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

select 
	category, count(*)
from nobel
where prize_share in (
	select prize_share from (
		select
		prize_share, count(*)
		from nobel 
		group by prize_share
		order by count asc
	)  as prize
	limit 1
)
group by category
order by count(*) desc

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

select 
	category, count(*)
from nobel
where prize_share in (
	select prize_share from (
		select
		prize_share, count(*)
		from nobel 
		group by prize_share
		order by count asc
	)  as prize
	limit 1
)
group by category
having (count(*)) > 30

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

select name, count(*) from (
	select 
	unnest((string_to_array(name,' '))) as name
	from cervejarias
) as names
group by name
order by count(*) desc
limit 4

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

select name, count(*) from (
	select 
	unnest((string_to_array(name,' '))) as name
	from cervejas
) as names
group by name
order by count(*) desc
limit 5

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

select count(*) from (
	select name, count(*) from (
		select 
		unnest((string_to_array(name,' '))) as name
		from cervejarias 
	) as names 
	group by name
	order by count desc) as names_word
	where count = 1

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

select count(*) from (
	select name, count(*) from (
		select 
		unnest((string_to_array(name,' '))) as name
		from cervejas 
	) as names 
	group by name
	order by count desc) as names_word
	where count = 1

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

select * from (
	select
	unnest((string_to_array(name,' '))) as names_1
	from cervejarias i 
	group by names_1
	) as c1
where c1.names_1 in (select 
		unnest((string_to_array(name,' '))) as names_2
		from cervejas 
		group by names_2
		) 

-----------

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

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

select 
	unnest((string_to_array(casting,', '))) actors,
	count(*)
from
	netflix
	group by actors
	order by count desc;

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

select 
	unnest((string_to_array(casting,', '))) actors,
	count(*)
from
	netflix
	group by actors
	order by count desc
	limit 10;

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

select * from (
	select 
		unnest((string_to_array(casting,', '))) actors,
		count(*)
	from
		netflix
		group by actors
		order by count desc 
		) as a
where count between 10 and 30
and count not between 15 and 20

---------

select * from (
	select 
		unnest((string_to_array(casting,', '))) actors,
	    unnest((string_to_array(country,', '))) country,
		count(*)
	from
		netflix
	where country not like '%,%'
	and country not like 'India'
		group by actors, country
		order by count desc 
		) as a
where count >= 10

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

select * from (
	select
	unnest((string_to_array(director,', '))) as director
	from netflix 
	group by director
	) as d
where d.director in (select 
		distinct unnest((string_to_array(casting,', '))) as actors
		from netflix
		)