Desafio SQL: Contando Hierarquias em Empresas
26/11/2024
Neste post, vamos abordar um problema desafiador que testa habilidades avançadas em SQL para navegar por hierarquias corporativas e consolidar informações de múltiplas tabelas. Este tipo de problema é ideal para quem deseja melhorar suas habilidades em junções, agrupamento e contagem de dados.
O Enunciado do Problema
Amber, fundadora de um conglomerado, adquiriu novas empresas, cada uma com uma estrutura hierárquica específica:
Fundador
Gerente Principal (Lead Manager)
Gerente Sênior (Senior Manager)
Gerente (Manager)
Funcionário (Employee)
Nosso objetivo é escrever uma consulta SQL que exiba:
O código da empresa (
company_code)O nome do fundador
O total de gerentes principais
O total de gerentes seniores
O total de gerentes
O total de funcionários
A saída deve ser ordenada em ordem crescente do código da empresa.
Observação:
O código da empresa (‘company_code’) deve ser tratado como string, portanto, a ordenação deve ser alfanumérica.
As tabelas podem conter registros duplicados.
Estrutura das Tabelas
Tabela Company
| Coluna | Tipo |
|---|---|
| company_code | String |
| founder | String |
Tabela Lead_Manager
| Coluna | Tipo |
| lead_manager_code | String |
| company_code | String |
Tabela Senior_Manager
| Coluna | Tipo |
| senior_manager_code | String |
| lead_manager_code | String |
| company_code | String |
Tabela Manager
| Coluna | Tipo |
| manager_code | String |
| senior_manager_code | String |
| lead_manager_code | String |
| company_code | String |
Tabela Employee
| Coluna | Tipo |
| employee_code | String |
| manager_code | String |
| senior_manager_code | String |
| lead_manager_code | String |
| company_code | String |
Solução em SQL
A consulta SQL que resolve o problema é a seguinte:
Explicação do Código
1. Estrutura Hierárquica
A hierarquia é construída conectando as tabelas de forma sequencial:
Company se conecta com Lead_Manager via
company_code.Lead_Manager se conecta com Senior_Manager via
lead_manager_code.Senior_Manager se conecta com Manager via
senior_manager_code.Manager se conecta com Employee via
manager_code.
2. Junções (LEFT JOIN)
Utilizamos LEFT JOIN para incluir todas as empresas, mesmo aquelas que não possuem todos os níveis hierárquicos preenchidos. Isso garante que o resultado exiba valores zerados para os níveis ausentes.
3. Contagem Distinta
Usamos COUNT(DISTINCT ...) para garantir que cada entidade seja contada apenas uma vez dentro de sua categoria. Por exemplo:
COUNT(DISTINCT lm.lead_manager_code)conta os gerentes principais únicos.COUNT(DISTINCT e.employee_code)conta os funcionários únicos.
4. Agrupamento
O GROUP BY c.company_code, c.founder agrupa os resultados por empresa e fundador, permitindo calcular as contagens para cada empresa separadamente.
5. Ordenação
O ORDER BY c.company_code organiza os resultados em ordem alfanumérica de códigos das empresas, conforme solicitado.
Exemplo de Saída
| company_code | founder | total_lead_managers | total_senior_managers | total_managers | total_employees |
| C1 | Monika | 1 | 2 | 1 | 2 |
| C2 | Samantha | 1 | 1 | 2 | 2 |
Resumo
Este desafio destaca a importância de entender estruturas hierárquicas e manipular dados relacionais complexos em SQL. Ao resolver este problema, praticamos:
Construção de consultas com múltiplas junções.
Uso de funções de agregação.
Tratamento de dados ausentes com
LEFT JOIN.Ordenação e agrupamento de dados.
Gostou deste desafio? Deixe seu comentário ou compartilhe como você o resolveria!
