O problema de SQL que quase me enlouqueceu

Estou me preparando para algumas vagas de Engenheiro de Dados e para isso, é claro, eu preciso dominar SQL. Só ficar lendo livros de SQL não me ajuda nisso e por esse motivo eu gosto de ficar fazendo questões em sites como o hackerrank.

Costumo fazer umas questões toda quarta para me desafiar e em uma semana dessas eu encontrei o que, para mim, é a questão mais desafiadora que já enfrentei. Embora ela esteja classificada como “média” no site, eu demorei um certo tempo para resolver ela.

Se estou aqui agora é porque eu consegui e tenho como objetivo, nesse artigo, mostrar o passo a passo que tomei para resolver esse problema. O problema em si está no link a seguir.

Occupations | HackerRank

💡 Se desafie a tentar resolver o problema sem ler o artigo primeiro. Caso não consiga, volte aqui e veja o meu passo a passo para comparar 😊

Passo 1: Entendendo o Problema

Nesse desafio, nos é dado uma tabela chamada “OCCUPATIONS” com duas colunas: “Name” e “Occupation”. A primeira coluna é auto explicativa, nos dá o nome de uma pessoa. A segunda coluna nos apresenta a profissão da pessoa com nome definido na coluna anterior.

As profissões disponíveis na tabela são “Doctor”, “Actor”, “Singer” e “Professor”.

Nos é pedido para “Pivotar” a coluna “Occupations”, de forma que os nomes das pessoas sejam ordenados de forma alfabética e mostrados abaixo de sua profissão correspondente, sendo que no header devemos ter, respectivamente: Doctor, Professor, Singer e Actor.

Devemos mostrar NULL se não há mais nomes correspondentes para uma ocupação

💡 Pivot não tem uma tradução óbvia para o português, por isso é comum ouvir “Pivotar”, mas dá para entender como algo do tipo “girar a tabela” ou “tabela virada”.

Efetivamente, se recebemos a seguinte tabela:

Name	Occupation
Samantha	Doctor
Julia	Actor
Maria	Actor
Meera	Singer
Ashley	Professor
Ketty	Professor
Christeen	Professor
Jane	Actor
Jenny	Doctor
Priya	Singer

Devemos retornar com a nossa query a seguinte tabela:

Doctor	Professor	Singer	Actor
Jenny	Ashley	Meera	Jane
Samantha	Christeen	Priya	Julia
NULL	Ketty	NULL	Maria

Passo 2: Abstraindo o Problema

Antes de falar sobre ferramenta e quais comandos usar para resolver o problema, vamos abstrair ele. Se você é de computação você deve saber quanto a palavra “abstrair” é famosa na área.

Seguindo um passo a passo, temos a seguinte abstração para resolver o problema:

1. Separar as pessoas por profissão
2. Ordenar as pessoas de cada profissão por nome
3. Selecionar as pessoas na profissão “Doctor” e colocar na primeira coluna
4. Selecionar as pessoas na profissão “Professor” e colocar na segunda coluna
5. Selecionar as pessoas na profissão “Singer” e colocar na terceira coluna
6. Selecionar as pessoas na profissão “Actor” e colocar na última coluna

Seguindo esse algoritmo nós resolvemos o problema.

Passo 3: Entender quais técnicas usar para resolver o problema.

Passos 1 e 2 da abstração

💡 Para fins de implementação, estou usando comandos do MySQL. Pode ser que esses comandos existam ou não em outros SGBDs, ou podem ter seus próprios comandos que implementam a mesma ideia.

Seguindo o algoritmo que definimos no passo anterior, as etapas 1 e 2 podem ser facilmente resolvidas por um SELECT, usando também uma função muito interessante: a função row_number(), que retorna um número para cada linha em uma dada partição.

💡 Para saber mais sobre a função row_number() acesse o link https://www.javatpoint.com/mysql-row_number-function ou busque outras fontes de sua preferência

Dessa forma, escrevendo a query da seguinte forma:

select name, occupation, row_number() 
	over (partition by occupation order by name) as sequential_num
from Occupations;

Teremos como retorno a tabela:

Name	Occupation	s_no
Jane	Actor	1
Julia	Actor	2
Maria	Actor	3
Jenny	Doctor	1
Samantha	Doctor	2
Ashley	Professor	1
Christeen	Professor	2
Ketty	Professor	3
Meera	Singer	1
Priya	Singer	2

row_number() vai iterar sobre (over) uma partição. Nesse caso, estamos iterando sobre a partição occupation (partition by occupation), e dessa forma separando as pessoas por profissão. Além disso, estamos ordenando as pessoas por nome (order by name).

Ótimo, com essa primeira query já resolvemos os dois primeiros passos da abstração. O passo 3 até o 6, agora, só são casos de seleção dentro dessa tabela que criamos anteriormente.

Passos 3 a 6 da abstração

Com todas as pessoas organizadas por profissão e por ordem alfabética, podemos selecionar todas as pessoas de determinada profissão com um select case:

select
    case when occupation = 'Doctor' then name end,
    case when occupation = 'Professor' then name end,
    case when occupation = 'Singer' then name end,
    case when occupation = 'Actor' then name end
from (select name, occupation, row_number()
        over (partition by occupation order by name) as sequential_num
from Occupations)

Entretanto, essa query irá nos retornar uma lista muito longa e vai listar primeiro os atores, depois os médicos, depois os professores e por último os cantores tendo 3 NULL em cada linha e somente um nome de pessoa e não é isso que queremos.

O que queremos é que toda linha esteja preenchida com nomes de pessoas e que NULL só apareça caso não tenham mais pessoas em uma profissão.

Como queremos manter a ordem alfabética na nossa última seleção, vamos selecionar a primeira pessoa em cada lista de profissões, ou seja: min(case when occupation = '{profissão}' then name end

select
    min(case when occupation = 'Doctor' then name end),
    min(case when occupation = 'Professor' then name end),
    min(case when occupation = 'Singer' then name end),
    min(case when occupation = 'Actor' then name end)
from (select name, occupation, row_number()
        over (partition by occupation order by name) as sequential_num
from Occupations) P;

Além de adicionar o uso da função min para selecionar a primeira pessoa de cada lista, por determinação do SQL, toda tabela derivada (nosso select interior) precisa ter um apelido e eu chamei ele de “P”, para “Pivot”.

Com isso temos a primeira linha da nossa lista. Só falta conseguir as demais linhas e para isso usamos um famoso GROUP BY.

Pelo o que agrupamos? Pelo número de sequência que foi obtido com nosso row_number()

select
    min(case when occupation = 'Doctor' then name end),
    min(case when occupation = 'Professor' then name end),
    min(case when occupation = 'Singer' then name end),
    min(case when occupation = 'Actor' then name end)
from (select name, occupation, row_number()
        over (partition by occupation order by name) as sequential_num
from Occupations) P
group by P.sequential_num;

E pronto! Temos nosso resultado do jeitinho que queríamos.

Se você quiser ver a mágica que o GROUP BY causou, experimenta adicionar P.sequential_num as seq_num logo após o select e antes do primeiro min(…). Você vai ter algo semelhante a isso:

seq_num	Doctor	Professor	Singer	Actor
1	Jenny	Ashley	Meera	Jane
2	Samantha	Christeen	Priya	Julia
3	NULL	Ketty	NULL	Maria

Lindo, não é?

Conclusão

Com esse artigo pude explicar passo a passo meu processo de raciocínio para resolver esse problema bem desafiante e interessante.

Com ele eu espero ter te ajudado a aprender algo novo em SQL e até mesmo ter te incentivado a experimentar um pouco de “programação competitiva” em sites como o hackerrank.

Dúvidas, sugestões, críticas e comentários são muito bem vindo na sessão de comentários abaixo. Agradeço muito pelo tempo investido aqui e espero te ver nos próximos artigos. Até a próxima 🙂.