Top 27 Entrevistas SQL Avançadas Perguntas com Respostas

Onde um profissional SQL pode encontrar um guia abrangente para perguntas avançadas de entrevista SQL? A resposta mais curta é: aqui! Selecionamos as 27 perguntas SQL mais importantes e as respondemos para você.

A preparação para uma entrevista SQL não é fácil, especialmente se seu trabalho requer o conhecimento de SQL avançado. Este artigo contém as 27 perguntas SQL avançadas mais comuns para entrevistas e fornece respostas detalhadas e recursos para leitura posterior.

Vamos analisar estes quatro conceitos principais e mais alguns outros:

• JOINs
• GROUP BY, WHERE, e HAVING
• CTEs (Expressões comuns da tabela) e consultas recursivas
• Funções de Janela (Window Functions) em SQL

A melhor maneira de atualizar seus conhecimentos avançados de SQL é tomando nosso SQL Avançado pista. Possui mais de 300 exercícios práticos para funções de janela, Expressões de Mesa Comum, funções recursivas e muito mais.

Vamos atacar estas questões frontalmente, sem mais delongas!

1) O que é um JOIN em SQL?

JOIN é um comando SQL que permite a combinação de duas ou mais tabelas. Isto é feito através de uma coluna comum (ou seja, uma coluna que tem os mesmos valores em ambas as tabelas), que permite utilizar dados de duas ou mais tabelas ao mesmo tempo. Unir tabelas em SQL é essencial devido à natureza dos bancos de dados relacionais: os dados são atomizados em tabelas, com cada tabela contendo apenas uma parte dos dados disponíveis no banco de dados.

Vamos usar duas tabelas para mostrar como isto funciona. A primeira tabela é football_players.

id	first_name	last_name	national_team_id	games_played
1	Gianfranco	Zola	1	35
2	Virgil	van Dijk	2	53
3	Marcus	Rashford	3	51
4	Kylian	Mbappé	5	66
5	Phil	Foden	3	22
6	Frenkie	de Jong	2	22
7	Mario	Balotelli	1	36
8	Erling	Haaland	6	23

A segunda é national_team.

id	country
1	Italy
2	Netherlands
3	England
4	Croatia

Aqui está uma consulta que une duas mesas:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Ela seleciona colunas de ambas as tabelas. Para juntá-las, primeiramente fazemos referência a uma tabela na cláusula FROM. Em seguida, JOIN, e depois disso vem a segunda tabela. Usamos a cláusula ON para especificar a condição com a qual as tabelas serão unidas: o national_team_id na football_players deve ser igual à coluna id na national_team mesa.

A saída da consulta é:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

INNER JOIN é uma das várias junções distintas em SQL. Sua característica é que só retorna dados das tabelas unidas onde a condição de união é verdadeira. Aqui estão mais detalhes sobre como funciona o SQL INNER JOIN.

Qual é a diferença entre INNER JOIN, LEFT JOIN, RIGHT JOIN, e FULL JOIN?

Existem diferentes tipos de junção em SQL. As junções mais utilizadas são INNER JOIN, LEFT JOIN, RIGHT JOIN, e FULL JOIN. LEFT JOIN, RIGHT JOIN, e FULL JOIN são as chamadas junções externas. JOIN (aka INNER JOIN) é uma união interna. Neste caso, 'interior' significa que ela retorna apenas as linhas de ambas as tabelas que satisfazem a condição de união; as uniões externas retornam todas as linhas de uma tabela, mais as linhas correspondentes na(s) outra(s) tabela(s). A exceção é FULL JOIN, que retorna todas as linhas de ambas as tabelas.

Aqui está o resultado do INNER JOIN do exemplo anterior. Vamos repetir aqui. Dessa forma, será mais fácil ver a diferença entre as diferentes uniões.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36

LEFT JOIN retorna todos os dados da tabela da esquerda (ou seja, a primeira tabela, que é listada antes\ à esquerda da palavra-chave JOIN ) e somente as linhas correspondentes da tabela da direita (a segunda tabela, listada depois\ à direita da palavra-chave JOIN ). Se não houver dados correspondentes na tabela da direita, os valores em falta são mostrados como NULLs. Aqui está a mesma consulta com LEFT JOIN substituindo por INNER JOIN:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
LEFT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

A tabela da esquerda aqui é football_playerse o direito é national_team. Como era de se esperar, a produção é diferente:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23

Todos os jogadores de futebol da mesa da esquerda estão aqui. Entretanto, Kylian Mbappe e Erling Haaland não têm um país correspondente na mesa da direita, portanto, há NULLs na coluna country para esses jogadores. Estas fileiras não estavam presentes no resultado INNER JOIN. Elas foram adicionadas pelo LEFT JOIN.

O RIGHT JOIN faz o oposto: retorna todos os dados da tabela da direita e somente os dados correspondentes da tabela da esquerda. Quando não há dados correspondentes na tabela da esquerda, os valores em falta são mostrados como NULLs.

Aqui está o código:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
RIGHT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Tudo permanece o mesmo, exceto que estamos usando RIGHT JOIN ao invés de LEFT JOIN. Esta é a saída:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
NULL	NULL	NULL	NULL	Croatia	NULL

Agora temos todas as seleções nacionais e seus jogadores. Mas você pode ver que há um país (Croácia) que não tem jogadores na tabela da esquerda. As colunas de jogadores para a Croácia são preenchidas com NULLs.

FULL JOIN O resultado é a saída de todos os dados de todas as mesas juntas. Novamente, se não houver dados correspondentes na tabela correspondente, os valores em falta aparecerão como NULL.

Mais uma vez, mudamos o tipo de junção na consulta:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
FULL JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Ele retornará todos os dados de ambas as tabelas. Qualquer dado não coincidente é substituído por NULLs. Todos os jogadores estão no resultado, mesmo que não tenham um país correspondente na outra tabela. Todos os países estão no resultado, mesmo que não tenham jogadores na tabela de football_player mesa. O resultado FULL JOIN é a união de LEFT JOIN e RIGHT JOIN:

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	NULL	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	NULL	23
NULL	NULL	NULL	NULL	Croatia	NULL

Você pode encontrar mais informações no artigo sobre diferentes tipos de uniões. Você também pode ver nossa Cláusulas JOIN em SQL Cheat Sheet para um rápido refrescamento.

3) O que é um CROSS JOIN?

Um CROSS JOIN é outro tipo de junção disponível em SQL. Ele retorna um produto cartesiano. Isto significa que CROSS JOIN retornará cada linha da primeira tabela combinada com cada linha da segunda tabela.

Ele não é usado com muita freqüência. Mas se você se sentir tentado a usá-lo, pense duas vezes. Retornar todas as combinações de fileiras pode levar algum tempo - se a consulta terminar de todo!

Como exemplo, vamos usar as tabelas como nas duas perguntas anteriores. Para escrever a pergunta, use a palavra-chave CROSS JOIN. Como este é um tipo de join que retorna todas as combinações de linhas de todas as tabelas, não há nenhuma cláusula ON. Dê uma olhada:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
CROSS JOIN national_team nt;

Aqui está a saída. Todos os jogadores no football_players tabela estão listados com todos os países no national_team mesa.

id	first_name	last_name	national_team_id	country	games_played
1	Gianfranco	Zola	1	Italy	35
2	Virgil	van Dijk	2	Italy	53
3	Marcus	Rashford	3	Italy	51
4	Kylian	Mbappé	5	Italy	66
5	Phil	Foden	3	Italy	22
6	Frenkie	de Jong	2	Italy	22
7	Mario	Balotelli	1	Italy	36
8	Erling	Haaland	6	Italy	23
1	Gianfranco	Zola	1	Netherlands	35
2	Virgil	van Dijk	2	Netherlands	53
3	Marcus	Rashford	3	Netherlands	51
4	Kylian	Mbappé	5	Netherlands	66
5	Phil	Foden	3	Netherlands	22
6	Frenkie	de Jong	2	Netherlands	22
7	Mario	Balotelli	1	Netherlands	36
8	Erling	Haaland	6	Netherlands	23
1	Gianfranco	Zola	1	England	35
2	Virgil	van Dijk	2	England	53
3	Marcus	Rashford	3	England	51
4	Kylian	Mbappé	5	England	66
5	Phil	Foden	3	England	22
6	Frenkie	de Jong	2	England	22
7	Mario	Balotelli	1	England	36
8	Erling	Haaland	6	England	23
1	Gianfranco	Zola	1	Croatia	35
2	Virgil	van Dijk	2	Croatia	53
3	Marcus	Rashford	3	Croatia	51
4	Kylian	Mbappé	5	Croatia	66
5	Phil	Foden	3	Croatia	22
6	Frenkie	de Jong	2	Croatia	22
7	Mario	Balotelli	1	Croatia	36
8	Erling	Haaland	6	Croatia	23

Você pode saber mais sobre o CROSS JOIN neste guia ilustrado para o SQL CROSS JOIN.

4) O que é um Auto-ajuste em SQL?

Como você provavelmente suspeita, uma auto-junção ocorre quando a mesa é unida a si mesma. É importante notar que não é um comando distinto em SQL: qualquer tipo JOIN pode ser usado para unir uma mesa consigo mesma.

A união é feita como qualquer outro JOIN, mas desta vez você referenciará a mesma tabela em ambos os lados da palavra-chave JOIN. A auto-união é especialmente útil quando uma tabela tem uma chave estrangeira referenciando sua chave primária. Isto permite que você consulte dados hierárquicos, tais como árvores genealógicas ou a hierarquia organizacional de uma empresa. Também é útil quando se deseja encontrar pares de valores. No exemplo abaixo, estamos procurando jogadores da mesma equipe nacional:

SELECT 
  fp1.id,
  fp1.first_name,
  fp1.last_name,
  fp1.national_team_id,
  fp2.id AS id_2,
  fp2.first_name AS first_name_2,
  fp2.last_name AS last_name_2,
  fp2.national_team_id as national_team_id_2
FROM football_players fp1 
JOIN football_players fp2
ON fp1.id <> fp2.id
AND fp1.national_team_id = fp2.national_team_id;

Auto-junta significa em vez de duas tabelas, você está especificando a mesma tabela duas vezes: uma na cláusula FROM e outra após a cláusula JOIN. Como você está usando a mesma tabela duas vezes, você deve usar pseudônimos para as tabelas. Cada ocorrência da tabela deve receber um apelido distinto (fp1, fp2 em nossa consulta) para que fique claro a qual ocorrência da tabela estamos nos referindo.

Estamos juntando jogadores da mesma equipe nacional (seus valores national_team_id são iguais). Entretanto, não queremos listar um jogador consigo mesmo, portanto excluímos o caso quando fp1.id e fp2.id forem iguais.

O resultado da consulta é o seguinte:

id	first_name	last_name	national_team_id	id_2	first_name_2	last_name_2	national_team_id_2
1	Gianfranco	Zola	1	7	Mario	Balotelli	1
2	Virgil	van Dijk	2	6	Frenkie	de Jong	2
3	Marcus	Rashford	3	5	Phil	Foden	3
5	Phil	Foden	3	3	Marcus	Rashford	3
6	Frenkie	de Jong	2	2	Virgil	van Dijk	2
7	Mario	Balotelli	1	1	Gianfranco	Zola	1

Você pode usar as colunas national_team_id de ambas as tabelas para confirmar que, na verdade, Gianfranco Zola e Mario Balotelli jogaram pela mesma equipe.

Você pode saber mais sobre isto em nosso artigo sobre exemplos de auto-ajuda.

5. junte-se a duas mesas usando uma junção de duas colunas

Você recebe duas tabelas. A primeira é employeeque possui os seguintes dados:

id	first_name	last_name
1	Steve	Bergman
2	Steve	Johnson
3	Steve	King

A segunda tabela é customerque possui os seguintes dados:

id	first_name	last_name
1	Ann	Coleman
2	Steve	Bergman
3	Steve	Young
4	Donna	Winter
5	Steve	King

Sua tarefa é devolver todos os funcionários que também são clientes da empresa. Infelizmente, você não pode usar a coluna id porque é a identificação do funcionário em uma tabela e a identificação do cliente em outra. Em outras palavras, não há uma única coluna em uma tabela que faça referência à outra.

A solução é juntar as tabelas no primeiro e último nome, ou seja, usar uma coluna de duas colunas JOIN.

O código abaixo unirá primeiro as tabelas sobre o primeiro nome. Depois disso, a palavra-chave AND estabelece a segunda condição de junção, que é o sobrenome. Dessa forma, você obterá dados de ambas as tabelas onde a combinação do primeiro nome/último nome é a mesma. Se usássemos apenas uma destas colunas, poderíamos ter obtido os dados errados porque funcionários e clientes podem ter o mesmo primeiro nome, mas sobrenomes diferentes (ou vice versa). Aqui está a pergunta:

SELECT 
  e.first_name, 
  e.last_name
FROM employee e
JOIN customer c
ON e.first_name = c.first_name
AND e.last_name = c.last_name;

Aqui está o código de saída..:

first_name	last_name
Steve	Bergman
Steve	King

O resultado mostra que Steve Bergman e Steve King são tanto funcionários da empresa quanto clientes.

6. junte-se a duas mesas utilizando uma non-Equi JOIN

Até o momento, temos usado eqüi-juntas: junções onde há um sinal de igualdade na condição ON. Por outro lado, a adesão non-equi é uma adesão que tem uma condição de non-equidade na cláusula ON.

Desta vez, temos dados sobre usuários móveis e seu uso de dados. A primeira tabela é mobile_userque mostra os usuários móveis e seu limite mensal de dados móveis em MB:

id	first_name	last_name	mobile_data_limit
1	Michael	Watson	5,000
2	Nicole	Gomez	10,000
3	Sam	Stone	8,000

A segunda tabela é data_usageque mostra o uso real dos dados mensais do usuário em MB:

id	mobile_user_id	data_used	period
1	1	4,987	2022_10
2	2	6,875	2022_10
3	3	12,547	2022_10
4	1	5,037	2022_11
5	2	11,111	2022_11
6	3	4,897	2022_11

A tarefa é encontrar todos os dados onde o uso real estava acima do limite mensal. Queremos ver o primeiro e último nome do usuário, o limite mensal, os dados reais utilizados e o período de tempo.

A solução é usar a junção non-equi, como mostrado abaixo:

SELECT 
  first_name,
  last_name,
  mobile_data_limit,
  data_used,
  period
FROM mobile_user mu
JOIN data_usage du
ON mu.id = du.mobile_user_id
AND mobile_data_limit < data_used;

A consulta seleciona todas as informações necessárias a partir de duas tabelas. As tabelas são unidas usando um INNER JOIN. Primeiro unimos as tabelas onde o ID do usuário é o mesmo. Em seguida, adicionamos a segunda condição após a palavra-chave AND. Aqui temos uma condição de non-equidade que nos dará dados onde o limite está abaixo do uso mensal.

Você pode ver o resultado abaixo:

first_name	last_name	mobile_data_limit	data_used	period
Sam	Stone	8,000	12,547	2022_10
Michael	Watson	5,000	5,037	2022_11
Nicole	Gomez	10,000	11,111	2022_11

Se você estiver interessado, aqui estão mais alguns exemplos de non-equi junte.

7) O que a DISTINCT faz?

DISTINCTO objetivo da DISTINCT, em geral, é remover valores duplicados. Ou, dito de outra forma, para mostrar valores únicos na saída da sua consulta.

Imagine que você está trabalhando com isto loans tabela que mostra as identificações dos empréstimos e suas durações em meses.

loan_id	loan_duration
100112	60
100205	60
100208	48
100333	120
100357	48
100398	120

Empréstimos diferentes podem ter a mesma duração, então você quer extrair a lista de possíveis durações de empréstimo. Você pode fazer isso usando DISTINCT:

SELECT DISTINCT loan_duration
FROM loans
ORDER BY loan_duration; 

A saída mostra que há empréstimos com durações de 48, 60, e 120 meses:

loan_duration
48
60
120

DISTINCT pode ser usado em SELECT com uma coluna para mostrar apenas os valores únicos daquela coluna, como no exemplo acima. Se for usado em SELECT mas com várias colunas, então a saída mostrará as combinações únicas de todas estas colunas.

Você também pode usar DISTINCT com funções agregadas. Se você fizer isso, sua consulta eliminará resultados de agregação duplicados.

Você pode ver exemplos destes usos em nosso artigo falando sobre o papel do DISTINCT no SQL.

8) O que o GROUP BY SQL faz?

GROUP BY é uma cláusula SQL utilizada para organizar os dados em grupos com base em um valor ou valores comuns. Ela é mais comumente usada com funções agregadas; esta combinação retornará dados agregados para cada grupo. Entretanto, é importante saber que o uso de funções agregadas dentro da cláusula GROUP BY não é permitido.

A sintaxe geral GROUP BY é:

SELECT 
  column_1,
  column_2,
  …,
FROM table_name
WHERE …
GROUP BY column_1, column_2
HAVING …
ORDER BY column_1, column_2;

Suponha que haja a seguinte tabela salaries:

id	first_name	last_name	salary	department
1	Nicholas	Poirot	4,798.44	IT
2	Samantha	Wolf	5,419.24	IT
3	Stewart	Johnsons	5,419.24	IT
4	Jackie	Biden	8,474.54	Sales
5	Mark	Hamilton	10,574.84	Sales
6	Mariana	Costa	9,747.54	Sales
7	Paul	Stewart	3,498.12	Accounting
8	Mary	Rutte	4,187.23	Accounting
9	Thomas	Schwarz	3,748.55	Accounting

Usaremos GROUP BY e AVG() para encontrar o salário médio por departamento:

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department;

Queremos ver os departamentos, por isso selecionamos esta coluna. Para calcular o salário médio, aplicamos a função AVG() à coluna salary.

Todas as colunas listadas em GROUP BY definem os grupos de dados. Em nosso exemplo, os grupos são definidos pela coluna do departamento: calculamos o salário médio para cada departamento.

Nosso agrupamento e agregação de dados se assemelha a isto:

department	average_salary
Accounting	3,811.30
Sales	9,598.97
IT	5,212.31

O salário médio em Contabilidade é de 3.811,30. O salário médio nos outros dois departamentos é de 9.598,97 e 5.212,31, respectivamente.

Ao escrever uma consulta, GROUP BY sempre tem que vir depois de WHERE, mas antes da cláusula HAVING. Você pode saber mais sobre isso neste artigo sobre GROUP BY em SQL.

9. como filtrar GROUP BY Groups?

Uma vez obtidos os grupos que você especificou em GROUP BY, às vezes você vai querer filtrá-los. A dica para fazer isso está na sintaxe da pergunta anterior. A cláusula que lhe permite filtrar grupos é HAVING.

Após os critérios de filtragem serem especificados em HAVING, a consulta retornará todos os dados que satisfazem os critérios. Todos os outros dados serão filtrados.

Veja como funciona nos dados da pergunta anterior se tivéssemos que mostrar apenas departamentos com um salário médio abaixo de 5.500 dólares.

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department
HAVING AVG(salary) < 5500;

O código é muito parecido com o da pergunta anterior. A diferença é a cláusula HAVING. Utilizamo-la para filtrar os resultados e mostrar apenas os departamentos com salários abaixo de 5.500 dólares.

Aqui está o que o código retorna:

department	average_salary
Accounting	3,811.30
IT	5,212.31

O departamento que falta na produção é Vendas porque seu salário médio é de 9.598,97.

Você pode encontrar exemplos práticos adicionais desta cláusula neste artigo explicando o HAVING em SQL.

10 Qual é a diferença entre WHERE e HAVING?

Se você sabe as respostas às duas perguntas anteriores, você provavelmente sabe a resposta a esta pergunta.

A principal diferença é que WHERE é usado para filtrar os dados antes de serem agrupados. Sua posição na declaração SELECT mostra o seguinte: ela vem antes de GROUP BY. Devido a sua finalidade, nenhuma função agregada é permitida em WHERE.

HAVINGpelo contrário, é utilizada para filtrar dados após o agrupamento; é por isso que é utilizada após GROUP BY. Além disso, HAVING permite condições que incluem funções agregadas.

A melhor maneira de aprender a distinção é ler este artigo sobre ONDE vs. HAVING em SQL.

11. o que a seguinte tentativa de filtrar NULLs retornará?

Você receberá com freqüência este tipo de pergunta em sua entrevista SQL avançada: você receberá um código e terá que descrever o retorno da consulta. Enquanto escreve e lê o código SQL anda de mãos dadas, ainda se sente diferente quando você tem que analisar o código que outra pessoa escreveu.

Você tem dados na tabela contributors:

id	first_name	last_name	start_date	termination_date
1	Valeria	Bogdanov	2022-10-11	NULL
2	Nicholas	Bertolucci	2022-04-07	2022-11-11
3	Mathilde	Bauman	2022-05-25	2022-10-01
4	Trevor	Trucks	2022-01-28	NULL
5	Maria	Szabo	2022-03-15	NULL

O que este código retornará?

SELECT 
  first_name,
  last_name,
  start_date,
  termination_date
FROM contributors
WHERE termination_date != '2022-10-01';

Se você responder que ele retornará todas as linhas exceto ID = 3, você está errado! Esta é uma espécie de pergunta com rasteira. Ao ler a condição WHERE, você poderia lê-la como: devolver todos os dados onde a data de terminação é diferente de 2022-10-01. Ao olhar para a tabela, você pensaria que são todas as linhas, exceto uma.

E é, mas não para SQL! Como você pode ver, há três filas com valores NULL. Para SQL, NULL não é igual a um valor; é um valor que não tem valor. Portanto, quando você configura a condição em ONDE assim, estará excluindo todas as datas que não são iguais aos valores de 2022-10-01 e NULL.

Aqui está a saída como prova:

first_name	last_name	start_date	termination_date
Nicholas	Bertolucci	2022-04-07	2022-11-11

Você pode saber mais sobre este e outros operadores de comparação utilizados com o NULL.

12. escreva uma pergunta que encontre o número de canções por artista. Use LEFT JOIN e COUNT().

Suponha que lhe sejam dadas duas tabelas: artist e song.

Aqui está o artist dados:

id	artist_name
1	Prince
2	Jimi Hendrix
3	Santana
4	Otis Redding
5	Lou Rawls

Abaixo estão os song dados:

id	artist_id	song_title
1	1	Purple Rain
2	2	Purple Haze
3	3	Europa
4	1	Cream
5	1	Bambi
6	1	Why You Wanna Treat Me So Bad?
7	2	Spanish Castle Magic
8	3	Taboo
9	3	Incident at Neshabur
10	3	Flor D' Luna

Você precisa usar LEFT JOIN e COUNT() para encontrar todos os artistas, suas identificações e o número de suas músicas no banco de dados.

Você pode ser tentado a sugerir esta solução:

SELECT 
  a.id,
  artist_name,
  COUNT(*) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Vamos dar uma olhada na saída:

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	1
5	Lou Rawls	1

A saída mostra todos os artistas; tudo bem. Entretanto, o número de músicas para Otis Redding e Lou Rawls é um, o que é errado! Dê uma olhada na tabela songe você verá que não há identificações de artistas iguais a 4 ou 5.

O que deu errado? Ao utilizar COUNT(*) com LEFT JOIN, a função agregada contará todos os valores não correspondentes (NULLs). É por isso que o resultado mostrou uma canção cada para Otis Redding e Lou Rawls, embora eles não tenham nenhuma canção na tabela.

COUNT(*) é usada para contar todas as fileiras. Para dar uma resposta correta, deve-se usar COUNT(song_title) em seu lugar.

SELECT 
  a.id,
  artist_name,
  COUNT(song_title) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Este código lhe dará a saída correta:

id	artist_name	number_of_songs
1	Prince	4
2	Jimi Hendrix	2
3	Santana	4
4	Otis Redding	0
5	Lou Rawls	0

O número de músicas do Príncipe, Jimi Hendrix e Santana permaneceu o mesmo que na saída anterior. Entretanto, o número de canções dos outros dois artistas é agora zero, e essa é a contagem correta.

13 Qual é a diferença entre JOIN e UNION?

JOIN é uma cláusula SQL utilizada para unir duas ou mais tabelas. Ela permite o uso de dados de todas as tabelas unidas. Em outras palavras, colunas de todas as tabelas são mostradas uma ao lado da outra, o que significa que os dados são empilhados horizontalmente.

UNION é um operador conjunto usado para combinar os resultados de duas ou mais declarações SELECT. Os dados são empilhados verticalmente. Um dos requisitos ao usar UNION é que deve haver um número igual de colunas em todas as declarações do SELECT sindicalizadas. Além disso, todas as colunas selecionadas têm que ser do mesmo tipo de dados.

14 Qual é a diferença entre UNION e UNION ALL?

O que eles têm em comum é que ambos são operadores definidos. Além disso, ambos os operadores são usados para o mesmo propósito: fundir dados de duas ou mais declarações SELECT.

Os requisitos relativos ao número de colunas e seu tipo de dados também são os mesmos.

Agora, a diferença é que a UNION retorna apenas registros únicos. Por outro lado, UNION ALL retorna todos os registros, o que inclui duplicatas.

Normalmente, o UNION ALL é mais rápido porque não classifica o resultado para remover as duplicatas. A regra geral é usar o UNION ALL por padrão. Use UNION somente se você precisar de resultados únicos ou se tiver certeza absoluta de que sua consulta não retornará dados duplicados.

Você pode saber mais sobre sua sintaxe e uso neste artigo sobre as diferenças entre UNION e UNION ALL.

15 O que é uma Subquery em SQL?

Uma subconsulta é uma consulta escrita dentro de outra consulta SQL. A consulta 'outra' é chamada de consulta principal, enquanto uma subconsulta às vezes é também chamada de consulta aninhada.

As subconsultas podem ser usadas nas instruções SELECT, INSERT, UPDATE, e DELETE. Elas também podem ser usadas em cláusulas como FROM ou WHERE, que é o uso mais comum.

Aqui está um exemplo. A tabela é productse armazena informações sobre nomes, quantidades e categorias de produtos:

id	product_name	quantity	product_category
1	Apple MacBook Air (2020) MGN63N/A Space Gray	319	Laptop
2	Fairphone 4 128GB Green 5G	208	Mobile phone
3	Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver	157	Desktop
4	HP 17-cp0971nd	487	Laptop
5	Huawei P30 Pro - 128GB - Blue	148	Mobile phone
6	Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC	514	Desktop
7	Toshiba Dynabook Satellite Pro E10-S-101 Notebook	207	Laptop
8	Samsung Galaxy S23 5G - 256GB - Phantom Black	56	Mobile phone
9	Intel Compleet PC | Intel Core i7-10700	459	Desktop

Usaremos uma subquisição e mostraremos a quantidade total por categoria de produto, mas somente para os produtos individuais cuja quantidade está acima da quantidade média para todos os produtos.

Aqui está a solução:

SELECT 
  product_category,
  SUM(quantity) AS product_quantity
FROM products
WHERE quantity > (SELECT AVG(quantity)
			FROM products)
GROUP BY product_category;

A consulta seleciona a categoria do produto e soma a quantidade usando a função agregada SUM(). Há uma condição em WHERE que diz que somente os produtos individuais cuja quantidade esteja acima da média serão incluídos na soma. Usamos a subconsulta e a função AVG() para obter esta média.

A consulta retorna duas filas:

product_category	product_quantity
Laptop	806
Desktop	973

Uma categoria está faltando porque não satisfaz os critérios de filtragem - telefones celulares.

Há diferentes tipos de subconsultas, como escalar, múltiplas linhas e consultas correlatas. Você pode saber mais sobre eles em nosso artigo sobre tipos de subconsultas.

16. escreva uma consulta para devolver os vendedores e seus dados de vendas mensais acima da média de suas vendas pessoais. Use uma subconsulta relacionada à Cor.

Uma subconsulta correlata é um tipo de subconsulta que utiliza valores da consulta externa. É verificado uma vez para cada linha que a consulta externa retorna, o que pode retardar o desempenho.

No entanto, a pergunta insiste em usá-la, portanto, vamos ver os dados.

A primeira tabela é salesperson:

id	first_name	last_name
1	Nina	Lee
2	Carolina	Green
3	Mick	Johnson

A outra tabela é sales:

id	salesperson_id	monthly_sales	period
1	1	1,200.47	2021_10
2	2	5,487.22	2021_10
3	3	700.47	2021_10
4	1	15,747.54	2021_11
5	2	16,700.87	2021_11
5	3	14,322.87	2021_11
6	1	9,745.55	2021_12
7	2	9,600.97	2021_12
8	3	6,749.58	2021_12

Sua tarefa é usar uma subconsulta correlata e devolver o nome completo do vendedor, suas vendas mensais e os períodos em que suas vendas estão acima de sua média pessoal.

Aqui está a solução:

SELECT 
  first_name,
  last_name,
  monthly_sales,
  period
FROM salesperson sp
JOIN sales s
ON sp.id = s.salesperson_id
WHERE monthly_sales > 
   (SELECT AVG(monthly_sales)
    FROM sales
    WHERE salesperson_id = sp.id);

A consulta seleciona todas as colunas necessárias. Estes dados são de ambas as tabelas, por isso nos unimos a elas.

Agora vem a parte crucial. Para filtrar os dados, usamos a cláusula WHERE. A condição diz que a consulta deve retornar todos os dados onde as vendas mensais são mais altas do que a média de vendas de cada vendedor. Como calculamos essas vendas médias individuais? Usando a função AVG() na subconsulta que escrevemos na cláusula WHERE.

Aqui está a saída:

first_name	last_name	monthly_sales	period
Nina	Lee	15,747.54	2021_11
Carolina	Green	16,700.87	2021_11
Mick	Johnson	14,322.87	2021_11
Nina	Lee	9,745.55	2021_12

Há mais alguns exemplos neste artigo sobre subconsultas correlatas.

17. O que é Funções de Janela (Window Functions) em SQL em SQL?

As funções da janela SQL recebem seu nome pelo fato de serem aplicadas a uma janela de dados. Esta janela é simplesmente um conjunto de linhas relacionadas com a linha atual.

As funções de janela são iniciadas pela cláusula OVER(). Outra cláusula importante é PARTITION BY, que define partições de dados dentro de uma estrutura de janela. Quando esta cláusula é omitida, a partição é a tabela de resultados completa. Quando PARTITION BY é usado, você pode definir uma ou mais colunas pelas quais os dados serão particionados. Você pode olhar para ela como GROUP BY para funções de janela.

Outra cláusula importante é ORDER BY. Ela ordena os dados dentro da janela. No contexto das funções de janela, esta cláusula dá instruções sobre a ordem na qual a função será executada.

Para saber mais, consulte este artigo sobre funções de janela.

18 Qual é a diferença entre Funções de Janela (Window Functions) em SQL e GROUP BY?

A única semelhança que eles compartilham é que tanto GROUP BY quanto as funções de janela podem ser - e muito freqüentemente são - usadas com as funções agregadas, e ambas trabalham em um conjunto de linhas.

Entretanto, quando se usa GROUP BY, a saída é mostrada como grupos, e não se pode ver as linhas individuais que formam o grupo.

As funções de janela não têm tais problemas. Uma de suas características é que elas não colapsam as linhas individuais ao mostrar os dados agregados. Isto significa que é possível mostrar os dados agregados e não agregados simultaneamente.

As funções de janela são usadas para mais do que a agregação de dados, como você verá na pergunta a seguir. Mas se você quiser saber mais sobre o tópico atual, temos um artigo que explica as funções de janela vs. GROUP BY.

19. O que Funções de Janela (Window Functions) em SQL Você Sabe?

As funções de janela SQL podem ser geralmente divididas em quatro categorias:

• Funções de Ranking
• Funções de distribuição
• Funções analíticas
• Funções agregadas

As funções de classificação são:

• ROW_NUMBER() - Retorna um número único para cada linha dentro de uma partição; os valores empatados têm números de linhas diferentes.
• RANK() - Classifica os dados dentro de uma partição; os valores empatados têm a mesma classificação, e há uma lacuna após os empates (por exemplo, 1, 2, 3, 3, 5).
• DENSE_RANK() - Classifica os dados dentro de uma partição; os valores empatados têm a mesma classificação e não há uma lacuna na classificação (por exemplo, 1, 2, 3, 3, 4).

As funções de distribuição são:

• PERCENT_RANK() - Retorna a classificação relativa dentro de uma partição.
• CUME_DIST() - Retorna a distribuição cumulativa dentro de uma partição.

As funções analíticas são: Retorna a distribuição relativa dentro de uma partição:

• LEAD() - Permite acessar valores de uma linha subseqüente em relação à linha atual.
• LAG() - Permite acessar os valores de uma linha anterior em relação à linha atual.
• NTILE() - Divide as linhas dentro de uma partição em grupos aproximadamente iguais.
• FIRST_VALUE() - Permite acessar os valores da primeira linha dentro de uma partição.
• LAST_VALUE() - Permite acessar os valores da última linha dentro de uma partição.
• NTH_VALUE() - Permite acessar a n-ésima fileira dentro de uma partição.

Finalmente, as funções agregadas são:

• AVG() - Retorna um valor médio para as linhas dentro de uma partição.
• COUNT() - Retorna o número de valores nas linhas de uma partição.
• MAX() - Retorna o valor máximo para as linhas em uma partição.
• MIN() - Retorna o valor mínimo para as linhas em uma partição.
• SUM() - Devolve o valor da soma das linhas em uma partição.

Nossa Funções de Janela (Window Functions) em SQL Cheat Sheet lhe fornecerá mais informações sobre todas estas funções.

20. Como você cria um ranking em SQL?

A maneira mais fácil de classificar os dados em SQL é usar uma das três funções da janela de classificação:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Você recebe este conjunto de dados chamado album_sales com os seguintes dados:

id	artist	album	copies_sold
1	Eagles	Hotel California	42,000,000
2	Led Zeppelin	Led Zeppelin IV	37,000,000
3	Shania Twain	Come On Over	40,000,000
4	Fleetwood Mac	Rumours	40,000,000
5	AC/DC	Back in Black	50,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Michael Jackson	Thriller	70,000,000
8	Pink Floyd	The Dark Side of the Moon	45,000,000
9	Whitney Houston	The Bodyguard	45,000,000
10	Eagles	Their Greatest Hits (1971-1975)	44,000,000

Estas são as vendas dos dez álbuns mais vendidos da história. Como você pode ver, os álbuns não estão classificados. Isso é o que faremos aqui: classificá-los do melhor para o pior, usando funções de janela.

Se você usar ROW_NUMBER(), a consulta será parecida com esta:

SELECT 
  ROW_NUMBER() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

A sintaxe é simples. Primeiro, você escolhe a função de janela. Em seguida, você usa a cláusula obrigatória OVER() que sinaliza que é uma função de janela. Em ORDER BY, você ordena os dados de forma decrescente. Isto agora significa que os números das linhas serão atribuídos de acordo com as cópias vendidas da alta para a baixa.

É claro, liste todas as outras colunas necessárias e faça referência à tabela para obter a mesma saída:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
4	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
8	Fleetwood Mac	Rumours	40,000,000
9	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,000,000

Como você pode ver, os álbuns são classificados de um a dez. Dois álbuns venderam 45 milhões de cópias. No entanto, eles são classificados de forma diferente (terceiro e quarto) de acordo com critérios aleatórios. O mesmo acontece com três álbuns que venderam 40 milhões de cópias.

Se você usar RANK(), a sintaxe é a mesma, exceto que você usa uma função de janela diferente:

SELECT 
  RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

No entanto, a saída é diferente:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
5	Eagles	Their Greatest Hits (1971-1975)	44,000,000
6	Eagles	Hotel California	42,000,000
7	Shania Twain	Come On Over	40,000,000
7	Fleetwood Mac	Rumours	40,000,000
7	Bee Gees	Saturday Night Fever	40,000,000
10	Led Zeppelin	Led Zeppelin IV	37,000,000

Você pode ver que os álbuns empatados são classificados como terceiros (duas vezes). O próximo álbum não empatado é classificado como quinto. O mesmo acontece com os álbuns classificados em sétimo lugar.

Vamos ver o que acontece se usarmos DENSE_RANK():

SELECT 
  DENSE_RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Aqui está o resultado:

rank	artist	album	copies_sold
1	Michael Jackson	Thriller	70,000,000
2	AC/DC	Back in Black	50,000,000
3	Whitney Houston	The Bodyguard	45,000,000
3	Pink Floyd	The Dark Side of the Moon	45,000,000
4	Eagles	Their Greatest Hits (1971-1975)	44,000,000
5	Eagles	Hotel California	42,000,000
6	Shania Twain	Come On Over	40,000,000
6	Fleetwood Mac	Rumours	40,000,000
6	Bee Gees	Saturday Night Fever	40,000,000
7	Led Zeppelin	Led Zeppelin IV	37,000,000

Os primeiros álbuns empatados são classificados como terceiros, o que é o mesmo que no resultado anterior. Mas a diferença é que a próxima classificação sem vínculos é a quarta - o que significa que a classificação não é ignorada.

Os outros três álbuns empatados são agora classificados como sexto, e não sétimo como antes. Além disso, a posição mais alta é a sétima, e não a décima.

Como você pode ver, cada método retorna resultados diferentes. Você deve usar o que melhor se adequa aos seus dados e ao que você deseja alcançar com a classificação. Para saber mais, leia o artigo sobre linhas de ranking em SQL.

21 Qual é a diferença entre RANK() e DENSE_RANK()?

Já abordamos a diferença na pergunta anterior. Você a viu em um exemplo prático, e agora vamos formulá-la para responder a esta pergunta.

RANK() Atribui a mesma classificação a linhas com os mesmos valores. Quando chega à seguinte fileira não amarrada, ela salta a classificação pelo número de fileiras empatadas.

DENSE_RANK() também dá a mesma classificação aos valores empatados. Entretanto, a classificação não é ignorada quando alcança a seguinte linha não amarrada. Em outras palavras, DENSE_RANK() classifica os dados seqüencialmente.

Mais detalhes são explicados neste artigo sobre as diferenças RANK() e DENSE_RANK().

22. encontrar as linhas Top n em SQL usando uma função de janela e um CTE.

Esta é uma questão comum e pode ser resolvida de várias maneiras. Usaremos a função de janela em um CTE para retornar o resultado desejado.

Os dados disponíveis são armazenados na função salary mesa:

id	first_name	last_name	salary	department
1	Tim	Thompson	10,524.74	Sales
2	Martina	Hrabal	7,895.14	Accounting
3	Susan	Truman	15,478.69	Sales
4	Ciro	Conte	8,794.41	Accounting
5	Jorge	De Lucia	7,489.15	Sales
6	Carmen	Lopez	10,479.15	Accounting
7	Catherine	Molnar	8,794.89	Sales
8	Richard	Buchanan	12,487.69	Accounting
9	Mark	Wong	9,784.19	Sales
10	Silvia	Karelias	9,748.64	Accounting

A tarefa aqui é devolver os três funcionários mais bem pagos de cada departamento, juntamente com seu salário e departamento.

A abordagem é a seguinte:

WITH ranking AS (
  SELECT 
    first_name,
    last_name,
    salary,
    department,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM salary
)

SELECT *
FROM ranking
WHERE salary_rank <= 3
ORDER BY department, salary_rank;

A primeira parte do código é uma Expressão de Tabela Comum, ou CTE. Ela é iniciada usando a palavra-chave WITH. O CTE é nomeado ranking. Após a palavra-chave AS, escrevemos a definição do CTE como uma declaração SELECT entre parênteses.

Depois de selecionar todas as colunas necessárias vem a classificação, usamos a função DENSE_RANK(). Você pode usar qualquer outra função de janela de ranking, se desejar.

A sintaxe é familiar. Para obter os rankings por departamento, precisamos dividir os dados por essa coluna. Além disso, queremos classificar os salários de alto para baixo. Em outras palavras, os dados na partição precisam ser organizados por salário em ordem decrescente.

A segunda declaração SELECT (ou seja, a consulta externa) seleciona todas as colunas do CTE e estabelece a condição na cláusula WHERE para filtrar somente os três salários mais altos pelo departamento. Finalmente, a saída é ordenada por departamento e por ordem de salário.

O resultado é o seguinte:

first_name	last_name	salary	department	salary_rank
Richard	Buchanan	12,487.69	Accounting	1
Carmen	Lopez	10,479.15	Accounting	2
Silvia	Karelias	9,748.64	Accounting	3
Susan	Truman	15,478.69	Sales	1
Tim	Thompson	10,524.74	Sales	2
Mark	Wong	9,784.19	Sales	3

23. calcular a diferença entre duas fileiras (Delta) usando Funções de Janela (Window Functions) em SQL

Este problema é resolvido da forma mais elegante usando a função de janela LAG(). Lembre-se, esta é uma função que acessa o valor da linha anterior.

Os dados do exemplo podem ser encontrados na tabela revenue:

id	actual_revenue	period
1	8,748,441.22	2022_07
2	10,487,444.59	2022_08
3	7,481,457.15	2022_09
4	7,497,441.89	2022_10
5	8,697,415.36	2022_11
6	12,497,441.56	2022_12

Você precisa mostrar a receita real, período de tempo e diferença mensal (delta) entre o mês real e o mês anterior.

Veja como fazer isso.

SELECT 
  actual_revenue,
  actual_revenue - LAG(actual_revenue) OVER (ORDER BY period ASC) AS monthly_revenue_change,	 
 period
FROM revenue
ORDER BY period;

Um delta é calculado pela subtração do mês anterior do mês real. Isso é exatamente o que esta consulta faz! Para obter a receita do mês anterior, a função LAG() vem a calhar. A coluna actual_revenue é o argumento da função, já que queremos acessar os dados da receita da linha anterior. Como em toda função de janela, há uma cláusula OVER(). Nela, ordenamos os dados por período de forma ascendente porque é lógico calcular o delta cronologicamente.

Esta é a saída da consulta:

actual_revenue	monthly_revenue_change	period
8,748,441.22	NULL	2022_07
10,487,444.59	1,739,003.37	2022_08
7,481,457.15	-3,005,987.44	2022_09
7,497,441.89	15,984.74	2022_10
8,697,415.36	1,199,973.47	2022_11
12,497,441.56	3,800,026.20	2022_12

A primeira mostra a mudança de receita como NULL. Isto é esperado porque não há um mês anterior para deduzir. Em 2022_08, houve um aumento da receita de 1.739.003,37 = receita mensal real - a receita do mês anterior = 10.487.444,59 - 8.748.441,22. A mesma lógica se aplica a todos os outros resultados.

Você pode encontrar exemplos similares no artigo sobre o cálculo da diferença entre duas linhas em SQL.

24. Use Funções de Janela (Window Functions) em SQL para calcular um total em execução

Um total em execução ou cumulativo é uma soma de uma seqüência numérica. O total em execução é atualizado cada vez que um novo valor é adicionado à seqüência. Pense na receita mensal: a receita total do mês atual incluirá a soma da receita do mês atual e de todos os meses anteriores.

A função de janela que é perfeita para calcular um total em execução (soma acumulada) é SUM().

Vamos mostrar a abordagem sobre os mesmos dados que na pergunta anterior. O objetivo é calcular a receita acumulada para todos os meses disponíveis em 2022.

Aqui está a solução:

SELECT 
  actual_revenue,
  SUM(actual_revenue) OVER (ORDER BY period ASC) AS cumulative_revenue,
  period
FROM revenue;

A soma cumulativa é a soma da receita real do mês e a soma de toda a receita dos meses anteriores. A função da janela SUM() aplica esta lógica. O argumento da função é a receita real, porque é isso que estamos somando. Para que ela totalize toda a receita anterior e a receita atual, ordenar os dados de forma ascendente por período. Novamente, é lógico calcular um total cumulativo do primeiro ao último mês.

Isto é o que o código retorna:

actual_revenue	cumulative_revenue	period
8,748,441.22	8,748,441.22	2022_07
10,487,444.59	19,235,885.81	2022_08
7,481,457.15	26,717,342.96	2022_09
7,497,441.89	34,214,784.85	2022_10
8,697,415.36	42,912,200.21	2022_11
12,497,441.56	55,409,641.77	2022_12

A receita acumulada na primeira linha é a mesma que a receita real. Para a segunda linha, o acumulado é 19.235.885,81 = 8.748.441,22 + 10.487.444,59. Em setembro, o cumulativo é 26.717.342,96 = 8.748.441,22 + 10.487.444,59 + 7.481.457,15.

A mesma lógica se aplica ao restante da tabela.

Você pode aprender mais sobre o total em execução e como calculá-lo aqui.

25. encontrar uma média móvel usando Funções de Janela (Window Functions) em SQL

Uma média móvel é usada quando se analisa uma série. Você pode encontrá-la sob outros nomes, tais como média móvel, média móvel ou média em execução. É uma média do valor atual e do número definido de valores anteriores. Por exemplo, uma média móvel de 7 dias é a média do dia atual e dos seis dias anteriores.

Para mostrar como calculá-la, usaremos o eur_usd_rate mesa:

id	exchange_rate	date
1	1.0666	2022-12-30
2	1.0683	2023-01-02
3	1.0545	2023-01-03
4	1.0599	2023-01-04
5	1.0601	2023-01-05
6	1.0500	2023-01-06
6	1.0696	2023-01-09
7	1.0723	2023-01-10
8	1.0747	2023-01-11
9	1.0772	2023-01-12
10	1.0814	2023-01-13

Vamos calcular a média móvel de 3 dias da seguinte forma:

SELECT 
  exchange_rate,
  AVG(exchange_rate) OVER (ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS eur_usd_moving_average,
  date
FROM eur_usd_rate;

Usamos a função da janela AVG() na coluna exchange_rate. Na cláusula OVER(), os dados são ordenados por data, em ordem ascendente. Agora vem a parte importante! Lembre-se, precisamos de uma média móvel de 3 dias envolvendo a linha atual e duas linhas anteriores. Especificamos isso na cláusula BETWEEN: dizemos à função para incluir duas linhas anteriores e a linha atual.

Vamos dar uma olhada no resultado:

exchange_rate	eur_usd_moving_average	date
1.0666	1.0666	2022-12-30
1.0683	1.0675	2023-01-02
1.0545	1.0631	2023-01-03
1.0599	1.0609	2023-01-04
1.0601	1.0582	2023-01-05
1.0500	1.0567	2023-01-06
1.0696	1.0599	2023-01-09
1.0723	1.0640	2023-01-10
1.0747	1.0722	2023-01-11
1.0772	1.0747	2023-01-12
1.0814	1.0778	2023-01-13

A média móvel da primeira data é a mesma que a taxa de câmbio porque: 1.0666/1 = 1.0666. Para 2023-01-02, é calculado assim: (1,0666 + 1,0683)/2 = 1,0675.

Em 2023-01-03, finalmente teremos três datas: (1,0666 + 1,0683 + 1,0545)/3 = 1,0631. Esta lógica se aplica a todas as outras datas.

Mais exemplos podem ser encontrados neste artigo sobre o cálculo de médias móveis em SQL.

26 Qual é a diferença entre ROWS e RANGE?

Tanto ROWS quanto RANGE são cláusulas utilizadas para definir um quadro de janela. Elas limitam o intervalo de dados utilizados em uma função de janela dentro de uma partição.

A cláusula ROWS limita as linhas. É usada para especificar um número fixo de linhas que precedem e seguem a linha atual. O valor das linhas não é levado em conta.

A cláusula RANGE limita o intervalo de dados de forma lógica. Em outras palavras, ela limita os dados analisando os valores das linhas anterior e seguinte em relação à linha atual. Ela não leva em conta o número de linhas.

Como utilizá-las na prática? Leia nosso artigo sobre ROWS e RANGE para mais detalhes.

27 Use uma consulta recursiva para encontrar todos os funcionários sob um determinado gerente.

Uma consulta recursiva é um tipo especial de CTE que se refere a si mesmo até chegar ao final da recursividade. É ideal para consulta de dados gráficos ou estrutura hierárquica.

Um exemplo desta última é a estrutura organizacional da empresa, mostrada na seção company_organization mesa:

employee_id	first_name	last_name	manager_id
5529	Jack	Simmons	5125
5238	Maria	Popovich	5329
5329	Dan	Jacobsson	5125
5009	Simone	Gudbois	5329
5125	Albert	Koch	NULL
5500	Jackie	Carlin	5529
5118	Steve	Nicks	5952
5012	Bonnie	Presley	5952
5952	Harry	Raitt	5529
5444	Sean	Elsam	5329

Esta tabela mostra todos os funcionários e a identificação de seu gerente direto.

A tarefa aqui é utilizar a recorrência e devolver todos os subordinados diretos e indiretos de Jack Simmons. Além disso, vamos acrescentar uma coluna que pode ser usada para distinguir os diferentes níveis organizacionais. Aqui está o código:

WITH RECURSIVE subordinates AS (
	SELECT
  employee_id,
	  first_name,
	  last_name,
	  manager_id,
	  0 AS level
	FROM company_organization
	WHERE employee_id= 5529

	UNION ALL

	SELECT
 	  co.employee_id, 
	  co.first_name,
	  co.last_name,
	  co.manager_id,
	  level + 1
	FROM company_organization co 
JOIN subordinates s 
ON co.manager_id = s.employee_id
)

SELECT
  s.employee_id,
  s.first_name AS employee_first_name,
  s.last_name AS employee_last_name,
  co.employee_id AS direct_manager_id,
  co.first_name AS direct_manager_first_name,
  co.last_name AS direct_manager_last_name,
  s.level
FROM subordinates s 
JOIN company_organization co 
ON s.manager_id = co.employee_id
ORDER BY level;

Iniciamos a recursividade usando WITH RECURSIVE. (Se você estiver trabalhando no MS SQL Server, use apenas WITH.)

O primeiro SELECT em um CTE é chamado de membro âncora. Nele, referenciamos o conjunto de dados e selecionamos todas as colunas necessárias. Além disso, criamos uma nova coluna com o valor zero e filtramos os dados na cláusula WHERE. Por que usar esta condição exata em WHERE? Porque o ID de funcionário de Jack Simmons é 5529, e queremos mostrar a ele e seus subordinados.

Depois vem o UNION ALL, que combina os resultados da consulta de âncora e da consulta recursiva, ou seja, a segunda declaração SELECT.

Queremos que a recursividade percorra todo o caminho através da estrutura organizacional. Na consulta recursiva, juntamos o CTE com o company_organization mesa. Listamos novamente todas as colunas necessárias a partir desta última tabela. Além disso, queremos acrescentar um nível organizacional a cada repetição.

Finalmente, chegamos à consulta que utiliza o CTE. Esta consulta serve para obter dados tanto do CTE quanto do company_organization mesa. Utilizamos o CTE para mostrar os dados dos funcionários. A outra tabela é usada para mostrar as informações do gerente direto.

Executando o código, você obterá este resultado:

employee_id	employee_first_name	employee_last_name	direct_manager_id	direct_manager_first_name	direct_manager_last_name	level
5529	Jack	Simmons	5125	Albert	Koch	0
5952	Harry	Raitt	5529	Jack	Simmons	1
5500	Jackie	Carlin	5529	Jack	Simmons	1
5012	Bonnie	Presley	5952	Harry	Raitt	2
5118	Steve	Nicks	5952	Harry	Raitt	2

A tabela acima mostra que o gerente direto de Jack Simmons é Albert Koch. Diretamente sob Simmons, há Harry Raitt e Jackie Carlin. Os subordinados indiretos são Bonnie Presley e Steve Nicks. O gerente direto deles é Harry Raitt.

Algumas outras variações desta tarefa podem ser encontradas no artigo sobre CTEs recursivas.

Você pode aprender mais sobre funções de janela neste artigo dedicado a perguntas sobre funções de janela SQL.

Você está pronto para Ace SQL Job Interview Questions?

Escrever este guia não foi fácil. Mas compensa quando pensamos em facilitar sua entrevista de trabalho SQL avançada.

É claro, estas não são todas as perguntas que você poderia obter na entrevista. Entretanto, acreditamos que esta seleção lhe proporcionará uma base firme dos mais importantes conceitos avançados de SQL. Este guia também é suficientemente curto para que você possa analisá-lo rapidamente antes da entrevista e refrescar sua memória.

Para mais atualizadores sobre tópicos avançados de SQL, tente nosso Funções de Janela (Window Functions) em SQL curso ou SQL Avançado pista.