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

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

Efetivamente, se recebemos a seguinte tabela:

Devemos retornar com a nossa query a seguinte tabela:

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

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.

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:

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:

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 🙂.