# DESAFIO DE BACK END - MODULO 1
## Grupo 1 - Questões - Contagem, Agrupamentos e Comparações

**1.** Quantos bruxos nascidos-trouxa (Muggle-born) ou mestiços (Half-blood) existem em cada casa (house)? Ordene-os decrescentemente e limite a busca em quatro (4) registros. Observe que não temos certeza sobre blood_status de alguns bruxos. Mas, nós estamos buscando aqui nessa questão pelos bruxos que são Muggle-born, Half-blood ou Muggle-born or Half-blood.

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

**2.** Qual é a casa que tem a menor quantidade de sangues-puro (Pure-blood), assumindo apenas aqueles que têm-se certeza? Remova, também, aqueles casos que a casa (House) seja nula e o (Blood Status) seja nula.

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

**3.** Quantas pessoas existem na família Weasley (assuma pessoas com algum nome de Weasley)?

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

**4.** Existe alguém na família Weasley que não seja ruivo (Red)?

select count(name)
from harry_potter
where name like '%Weasley' and hair_colour = 'red'
;

**5.** Todo bruxo consegue aprender o feitiço do patrono, um feitiço de proteção que toma forma de um animal. É um feitiço complexo e nem todos os bruxos possuem um patrono. Existem pessoas que possuem o mesmo animal patrono (patronus)? Remova os casos em que os valores sejam Unknown (do inglês, desconhecido), Non-corporeal (Não possui forma animal) ou None (nenhum) e ordene-os pela quantidade decrescentemente, limitando nos 3 maiores resultados.

select distinct patronus, count(*) 
from harry_potter
where patronus <> 'Unknown' and patronus <> 'Non-corporeal' and patronus <> 'None'
group by patronus
order by count(*) desc
limit 3
;

**6.** Utilizando apenas uma consulta com possibilidade de subqueries, responda: busque pelos quatro bruxas ou bruxos que possuem nomes que deram origem ao nome das casas de Hogwarts (House). Remova aqueles que sejam da espécie fantasma (Ghost). A resposta deve conter o sobrenome, o nome e a espécie.

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

**7.** Existiam bruxos que não eram da casa (house) Sonserina (Slytherin) que eram leais ao Lord Voldemort? Se existiram, qual são seus nomes? Desconsidere aqueles que não estão associados a nenhuma casa. A Resposta deve conter apenas o nome desses bruxos.

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

## Grupo 2 - Questões - Lógica

**1.** (REVISAR) A Batalha de Hogwarts foi uma intensa batalha em que vários bruxos morreram. Esta batalha ocorreu no dia 2 de maio (2 May) de 1998. Busque pelas quantidade de mortos nesta batalha entre aqueles que lutavam por Albus Dumbledore (Dumbledore's Army ou Albus Dumbledore) ou a Ordem da Fênix (Order of the Phoenix) versus aqueles que lutavam a favor de Lord Voldemort ou eram comensais da morte (Death Eaters), inclui-se também o nome Tom Marvolo Riddle que é o verdadeiro nome de Lord Voldemort. A resposta deve conter apenas os nomes, as casas e as lealdades. Dica: a busca por Dumbledore's Army pode ser feita da seguinte forma Dumbledore''s Army, ou seja com duas aspas simples ao pesquisar usando LIKE ou =.

select
	name, house, loyalty
from
	harry_potter
where
	death = '2 May, 1998'
	and loyalty like '%Dumbledore''s Army%'
	or (loyalty like '%Albus Dumbledore%' or loyalty like '%Order of the Phoenix%'
	or loyalty like '%Lord Voldemort%' or loyalty like '%Death Eaters%'
	or loyalty like '%Tom Marvolo Riddle%')
	limit 9
;

**2.** A primeira profecia de Sibila Trelawney é uma famosa profecia que colocou em dúvida quem seria o herói que teria poder para derrotar o grande vilão, Lord Voldemort. Provavelmente os bruxos não tinham um banco de dados tão interessante quanto o nosso mas a profecia tinha alguns requisitos que curiosamente alguns destes podem ser validados pelo dataset, a profecia dizia que um menino teria as seguintes características:

select
	name
from
	harry_potter
where
	gender = 'Male' and (loyalty like '%Albus Dumbledore%'
	or loyalty like '%Order of the Phoenix%'
	or loyalty like '%Order of the Phoenix%')
	and ( birth = '29 July, 1980' or birth = '30 July, 1980' or birth = '31 July, 1980')
;

**3.** Busque por todos os trabalhos (job) e separe-os individualmente e por fim, ordene-os por quantidade descrecente, além disso, filtre-os por aqueles que possuem acima de 2. Lembre-se um personagem pode ter zero ou mais trabalhos e àqueles maiores que 1 são separados por | (barra vertical entre espaços); Cuidado: existe uma armadilha nessa pergunta: na resposta deve existir apenas trabalhos distintos.

-- questao 3, grupo 2
select * from(
select distinct 
	unnest (string_to_array(job, ' | ')) as trabalho, count(job) 
from
	harry_potter
group by trabalho
order by count(job) desc 
) as new_table
where new_table.count >2;

**4.** (REVISAR. NÃO USEI SUBQUERIE MAS A RESPOSTA ESTÁ CERTA) Busque por todos aqueles que entram nessas restrições:
* Nasceram em Setembro September (birth);
* Não faleceram (death);
* Não possuem nomes iguais àqueles achados na questão 7 (utilize subquery);
* Possuem lealdade associada a alguém (loyalty);
* Que seu patrono não seja Unknown nem Non-corporeal;
* Que possuam alguma casa (house);
* Sejam do gênero feminino (gender);
A resposta deve conter os nomes das bruxas:

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'
	--and name <> 'Peter Pettigrew' and name <> 'Quirinus Quirrell'

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

**1.A.** Busque por todos os nomes de bruxos e quebre as palavras a fim de encontrarmos cada palavra usada como nome;

select
	unnest(string_to_array(name, ' ')) as nome
from
	harry_potter
limit 5;

**1.B.** Agora, agrupe os nomes a fim de encontrar a quantidade de vezes que o certo nome é repetido. (Lembre-se, existem pessoas com famílias, então espera-se repetição de nomes (que apareçam número maior do que 1));

select
	unnest(string_to_array(name, ' ')) as nome, count(name) 
from
	harry_potter
	group by name
limit 5;

**1.C.** Ordene-as palavras usadas como nome pela quantidade de repetições;

select * from (select nome1, count(*) from (select
	unnest (string_to_array(name, ' ')) as nome1 
from
	harry_potter	
) as new_table
group by
	nome1
order by count(*) desc
limit 5
) as tabela;

**1.D.** Filtre por todos aqueles nomes que tiveram 4 ou mais repetições;

select * from (select nome1, count(*) from (select
	unnest (string_to_array(name, ' ')) as nome1 
from
	harry_potter	
) as new_table
group by
	nome1
order by count(*) desc
) as tabela
where count >= 4;

**1.E.** Agora utilizando o recurso de JOIN, encontre todos os personagens que possuem nome que esteja no grupo encontrado na questão anterior (1.D);

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

**1.F.** Agora, filtre apenas por aqueles que estejam vinculados a casa de magia (house sejam IS NOT NULL);

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

**1.G.** Agora, encontre a quantidade de pessoas por casa de magia (house).

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

**2.A** Entre todas as habilidades, quebre-as e busque por aquelas que falem sobre as posições do Quadribol citadas anteriormente no enunciado.

select 
	unnest(string_to_array(skills, ' | ')) as nome
from
	harry_potter
where 
skills = 'Seeker' or skills = 'Keeper' or skills = 'Beater' or skills = 'Chaser'
limit 5
;

**2.B.** Sabendo das informações da questão anterior (2.A), liste a quantidade de jogadores por posição;

select nome, count(nome) from (
select 
	unnest(string_to_array(skills, ' | ')) as nome
from
	harry_potter
) as table2
where (nome = 'Keeper' or nome = 'Seeker' or nome = 'Chaser' or nome = 'Beater')
group by nome 
limit 5
;

**2.C.** Sabendo das informações da questão anterior (2.B), ordene-os os registros encontrados e busque pela posição que possui MENOR quantidade de jogadores;

select nome, count(nome) from (
select 
	unnest(string_to_array(skills, ' | ')) as nome
from
	harry_potter
) as table2
where (nome = 'Keeper' or nome = 'Seeker' or nome = 'Chaser' or nome = 'Beater')
group by nome
order by count(nome)
limit 1
;

**2.D.** Sabendo das informações da questão anterior (2.C), busque por todos os jogadores que são da posição encontrada.

select name, nome1 from (
select name, unnest(string_to_array(skills, ' | ')) as nome1 from harry_potter) as table3 inner join(
select nome, count(nome) from (
select 
	unnest(string_to_array(skills, ' | ')) as nome
from
	harry_potter
) as table2
where (nome = 'Keeper' or nome = 'Seeker' or nome = 'Chaser' or nome = 'Beater')
group by nome
order by count(nome)
limit 1
) as table4
on table3.nome1 = table4.nome

;

**3.** Para aqueles bruxos possuam patrono e que esses patronos não sejam Unknown, None e Non-corporeal nem NULL, busque pelos bruxos ou bruxas que possuem o mesmo animal. Por fim, ordene-os alfabeticamente e traga a informação de nome e o patrono.

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

**4.A.** Busque por todas possibilidades individuais de lealdade (Loyalty). Dica: os valores estão listados separando cada valor por | (barra vertical entre espaços);

select unnest (string_to_array(loyalty, ' | ' )) as lealdade
from harry_potter
limit 5;

**4.B.** Após saber da informação (4.A), busque por estas mesmas lealdades e suas respectivas quantidades;

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

**4.C.** Após saber da informação (4.B), busque por aquela lealdade que possui quantidade igual a 4 e que não seja igual ao ministro da magia (Minister of Magic).

select * from (
select unnest (string_to_array(loyalty, ' | ' )) as nome, count(*) 
from harry_potter
group by nome
order by count(*) desc 
) as table1
where nome <> 'Minister of Magic' and count = 4 
;

**4.D.** Após saber da informação (4.C), busque pelo nome das pessoas que possuem lealdade ao indíviduo ou organização encontrada na ©.

select name from ( -- esse select está mostrando os nomes 
select name, unnest (string_to_array(loyalty, ' | ' )) as nome1 from harry_potter -- esse select esta districhando as lealdades pq na primeira tabela as lealdades foram separadas. o name é para referenciar a tabela para nao ficar vazia quando usar o inner join 
) as table3 inner join (
select * from (
select unnest (string_to_array(loyalty, ' | ' )) as nome, count(*) 
from harry_potter
group by nome
order by count(*) desc 
) as table1
where nome <> 'Minister of Magic' and count = 4
)as table2
on table3.nome1 = table2.nome -- aqui ele ta unindo a igualdade entre as tabelas