---
# System prepended metadata

title: Back-end 28/07/2020

---

# Back-end 28/07/2020

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

------

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

--------

```sql
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
)
```

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


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


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


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


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



```sql
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
```

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


```sql
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
```


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

```sql
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
```


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

```sql
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
```


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


```sql
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
```

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


```sql
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
```
	
----------------

```sql
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
```

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

```sql
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
		) 
```

-----------

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

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


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



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

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


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


```sql
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
```


---------

```sql
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
```

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

```sql
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
		) 
```
