# Semana de Desafio - Módulo 1 - Grupos 1, 2 e 3

## Grupo 1 - Questões - Contagem, Agrupamentos e Comparações

### Questão 1
------

select
	house,
	count(*)
from
	harry_potter
where 
	(blood_status = 'Muggle-born'
	or blood_status = 'Half-blood')
	and house is not null
	group by house
	order by count(*) desc;

### Questão 2
------

select
	house,
	count(*)
from
	harry_potter
where 
	blood_status = 'Pure-blood'
	and house is not null
	group by house
	order by count(*) asc
	limit 1;

### Questão 3
------

select distinct 
	count(*)
from
	harry_potter
where 
	name like '%Weasley%'

### Questão 4
------

select distinct 
	count(*)
from
	harry_potter
where 
	name like '%Weasley%'
	and hair_colour <> 'Red'

### Questão 5
------

select 
	patronus,
	count(*)
from
	harry_potter
where
	patronus not in ('Unknown', 'Non-corporeal', 'None')
	group by patronus
	order by count(*) desc
	limit 3

### Questão 6
------

select 
	split_part(name, ' ', 2) as sobrenome,
	name,
	species
from
	harry_potter
where 
	name like '%Slytherin'
	or name like '%Gryffindor'
	or name like '%Ravenclaw'
	or name like '%Hufflepuff'

### Questão 7
------

select 
	name
from
	harry_potter
where
	loyalty like '%Lord Voldemort%'
	and	house <> 'Slytherin'

## Grupo 2 - Questões - Lógica
### Questão 01
------

select
	name,
	house,
	loyalty,
	death 
from
	harry_potter
where
	 death like '2 May%' 

### Questão 02
------

select
	name
from
	harry_potter
where
	(birth like '29 July%'
	or birth like '30 July%'
	or birth like '31 July%')
	and loyalty like '%Order of the Phoenix%'

### Questão 03
------

select
	(unnest((string_to_array(job,' | ')))) as nome,
	count(*)
from
	harry_potter
where
	job is not null
	group by nome
	order by count(*) desc
	limit 4

### Questão 04
------

select
	name
from
	harry_potter
where
	birth like '%September%'
	and death is null 
	and loyalty is not null 
	and (patronus <> 'Unknown' and     patronus <> 'Non-corporeal')
	and house is not null
	and gender = 'Female'

## Grupo 3 - Questões - Subqueries e JOIN

### 01.A
------

select
	(unnest((string_to_array(name,' ')))) as nome
from
	harry_potter
	group by nome

### 1.B
------

select
	(unnest((string_to_array(name,' ')))) as nome,
	count(*)
from
	harry_potter
	group by nome

### 1.C
------

select
	(unnest((string_to_array(name,' ')))) as nome,
	count(*)
from
	harry_potter
	group by nome
	order by count(*) desc

### 1.D
------

select * from (
	select
		(unnest((string_to_array(name,' ')))) as nome,
		count(*)
	from
		harry_potter
		group by nome
		order by count(*) desc
) as nomes
where count >= 4

### 1.E
------

select name, hp.nome, house
	from (
		select 
			name,
			(unnest((string_to_array(name,' ')))) as nome,
			house
			from
				harry_potter
	) as hp
inner join (
		select * from (
			select
				(unnest((string_to_array(name,' ')))) as nome,
				count(*)
			from
				harry_potter
				group by nome
				order by count(*) desc
		) as top_nomes
		where count >= 4
	) as nomes_separados
on nomes_separados.nome = hp.nome
	

### 1.F
------

select name, hp.nome, house
	from (
		select 
			name,
			(unnest((string_to_array(name,' ')))) as nome,
			house
			from
				harry_potter
	) as hp
inner join (
		select * from (
			select
				(unnest((string_to_array(name,' ')))) as nome,
				count(*)
			from
				harry_potter
				group by nome
				order by count(*) desc
		) as top_nomes
		where count >= 4
	) as nomes_separados
on nomes_separados.nome = hp.nome
where house is not null

### 1.G
------

select house, count(*)
	from (
		select 
			name,
			(unnest((string_to_array(name,' ')))) as nome,
			house
			from
				harry_potter
	) as hp
inner join (
		select * from (
			select
				(unnest((string_to_array(name,' ')))) as nome,
				count(*)
			from
				harry_potter
				group by nome
				order by count(*) desc
		) as top_nomes
		where count >= 4
	) as nomes_separados
on nomes_separados.nome = hp.nome
where house is not null
group by house	
	

### 2.A
------

select 
	*
from (
	select 
		(unnest((string_to_array(skills,' | ')))) as nome
	from
		harry_potter
) as quadribol
where 
    nome in ('Seeker', 'Chaser', 'Beater', 'Keeper')

### 2.B
------

select 
	nome,
	count(*)
from (
	select 
		(unnest((string_to_array(skills,' | ')))) as nome
	from
		harry_potter
) as quadribol
where 
    nome in ('Seeker', 'Chaser', 'Beater', 'Keeper')
    group by nome

### 2.C
------

select 
	nome,
	count(*)
from (
	select 
		(unnest((string_to_array(skills,' | ')))) as nome
	from
		harry_potter
) as quadribol
where 
	nome in ('Seeker', 'Chaser', 'Beater', 'Keeper')
	group by nome
	order by count(*) asc
	limit 1

### 2.D
------

select 
	name,
	hp.nome
from (
	select
		name,
		(unnest((string_to_array(skills,' | ')))) as nome
	from
	  harry_potter
	) as hp
inner join (
	select
		nome,
		count(*)
	from (
		select 
			(unnest((string_to_array(skills,' | ')))) as nome
		from
			harry_potter
	) as quadribol
	where 
		nome in ('Seeker', 'Chaser', 'Beater', 'Keeper')
		group by nome
		order by count(*) asc
		limit 1
) as posicao
on posicao.nome = hp.nome

### 3
------

select 
	name,
	patronus
from harry_potter hp 
where patronus in (
	select 
		patronus 
	from (
		select
			patronus,
			count(*)
		from
			harry_potter
		where
			patronus <> 'Unknown'
			and patronus <> 'None'
			and patronus <> 'Non-corporeal'
			and patronus is not null
			group by patronus
	) as p
	where count > 1
)	
order by patronus asc	

### 4.A
------

select 
	unnest((string_to_array(loyalty,' | '))) as loyalty_part
from harry_potter hp

### 4.B
------

select 
	unnest((string_to_array(loyalty,' | '))) as loyalty_part,
	count(*)
from harry_potter hp
group by loyalty_part 
order by count(*) desc

### 4.C
------

select 
	*
from (
	select 
		unnest((string_to_array(loyalty,' | '))) as loyalty_part,
		count(*)
	from harry_potter hp
	group by loyalty_part 
) as lealdade
where count = 4
and loyalty_part <> 'Minister of Magic'

### 4.D
------

select 
	name
from (
	select
	name,
	unnest((string_to_array(loyalty,' | '))) as loy
	from harry_potter hp 
) as hp_loy
inner join (
	select 
		*
	from (
		select 
			unnest((string_to_array(loyalty,' | '))) as loyalty_part,
			count(*)
		from harry_potter hp1
		group by loyalty_part 
	) as lealdade
	where count = 4
	and loyalty_part <> 'Minister of Magic'
) as lp
on lp.loyalty_part = hp_loy.loy