# #Back-end - Desafio M1

##### Aluno: Pedro Ciccone
###### tags: `Back-end`

### 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(*)
from harry_potter
where
	house is not null
	and (blood_status = 'Muggle-born'
		or blood_status = 'Half-blood'
		or blood_status = 'Muggle-born or Half-blood')
group by house
order by
	count (*) 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(*)
from harry_potter
where
	house is not null
	and blood_status is not null
	and blood_status = 'Pure-blood'
group by house
order by
	count (*) asc
	limit 1;

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

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

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

select count(*)
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`, `Non-corporeal` ou `None` (do inglês, desconhecido) e ordene-os pela quantidade decrescentemente, limitando nos 3 maiores resultados.

select 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, 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 house as sobrenome, name, species
from harry_potter
where
	species <> 'Ghost'
	and (name like '%Gryffindor'
		or name like '%Slytherin'
		or name like '%Hufflepuff'
		or name like '%Ravenclaw');

**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 e suas casas? 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 house is not null
	and loyalty like 'Lord Voldemort%';

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

**1.** 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 pela 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 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 count(*)
from harry_potter
where
	death like '2 May%'
	and (loyalty like '%Dumbledore''s Army%'
		or loyalty like '%Order of the Phoenix%'
		or loyalty like '%Albus Dumbledore%'
		or loyalty like '%Lord Voldemort%'
		or loyalty like '%Death Eaters%'
		or name = 'Tom Marvolo Riddle');

**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:
* Ser do gênero masculino.
* Seus pais tinham desafiado Voldemort três vezes e sobreviveram.
* Ele teria um poder que Voldemort era incapaz ou não queria compreender.
* Nascido nos últimos dias de julho de 1980, ano em que a profecia foi feita (possivelmente 29, 30 ou 31).
* Se mais de uma pessoa nascesse que satisfizer os requisitos, o próprio Voldemort iria escolher a quem acabaria por se referir.

Sua meta é encontrar nomes que poderiam satisfazer nossa vontade de descobrir quem poderia ser o herói.

**Observação:** bom, como dizia o ditado, "*o inimigo do meu inimigo é meu amigo*". Podemos assumir que aliados a Albus Dumbledore, Albus ou a Ordem da Fênix (`Order of the Phoenix`) eram também inimigos de Voldermort. Então isso pode ser um bom indicativo para tentar identificar nosso(s) herói(s).
Com base nesses requisitos quem são os possíveis heróis? A resposta deve conter apenas o nome do bruxo.

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

**3.** Busque por todos os trabalhos (`job`), 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 que 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.

select * from (
	select unnest(string_to_array(job, ' | ')) as trabalhos, count(*)
	from harry_potter
	where job is not null
	group by trabalhos
	order by count(*) desc) as trabalhos
where count > 2;

**4.** 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
	gender = 'Female'
	and 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 name not in (select name
		from harry_potter
		where
			house <> 'Slytherin'
			and house is not null
			and loyalty like 'Lord Voldemort%');

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

**1.** Todas as proposições abaixo PRECISAM ser executadas em uma só consulta, podendo utilizar subconsultas.

**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 nomes
from harry_potter;

**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 nomes, count(*)
from harry_potter
group by nomes;

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

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

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

select * from (
	select unnest(string_to_array(name, ' ')) as nomes, count(*)
	from harry_potter
	group by nomes
	order by count(*) desc) as qtd
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 
	tabela.name,
	tabela.house,
	tabela2.nomes
from 
(select name, house, unnest(string_to_array(name, ' ')) as nomes
	from harry_potter) as tabela
inner join
(select * from (
		select unnest(string_to_array(name, ' ')) as nomes, count(*)
		from harry_potter
		group by nomes
		order by count(*) desc) as qtd
	where count >= 4) as tabela2
on tabela.nomes = tabela2.nomes;

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

select * from (
		select 
		tabela.name,
		tabela.house,
		tabela2.nomes
	from 
	(select name, house, unnest(string_to_array(name, ' ')) as nomes
		from harry_potter) as tabela
	inner join
	(select * from (
			select unnest(string_to_array(name, ' ')) as nomes, count(*)
			from harry_potter
			group by nomes
			order by count(*) desc) as qtd
		where count >= 4) as tabela2
	on tabela.nomes = tabela2.nomes) as selecao
where house is not null;

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

select house, count(*) from(
	select * from (
			select 
			tabela.name,
			tabela.house,
			tabela2.nomes
		from 
		(select name, house, unnest(string_to_array(name, ' ')) as nomes
			from harry_potter) as tabela
		inner join
		(select * from (
				select unnest(string_to_array(name, ' ')) as nomes, count(*)
				from harry_potter
				group by nomes
				order by count(*) desc) as qtd
			where count >= 4) as tabela2
		on tabela.nomes = tabela2.nomes) as selecao
	where house is not null) as contagem
group by house
order by count(*) desc;

**2.** Todas proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas. O quadribol é um esporte bastante popular entre bruxos e existem várias posições muito parecidas com o Futebol ou Volei. No Quadribol, existem o apanhador (`Seeker`), o artilheiro (`Chaser`), o batedor (`Beater`) e até mesmo o goleiro (`Keeper`). A coluna de habilidades (`skills`) listam várias habilidades separadas por `|` (barra vertical entre espaços). Responda as perguntas abaixo com uma única consulta podendo utilizar subqueries:

**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 * from (
	select unnest(string_to_array(skills, ' | ')) as habilidades
	from harry_potter) as tabela
where habilidades = 'Seeker'
	or habilidades = 'Chaser'
	or habilidades = 'Beater'
	or habilidades = 'Keeper';

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

select distinct *, count(*) from (
	select unnest(string_to_array(skills, ' | ')) as habilidades
	from harry_potter) as tabela
where habilidades = 'Seeker'
	or habilidades = 'Chaser'
	or habilidades = 'Beater'
	or habilidades = 'Keeper'
group by habilidades;

**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 distinct *, count(*) from (
	select unnest(string_to_array(skills, ' | ')) as habilidades
	from harry_potter) as tabela
where habilidades = 'Seeker'
	or habilidades = 'Chaser'
	or habilidades = 'Beater'
	or habilidades = 'Keeper'
group by habilidades
order by
	count(*) asc
	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 * from (
	select name, unnest(string_to_array(skills, ' | ')) as habilidades
		from harry_potter) as nomes
	where habilidades in 
	(select habilidades from (
		select distinct habilidades, count(*) from (
			select unnest(string_to_array(skills, ' | ')) as habilidades
			from harry_potter) as tabela1
		where habilidades = 'Seeker'
			or habilidades = 'Chaser'
			or habilidades = 'Beater'
			or habilidades = 'Keeper'
		group by habilidades
		order by
			count(*) asc
			limit 1) as tabela2);

**3.** Para aqueles bruxos que 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(*)
		from harry_potter
		where
			patronus <> 'Unknown'
			and patronus <> 'None'
			and patronus <> 'Non-corporeal'
			and patronus is not null
		group by patronus) as qtd
	where count > 1)
order by patronus;

**4.** Todas as proposições abaixo PRECISAM ser executadas em uma só consulta podendo utilizar subconsultas:

**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 lealdades
from harry_potter;

**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 lealdades, count(*)
from harry_potter
group by lealdades
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 lealdades, count(*)
	from harry_potter
	group by lealdades
	order by count(*) desc) as selecao
where
	count = 4
	and lealdades <> 'Minister of Magic';

**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 no ponto anterior.

select name
from 
	(select name, unnest(string_to_array(loyalty, ' | ')) as lealdades
	from harry_potter) as selecao3
inner join
	(select * from (
		select unnest(string_to_array(loyalty, ' | ')) as lealdades, count(*)
		from harry_potter
		group by lealdades
		order by count(*) desc) as selecao
	where
		count = 4
		and lealdades <> 'Minister of Magic') as selecao2
on selecao3.lealdades = selecao2.lealdades;