Problema e solução de SQL do LeetCode: Os três maiores salários do departamento

Procurando uma explicação detalhada do exercício mais difícil do LeetCode SQL 50? Compilamos uma explicação detalhada desse problema de SQL, com o processo de raciocínio por trás da solução e instruções sobre como criar a consulta passo a passo.

O LeetCode é uma plataforma on-line popular que oferece uma variedade de problemas de SQL criados para ajudá-lo a aprender, praticar e se preparar para entrevistas. Eles também oferecem problemas de programação em outras linguagens. Os problemas são agrupados em tópicos como algoritmos, estruturas de dados, bancos de dados, design de sistemas e linguagens de programação individuais.

O LeetCode também inclui um plano de estudo de SQL com 50 problemas para que você adquira todas as habilidades necessárias para ser aprovado em uma entrevista de ciência de dados. Mas se você estiver procurando mais do que apenas 50 problemas para aprender e praticar, considere os mais de 120 exercícios em nosso cursoSQL para Iniciantes . Também temos uma coleção de mais de 100 perguntas para entrevistas sobre SQL criadas especificamente para você praticar antes de uma entrevista de emprego.

Neste artigo, daremos uma olhada de perto no problema mais difícil do conjunto de SQL do LeetCode: Os três salários mais altos do departamento. Discutiremos o processo de pensamento por trás da solução e como criar a consulta passo a passo. Pronto?

O problema "Department Top Three Salaries

Department Top Three Salaries é o único problema difícil do plano de estudo SQL 50 do LeetCode. Há vários pontos em que você pode ficar preso, portanto, vamos resolver esse problema uma etapa de cada vez.

A tarefa

Os executivos de uma empresa estão interessados em saber quem ganha mais dinheiro em cada um dos departamentos da empresa. Um funcionário que ganha muito é aquele que tem um salário entre os três maiores salários exclusivos desse departamento.

Escreva uma solução para encontrar os funcionários que ganham mais em cada um dos departamentos.

Dê uma olhada no esquema do banco de dados para essa tarefa:

A solução

Etapa 1: Localizar os funcionários com altos salários da empresa

Vamos começar encontrando os funcionários com altos salários sem nos preocuparmos com os departamentos. A descrição do problema especifica que uma pessoa de alto salário é qualquer pessoa com um salário que esteja entre os três principais salários exclusivos. Isso significa que, se houver várias pessoas com o mesmo salário, todas elas serão as que ganham mais. Dê uma olhada na tabela de resultados a seguir:

Observe que, embora estejamos selecionando apenas os quatro salários mais altos, pode haver mais de quatro pessoas com salários altos. Por exemplo, Will e Max compartilham o mesmo valor de salário e ambos acabam na lista dos que ganham mais, com a mesma classificação salarial de 3.

Não podemos usar os sites básicos ORDER BY e LIMIT para buscar os que ganham mais, pois não podemos saber quantos são os que ganham mais antes de executar a consulta! Mas podemos filtrar o resultado pela classificação salarial. Vamos discutir como podemos obter essa classificação, já que ela não está incluída no banco de dados.

Há três funções de classificação no SQL: RANK(), DENSE_RANK(), e ROW_NUMBER(). Todas elas retornarão uma classificação de valores dentro do quadro de janela e da direção de ordenação especificados. A diferença entre elas é como lidam com valores duplicados. Você pode ler uma explicação detalhada sobre como usar as funções de classificação em nossa Visão geral das funções de classificação no SQL.

Dê uma olhada no mesmo cenário de antes, mas agora com todas as funções de classificação incluídas:

Como você pode ver, RANK() e DENSE_RANK() retornam a mesma classificação se os salários forem iguais; esse é o comportamento de que precisamos para garantir que várias pessoas possam ser incluídas como pessoas com altos salários se tiverem o mesmo salário. Max seria excluído da lista dos 3 primeiros ao usar ROW_NUMBER(), embora tenha um salário entre os 3 primeiros.

A diferença entre RANK() e DENSE_RANK() é como eles lidam com os buracos na classificação deixados por valores duplicados. RANK() pula um número de classificação sequencial para cada classificação duplicada, resultando em valores não duplicados com a mesma classificação. DENSE_RANK() não pula classificações no caso de duplicatas. É isso que queremos para este problema, pois elimina a omissão de um salário top 3 quando há dois ou mais salários top 2.

Aqui está uma consulta que atribuirá a todos os funcionários uma classificação com base em seu salário usando DENSE_RANK():

SELECT
  e.name,
  e.salary,
  DENSE_RANK() OVER(ORDER BY e.salary DESC)
FROM employee e;

Ótimo! Agora estamos obtendo o mesmo resultado que no primeiro exemplo: Todos os funcionários recebem classificações correspondentes à sua classificação salarial. A próxima etapa é criar uma classificação separada para cada departamento.

Etapa 2: Encontrar os que ganham mais no departamento

Podemos adicionar um PARTITION BY dentro da cláusula OVER() para especificar o quadro da janela para cada linha com base em um determinado valor.

Neste problema, estamos procurando criar uma classificação dos 3 principais salários em cada departamento, portanto, devemos particionar a moldura da janela pelo ID do departamento. Para se aprofundar nessa expressão, leia nosso artigo sobre Como usar PARTITION BY com OVER().

Aqui está a consulta que atribuirá as classificações separadamente para cada departamento:

SELECT
  d.name AS department, 
  e.name AS employee,
  e.salary,
  DENSE_RANK() OVER(PARTITION BY d.id ORDER BY e.salary DESC) AS rank
FROM employee e
JOIN department d 
  ON d.id = e.departmentId;

Temos que juntar os campos department e employee porque a tarefa nos pede para exibir os nomes dos departamentos. Entretanto, a cláusula PARTITION BY deve usar a coluna department.id (a department (a chave primária da tabela) para garantir que não haja dois departamentos mapeados para a mesma partição (o que poderia acontecer se dois departamentos tivessem o mesmo nome).

Etapa 3: criar a consulta final

Ótimo! Temos uma classificação local de salários para cada departamento. E agora?

Não podemos filtrar pela coluna de classificação porque as funções de janelas não são permitidas na cláusula WHERE.

Teremos de envolver a consulta de classificação em um CTE e obter o resultado final a partir daí. Filtraremos pela coluna de classificação para incluir apenas os três principais salários de cada departamento. Lembre-se de não incluir a própria classificação no resultado final, pois precisamos apenas dos salários.

Dê uma olhada na solução completa do problema:

WITH ranks AS (
SELECT
  d.name AS department, 
  e.name AS employee,
  e.salary,
  DENSE_RANK() OVER(PARTITION BY d.id ORDER BY e.salary DESC) AS salary_rank
FROM employee e
JOIN department d 
  ON d.id = e.departmentId
)
SELECT
  department,
  employee,
  salary,
FROM ranks 
WHERE  salary_rank <= 3;

Assim, temos uma lista completa dos que ganham mais para cada departamento!

Resolvido: O problema SQL mais difícil do LeetCode!

Muito bem feito! Analisamos a fundo o problema mais difícil do plano de estudos do LeetCode SQL 50.

Está procurando mais prática além do plano SQL 50? Experimente nossa trilhaTrilha de Práticas em SQL e escolha entre mais de 1.000 exercícios interativos de SQL!