Top 5 Perguntas de Entrevista SQL CTE

Aqui estão cinco perguntas (e soluções!) sobre CTEs que você (provavelmente) será perguntado em uma entrevista.

Não estou dizendo que você receberá estas perguntas do CTE SQL em cada entrevista de emprego. Mas quando você as fizer, elas provavelmente estarão na linha das cinco que estou prestes a mostrar aqui.

Além das perguntas teóricas sobre CTEs, não há muitas variações nos cenários de CTE avaliados pelos entrevistadores. Analise estes cinco exemplos, e você terá uma boa base para realizar sua entrevista!

Para isso, você tem que saber o que são expressões comuns de tabela SQL e como funciona sua sintaxe.

Pronto para dar uma olhada nas perguntas? Vamos lá!

Tabela para as perguntas 1, 2, e 3

Para o primeiro conjunto de perguntas, usaremos a tabela employees. Aqui está o que parece:

id	first_name	last_name	department	salary	manager_id
1	Angelika	Voules	Marketing	5,293.74	2
2	Rozelle	Swynley	Marketing	8,295.08	18
3	Warren	Willey	Engineering	9,126.72	19
4	Lynelle	Whiten	Management Board	10,716.15	NULL
5	Consolata	Roman	Legal	8,456.06	4
6	Hoebart	Baldock	Research and Development	4,817.34	20
7	Starlene	Watkiss	Accounting	6,541.48	4
8	Barde	Ribbens	Marketing	4,852.87	2
9	Lorne	Philipsen	Engineering	7,235.59	3
10	Pedro	Naldrett	Research and Development	5,471.62	20
11	Brina	Dillinger	Marketing	6,512.17	2
12	Verile	Sonley	Research and Development	4,574.41	20
13	Noble	Geerling	Research and Development	8,391.18	20
14	Garey	MacAdam	Accounting	3,829.88	7
15	Theo	Sorrell	Engineering	6,441.67	3
16	Erminie	Gelling	Research and Development	8,590.70	20
17	Loralie	Koop	Accounting	5,248.46	7
18	Cal	Andrey	Management Board	11,258.82	NULL
19	Quincey	Gamell	Management Board	11,366.52	NULL
20	Janith	McGiffie	Research and Development	7,428.83	19

O que os dados lhe dizem? A primeira linha, por exemplo, diz que Angelika Voules trabalha em Marketing, e seu salário é de 5.293,74. Seu chefe tem uma identificação de gerente de 2; procure na coluna id, e você vê que o chefe de Angelika Voules é Rozelle Swynley.

Há três filas com valores NULL na coluna manager_id:

id	first_name	last_name	department	salary	manager_id
4	Lynelle	Whiten	Management Board	10,716.15	NULL
18	Cal	Andrey	Management Board	11,258.82	NULL
19	Quincey	Gamell	Management Board	11,366.52	NULL

Significa que estes três employees não têm gerentes. Isto faz sentido, uma vez que todos os três estão no conselho de administração.

Agora, vamos ver as perguntas da entrevista.

Pergunta da Entrevista 1: Encontre o Salário Médio por Departamento

Usando a tabela de funcionários, mostrar todos os funcionários, seus departamentos, salários e o salário médio em seu respectivo departamento. Encomende o resultado por departamento.

Consulta de soluções

WITH avg_salary AS (
		SELECT	AVG(salary) AS average_salary,
				department
		FROM employees
		GROUP BY department)
		
SELECT	e.first_name,
		e.last_name,
		e.department,
		e.salary,
		avgs.average_salary	
FROM employees e
JOIN avg_salary avgs
ON e.department = avgs.department
ORDER BY department;

Explicação da consulta da solução

Esta consulta usa um CTE chamado avg_salary para calcular o salário médio por departamento. A declaração SELECT no CTE agrupa as linhas por departamento e usa a função agregada AVG() para calcular a média de cada departamento.

Uma vez que temos isso, combinamos isso com outras colunas da tabela employees para completar a resposta à pergunta da entrevista. Para isso, nos juntamos à mesa employees com o CTE, como faríamos com duas mesas quaisquer. Selecionamos as colunas first_name, last_name, department, e salary da tabela employees, e a coluna average_salary do CTE. Por conveniência, usamos pseudônimos para a tabela e para o CTE. Finalmente, encomendamos o resultado por departamento.

A Tabela de Resultados

Eis como é o resultado:

first_name	last_name	department	salary	average_salary
Garey	MacAdam	Accounting	3,829.88	5,206.61
Loralie	Koop	Accounting	5,248.46	5,206.61
Starlene	Watkiss	Accounting	6,541.48	5,206.61
Warren	Willey	Engineering	9,126.72	7,601.33
Lorne	Philipsen	Engineering	7,235.59	7,601.33
Theo	Sorrell	Engineering	6,441.67	7,601.33
Consolata	Roman	Legal	8,456.06	8,456.06
Cal	Andrey	Management Board	11,258.82	11,113.83
Quincey	Gamell	Management Board	11,366.52	11,113.83
Lynelle	Whiten	Management Board	10,716.15	11,113.83
Angelika	Voules	Marketing	5,293.74	6,238.47
Rozelle	Swynley	Marketing	8,295.08	6,238.47
Barde	Ribbens	Marketing	4,852.87	6,238.47
Brina	Dillinger	Marketing	6,512.17	6,238.47
Noble	Geerling	Research and Development	8,391.18	6,545.68
Erminie	Gelling	Research and Development	8,590.70	6,545.68
Verile	Sonley	Research and Development	4,574.41	6,545.68
Pedro	Naldrett	Research and Development	5,471.62	6,545.68
Hoebart	Baldock	Research and Development	4,817.34	6,545.68
Janith	McGiffie	Research and Development	7,428.83	6,545.68

A explicação do resultado

Esta tabela nos diz que, por exemplo, o salário de Starlene Watkiss é de 6.541,48, e o salário médio em seu departamento (Contabilidade) é de 5.206,61.

first_name	last_name	department	salary	average_salary
Starlene	Watkiss	Accounting	6,541.48	5,206.61

Você pode interpretar as linhas restantes no resultado da mesma forma.

Entrevista Pergunta 2: Encontre o salário mais alto por departamento

Encontre o funcionário com o salário mais alto em cada departamento. Mostrar seu primeiro e último nome, salários e departamentos.

Consulta de soluções

WITH highest_salary AS (
		SELECT	first_name,
				last_name,
				department,
				salary,
				RANK () OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
		FROM employees)

SELECT	first_name,
		last_name,
		salary,
		department
FROM highest_salary
WHERE salary_rank = 1;

Explicação da consulta da solução

Desta vez, o CTE é nomeado highest_salary. Utilizamo-lo para classificar os funcionários dentro de cada departamento por salário. Como? Usando a função de janela RANK().

Dividimos os dados pelo departamento de colunas. Isto significa que classificamos os salários somente dentro de cada departamento, não em toda a empresa. Os dados são classificados de acordo com o salário da coluna em ordem decrescente porque queremos que o mais alto salary do departamento tenha a classificação de 1.

Também selecionamos várias colunas da tabela employees no mesmo CTE. Precisamos dos dados deste CTE na próxima declaração SELECT.

E, nessa declaração SELECT, selecionamos as colunas necessárias para responder à pergunta da entrevista. Todas as colunas são do CTE. Filtramos o resultado usando uma cláusula WHERE para obter apenas as linhas onde o nível salarial é 1, ou seja, as linhas com o salário mais alto.

A Tabela de Resultados

first_name	last_name	salary	department
Starlene	Watkiss	6,541.48	Accounting
Warren	Willey	9,126.72	Engineering
Consolata	Roman	8,456.06	Legal
Quincey	Gamell	11,366.52	Management Board
Rozelle	Swynley	8,295.08	Marketing
Erminie	Gelling	8,590.70	Research and Development

O Resultado Explicação

Não é muito difícil interpretar o resultado. Tomemos este funcionário como exemplo:

first_name	last_name	salary	department
Consolata	Roman	8,456.06	Legal

Os dados mostrados nos dizem que a Consolata Roman tem o salário mais alto do Departamento Jurídico em 8.456,06.

Passemos à terceira pergunta da entrevista SQL CTE.

Entrevista Pergunta 3: Encontre todos os funcionários sob um gerente específico

Encontre todos os funcionários que trabalham direta ou indiretamente sob o funcionário cujo ID é 18.

Consulta de soluções

WITH RECURSIVE subordinates AS (
		SELECT	id,
				first_name,
				last_name,
				manager_id
		FROM employees
		WHERE id = 18

	UNION

		SELECT	e.id,
				e.first_name,
				e.last_name,
				e.manager_id
		FROM employees e
JOIN subordinates s
ON e.manager_id = s.id
)
		
SELECT *
FROM subordinates
WHERE id != 18;

Explicação da consulta da solução

Neste exemplo, não se trata apenas de um CTE regular - ao contrário, usamos um CTE recorrente para obter o resultado. A diferença na sintaxe é WITH RECURSIVE, em vez de apenas COM. O CTE é nomeado subordinatese depois vem a declaração SELECT tal como nos CTEs não-recursivos.

Esta declaração seleciona certas colunas da tabela employees mas somente para o funcionário cuja identificação é 18. Então, usamos o operador UNION para vincular o resultado desta declaração SELECT com o resultado de outra declaração SELECT. Para isso, ambas as declarações SELECT precisam ter as mesmas colunas.

A segunda declaração SELECT no CTE seleciona colunas onde manager_id (da tabela employees) é igual ao id (do CTE).

Obtemos todos os dados para o funcionário cuja identificação é 18. Em seguida, encontramos os subordinados diretos, e por recorrência, encontramos os subordinados dos subordinados, até descermos toda a hierarquia da organização.

Em seguida, obtemos as colunas dos subordinados do CTE e retiramos o funcionário cujo ID é 18. É isso aí!

A Tabela de Resultados

id	first_name	last_name	manager_id
2	Rozelle	Swynley	18
1	Angelika	Voules	2
8	Barde	Ribbens	2
11	Brina	Dillinger	2

O Resultado Explicação

O chefe direto de Rozelle Swynley é o funcionário cujo gerente tem 18 anos de identificação. Mas Rozelle tem subordinados, também. Eles são Angelika Voules, Barde Ribbens, e Brina Dillinger. Sabemos disso porque a tabela mostra que a identificação do gerente deles é 2, que é a identificação de Rozelle Swynley. Eles são os subordinados diretos de Rozelle Swynley; eles também são subordinados indiretos do funcionário cujo ID é 18. O funcionário cujo ID é 18 é Cal Andrey, que não é mostrado no resultado porque o filtramos na cláusula WHERE.

Tabelas utilizadas para as perguntas 4 e 5

Para as duas últimas perguntas da entrevista, utilizaremos as tabelas customers e orders.

A tabela customers parece ser assim:

id	first_name	last_name
1	Simon	Paulson
2	Dylan	Bobson
3	Reb	Mackennack

O orders mesa é um pouco maior:

id	order_date	order_amount	customer_id
1	2021-10-01	42.12	3
2	2021-10-01	415.63	1
3	2021-10-02	84.99	2
4	2021-10-02	28.96	3
5	2021-10-02	54.31	1
6	2021-10-03	74.26	1
7	2021-10-03	77.77	2
8	2021-10-03	55.70	3
9	2021-10-04	16.94	3
10	2021-10-04	51.44	1
11	2021-10-05	41.58	3
12	2021-10-06	95.00	1

Esta segunda tabela contém dados sobre os pedidos feitos. A coluna customer_id é uma chave estrangeira para a chave primária da primeira tabela, para que possamos identificar qual cliente fez qual pedido. Tomemos a primeira linha como exemplo:

id	order_date	order_amount	customer_id
1	2021-10-01	42.12	3

É um pedido feito em 1 de outubro de 2021. O valor da mercadoria encomendada é 42,12, e o pedido foi feito pelo cliente cuja identificação é 3. Da tabela customersVejam, é Reb Mackennack.

Vejamos as tarefas a serem resolvidas usando estas tabelas.

Entrevista Questão 4: Encontre o número médio de pedidos

Esta pergunta de entrevista pede que você use uma expressão de tabela comum SQL para encontrar o número médio de pedidos por cliente.

Consulta de soluções

WITH orders_count AS (
		SELECT	customer_id,
				COUNT(*) AS no_of_orders
		FROM orders
		GROUP BY customer_id)

SELECT	AVG(no_of_orders) AS avg_no_of_orders
FROM orders_count;

Explicação da consulta da solução

Para obter o resultado, primeiro você conta o número de pedidos por cliente. Fazemos isto usando o CTE orders_count e a função COUNT() nela contida. Esta função conta o número de filas na tabela orders. Como não estamos interessados na contagem total de pedidos, mas sim no número de pedidos por cliente, eu agrupo o resultado pela coluna customer_id.

Agora que temos o número de pedidos, é fácil calcular o número médio. Basta usar a função AVG() na declaração SELECT, e você terá a resposta para a pergunta da entrevista.

A tabela de resultados

avg_no_of_orders
4

O Resultado Explicação

Uma mesa muito pequena requer uma explicação muito breve. A tabela acima mostra simplesmente que o número médio de pedidos por cliente é de quatro.

Entrevista Pergunta 5: Encontre o número de dias consecutivos com o pedido

Neste exemplo do CTE, é preciso calcular quantos dias seguidos cada cliente faz um pedido. Basicamente, é preciso calcular a duração de uma série contígua sem lacunas. Observe que todos os pedidos são feitos dentro do mesmo mês.

Consulta de soluções

WITH groupings_by_date AS (
	SELECT	c.id,
			c.first_name,
			c.last_name,
			RANK() OVER (PARTITION BY c.id ORDER BY o.order_date) AS row_number,
			o.order_date,
			EXTRACT(DAY FROM o.order_date) - RANK() OVER (PARTITION BY c.id ORDER BY o.order_date) AS date_group
FROM customers c
JOIN orders o
ON c.id = o.customer_id
)

SELECT	id,
		first_name,
		last_name,
		COUNT(*) AS orders_in_row
FROM groupings_by_date
GROUP BY id, first_name, last_name, date_group;

Explicação da consulta da solução

Esta consulta bastante demorada começa com um CTE. Ela seleciona certas colunas das tabelas customers e orders. Há uma função de janela RANK(), como na pergunta 2. Desta vez, usamos esta função para alocar um número de linha para cada pedido dentro do mesmo cliente. Por esse motivo, dividimos os dados pela coluna id. A classificação é feita de acordo com a data da ordem (queremos que a classificação seja seqüencial).

A outra função utilizada aqui é EXTRACT(). Sua finalidade é extrair a parte do dia da data do pedido para que possamos deduzir o número da fila dela.

Por que estamos fazendo isto? Estamos simplesmente dando a um grupo de pedidos consecutivos um date_group comum. Se você executar apenas este CTE, as duas primeiras filas do resultado se parecem com estas:

id	first_name	last_name	row_number	order_date	date_group
1	Simon	Paulson	1	2021-10-01	0
1	Simon	Paulson	2	2021-10-02	0

Como você pode ver, Simon Paulson fez pedidos tanto em 1º de outubro quanto em 2 de outubro de 2021. Como eles foram colocados dois dias seguidos, eles pertencem ao mesmo date_group.

Como podemos conseguir isto? É o dia extraído do order_date menos o row_number.

Na primeira linha, o dia da data '2021-10-01' é 1. O número da linha também é 1. Portanto, é 1-1 = 0, que também é o valor no date_group. A segunda linha é 2-2 = 0, que é o mesmo date_group como acima.

O valor específico de date_group realmente não importa! Importa apenas que os dias consecutivos tenham o mesmo valor de date_group. Este é um pequeno truque para calcular a duração de uma série. Funciona porque, se os pedidos são feitos todos os dias, então a diferença entre o número de dias no order_date e o valor no row_number é sempre a mesma para o mesmo cliente.

Note que estes dados não aparecem em nenhum lugar. Estou apenas mostrando-os para uma explicação mais fácil do que o CTE faz aqui.

É importante mencionar que este truque só funciona se seus dados estiverem todos dentro do mesmo mês. Se a série, digamos, começa em 2021-10-31 e vai até 2021-11-01, o truque não funciona; estes dois dias, embora sejam um após o outro, não pertencerão ao mesmo date_group. Portanto, você tem que entender seus dados antes de decidir usar este truque para obter a duração de uma série.

Uma vez que você tenha o CTE, use-o como uma tabela na declaração SELECT. Vou usar a função COUNT() para obter o número dos pedidos em uma linha. Quero ver o resultado para cada cliente, então agrupo os dados por id, first_name, e last_name. Também vou agrupá-los pela coluna date_group, para que os pedidos feitos pelo mesmo cliente sejam separados se houver uma lacuna entre eles.

A tabela de resultados

id	first_name	last_name	orders_in_row
1	Simon	Paulson	4
3	Reb	Mackennack	5
2	Dylan	Bobson	2
1	Simon	Paulson	1

O Resultado Explicação

Esta tabela mostra que Simon Paulson fez quatro pedidos seguidos. Reb Mackennack fez isso cinco dias seguidos, enquanto Dylan Bobson tem apenas dois pedidos seguidos. Finalmente, há um pedido adicional feito por Simon Paulson.

Quando os CTEs são úteis?

Como você vê nestes exemplos, os CTEs são muito úteis quando você tem que calcular algo em pelo menos duas etapas. Você usa um CTE para preparar o cálculo preliminar, e então você simplesmente usa a declaração SELECT, faz referência ao CTE, e executa outro nível de cálculo.

Em cálculos mais complexos, você também pode usar vários CTEs ou mesmo CTEs aninhados. É semelhante a subconsultas, mas os CTEs tornam o código mais legível e mais fácil de dividir o cálculo em etapas. E com os CTEs aninhados, você pode escrever uma consulta e referenciá-la imediatamente como em qualquer outra tabela.

Alguns destes exemplos mostram que você também pode usar as funções de agregado e janela. Isto torna os CTEs mais poderosos.

Além disso, os CTEs são ótimos para dados hierárquicos como estruturas organizacionais e para atravessar um gráfico. Entretanto, você precisa escrever um CTE recursivo nestes casos, como fiz na pergunta 3.

Finalmente, se você quiser escrever consultas recursivas e usá-las, o primeiro passo é aprender os CTEs. Se você precisar de ajuda, aqui está um guia sobre como abordar o aprendizado de CTEs.

Como você pode ver, os CTEs têm muitos benefícios e usos práticos. Estes exemplos mostram apenas um vislumbre de soluções para problemas práticos que levam muito mais tempo se não fossem os CTEs. Você quer mais? Não há problema, dê uma olhada em alguns outros usos práticos dos CTEs.

Quando você aprender os CTEs e começar a usá-los, tente pensar no problema primeiro. Desdobre as etapas de cálculo e depois traduza esta lógica em um código de expressão comum de tabela SQL. Os CTEs são adequados para isso.

Como são uma versão de tabelas temporárias, seja claro ao nomeá-las: use um nome que diga imediatamente o que o CTE faz e seja consistente com as convenções de nomeação. Geralmente, você quer seguir as melhores práticas do CTE, o que torna sua (e outras!) leitura de código muito mais fácil.

Quer saber mais sobre as Expressões da Tabela Comum SQL?

Se você está se preparando para uma entrevista de emprego SQL, quer ser melhor em seu emprego atual, ou simplesmente quer aprender algo novo em seu tempo livre, dê uma olhada em nosso Consultas Recursivas curso.

Lá, você encontra uma abordagem sistemática dos CTEs, explicando a teoria por trás deles, mostrando-lhe a sintaxe e dando-lhe mais alguns exemplos para praticar. O curso inteiro faz parte do SQL Avançado track, que cobre dois tópicos adicionais: funções de janela (que usamos neste artigo) e as extensões GROUP BY. Boa sorte!