Las 5 mejores preguntas de la entrevista de SQL CTE

Aquí tienes cinco preguntas (¡y soluciones!) sobre CTEs que (probablemente) te harán en una entrevista.

No digo que te hagan estas preguntas sobre CTEs SQL en todas las entrevistas de trabajo. Pero cuando lo hagas, probablemente sean del tipo de las cinco que voy a mostrar aquí.

Aparte de las preguntas teóricas sobre CTEs, no hay muchas variaciones en los escenarios de CTEs evaluados por los entrevistadores. Repasa estos cinco ejemplos y tendrás una buena base para superar tu entrevista.

Para ello, tienes que saber qué son las expresiones comunes de tabla SQL y cómo funciona su sintaxis.

¿Listo para echar un vistazo a las preguntas? Vamos allá.

Tabla para las preguntas 1, 2 y 3

Para la primera serie de preguntas, utilizaremos la tabla employees. Esto es lo 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

¿Qué te dicen los datos? La primera fila, por ejemplo, dice que Angelika Voules trabaja en Marketing, y su salario es de 5.293,74. Su jefe tiene un ID de gerente de 2; búsquelo en la columna id, y verá que el jefe de Angelika Voules es Rozelle Swynley.

Hay tres filas con valores NULL en la columna 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 estos tres employees no tienen administradores. Esto tiene sentido ya que los tres están en la junta directiva.

Ahora, veamos las preguntas de la entrevista.

Pregunta de la entrevista 1: Encuentre el salario medio por departamento

Utilizando la tabla empleados, muestre todos los empleados, sus departamentos, salarios y el salario medio en su respectivo departamento. Ordene el resultado por departamento.

Solución Consulta

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;

Solución Consulta Explicación

Esta consulta utiliza un CTE llamado avg_salary para calcular el salario medio por departamento. La sentencia SELECT del CTE agrupa las filas por departamento y utiliza la función de agregación AVG() para calcular la media de cada departamento.

Una vez que tenemos eso, lo combinamos con otras columnas de la tabla employees para completar la respuesta a la pregunta de la entrevista. Para ello, unimos la tabla employees con el CTE como haríamos con dos tablas cualquiera. Seleccionamos las columnas first_name, last_name, department, y salary de la tabla employeesy la columna average_salary del CTE. Por comodidad, utilizamos alias para la tabla y el CTE. Por último, ordenamos el resultado por departamento.

La tabla de resultados

Este es el aspecto del 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

Explicación del resultado

Esta tabla nos dice que, por ejemplo, el salario de Starlene Watkiss es de 6.541,48, y el salario medio de su departamento (Contabilidad) es de 5.206,61.

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

Puedes interpretar las demás filas del resultado de la misma manera.

Pregunta de la entrevista 2: Encuentre el salario más alto por departamento

Encuentre el empleado con el salario más alto en cada departamento. Indica sus nombres y apellidos, salarios y departamentos.

Solución Consulta

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;

Solución Explicación de la consulta

Esta vez, el CTE se llama highest_salary. Lo utilizamos para ordenar a los empleados dentro de cada departamento por su salario. ¿Cómo? Utilizando la función de ventana RANK().

Partimos los datos por la columna departamento. Esto significa que clasificamos los salarios sólo dentro de cada departamento, no en toda la empresa. Los datos se ordenan según la columna salario en orden descendente porque queremos que el más alto salary en el departamento tenga el rango de 1.

También seleccionamos varias columnas de la tabla employees en el mismo CTE. Necesitamos los datos de este CTE en la siguiente sentencia SELECT.

Y en esa sentencia SELECT, seleccionamos las columnas necesarias para responder a la pregunta de la entrevista. Todas las columnas son del CTE. Filtramos el resultado utilizando una cláusula WHERE para obtener sólo las filas en las que el rango del salario es 1, es decir, las filas con el salario más alto.

La tabla 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

Explicación del resultado

No es muy difícil interpretar el resultado. Tomemos este empleado como ejemplo:

first_name	last_name	salary	department
Consolata	Roman	8,456.06	Legal

Los datos mostrados nos dicen que Consolata Roman tiene el salario más alto del departamento jurídico, con 8.456,06.

Pasemos a la tercera pregunta de la entrevista SQL CTE.

Pregunta de entrevista 3: Encontrar todos los empleados bajo un gerente específico

Encuentre todos los empleados que trabajan directa o indirectamente bajo el empleado cuyo ID es 18.

Solución Consulta

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;

Solución Consulta Explicación

En este ejemplo, no se trata de un CTE normal, sino que utilizamos un CTE recursivo para obtener el resultado. La diferencia en la sintaxis es WITH RECURSIVE en lugar de simplemente WITH. El CTE se llama subordinatesy luego viene la sentencia SELECT como en los CTEs no recursivos.

Esta sentencia selecciona ciertas columnas de la tabla employees pero sólo para el empleado cuyo ID es 18. A continuación, utilizamos el operador UNION para enlazar el resultado de esta sentencia SELECT con el resultado de otra sentencia SELECT. Para ello, ambas sentencias SELECT deben tener las mismas columnas.

La segunda sentencia SELECT del CTE selecciona las columnas en las que manager_id (de la tabla employees) es igual a la id (de la CTE).

Obtenemos todos los datos del empleado cuyo ID es 18. Luego, encontramos los subordinados directos, y por recursión, encontramos los subordinados de los subordinados, hasta llegar a toda la jerarquía de la organización.

Luego obtenemos las columnas de los subordinados del CTE y eliminamos al empleado cuyo ID es 18. Y ya está.

La tabla 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

Explicación del resultado

El jefe directo de Rozelle Swynley es el empleado cuyo ID de gerente es 18. Pero Rozelle también tiene subordinados. Son Angelika Voules, Barde Ribbens y Brina Dillinger. Lo sabemos porque la tabla muestra que el ID de su gerente es el 2, que es el ID de Rozelle Swynley. Son los subordinados directos de Rozelle Swynley; también son subordinados indirectos del empleado cuyo ID es 18. El empleado cuyo ID es 18 es Cal Andrey, que no aparece en el resultado porque lo filtramos en la cláusula WHERE.

Tablas utilizadas para las preguntas 4 y 5

Para las dos últimas preguntas de la entrevista, utilizaremos las tablas customers y orders.

La tabla customers tiene el siguiente aspecto:

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

La tabla orders tabla es un poco más grande:

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 tabla contiene datos sobre los pedidos realizados. La columna customer_id es una clave ajena a la clave primaria de la primera tabla, por lo que podemos identificar qué cliente hizo qué pedido. Tomemos la primera fila como ejemplo:

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

Se trata de un pedido realizado el 1 de octubre de 2021. El valor de la mercancía pedida es de 42,12, y el pedido fue realizado por el cliente cuyo ID es 3. En la tabla customersse ve que es Reb Mackennack.

Veamos las tareas a resolver utilizando estas tablas.

Pregunta de la entrevista 4: Encontrar el número medio de pedidos

Esta pregunta de entrevista le pide que utilice una expresión de tabla común de SQL para encontrar el número medio de pedidos por cliente.

Solución Consulta

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;

Solución Consulta Explicación

Para obtener el resultado, primero se cuenta el número de pedidos por cliente. Para ello se utiliza el CTE orders_count y la función COUNT() que contiene. Esta función cuenta el número de filas de la tabla orders. Como no nos interesa el número total de pedidos sino el número de pedidos por cliente, agrupo el resultado por la columna customer_id.

Ahora que tenemos el número de pedidos, es fácil calcular el número medio. Simplemente utilice la función AVG() en la sentencia SELECT, y obtendrá la respuesta a la pregunta de la entrevista.

La tabla de resultados

avg_no_of_orders
4

La explicación del resultado

Una tabla muy pequeña requiere una explicación muy breve. La tabla anterior muestra simplemente que el número medio de pedidos por cliente es de cuatro.

Pregunta de la entrevista 5: Encontrar el número de días consecutivos con pedido

En este ejemplo de CTE, tiene que calcular cuántos días consecutivos realiza un pedido cada cliente. Básicamente le pide que calcule la longitud de una serie contigua sin huecos. Tenga en cuenta que todos los pedidos están dentro del mismo mes.

Solución Consulta

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;

Explicación de la consulta de la solución

Esta consulta, bastante larga, comienza con un CTE. Selecciona ciertas columnas de las tablas customers y orders. Hay una función de ventana RANK() como en la pregunta 2. Esta vez, utilizamos esta función para asignar un número de fila a cada pedido dentro del mismo cliente. Para ello, particionamos los datos por la columna id. La clasificación se realiza según la fecha del pedido (queremos que la clasificación sea secuencial).

La otra función que se utiliza aquí es EXTRACT(). Su objetivo es extraer la parte del día de la fecha del pedido para que podamos deducir el número de la fila.

¿Por qué hacemos esto? Simplemente estamos dando a un grupo de pedidos consecutivos un común date_group. Si se ejecuta sólo este CTE, las dos primeras filas del resultado tienen este aspecto:

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 puede ver, Simon Paulson realizó órdenes tanto el 1 de octubre como el 2 de octubre de 2021. Dado que se colocaron dos días seguidos, pertenecen al mismo date_group.

¿Cómo se obtiene esto? Es el día extraído del order_date menos el row_number.

En la primera fila, el día de la fecha '2021-10-01' es 1. El número de la fila también es 1. Por lo tanto, es 1-1 = 0, que es también el valor en el date_group. La segunda fila es 2-2 = 0, que es el mismo date_group que el anterior.

El valor específico de date_group no importa realmente. Sólo importa que los días consecutivos tengan el mismo valor de date_group. Este es un pequeño truco para calcular la longitud de una serie. Funciona porque, si los pedidos se realizan todos los días, entonces la diferencia entre el número de días en el order_date y el valor en el row_number es siempre la misma para el mismo cliente.

Ten en cuenta que estos datos no aparecen en ningún sitio. Sólo te lo muestro para facilitar la explicación de lo que hace el CTE aquí.

Es importante mencionar que este truco sólo funciona si sus datos están todos dentro del mismo mes. Si la serie, digamos, comienza el 2021-10-31 y llega hasta el 2021-11-01, el truco no funciona; estos dos días, aunque estén uno tras otro, no pertenecerán al mismo date_group. Por lo tanto, tienes que entender tus datos antes de decidirte a utilizar este truco para obtener la longitud de una serie.

Una vez que tengas el CTE, utilízalo como una tabla en la sentencia SELECT. Voy a utilizar la función COUNT() para obtener el número de los pedidos en una fila. Quiero ver el resultado de cada cliente, así que agrupo los datos por id, first_name, y last_name. También lo agrupo por la columna date_group, para que los pedidos realizados por el mismo cliente queden separados si hay un espacio entre ellos.

La tabla 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

Explicación del resultado

Esta tabla muestra que Simon Paulson ha realizado cuatro pedidos seguidos. Reb Mackennack lo ha hecho cinco días seguidos, mientras que Dylan Bobson sólo tiene dos pedidos seguidos. Por último, hay una orden adicional de Simon Paulson.

¿Cuándo son útiles los CTE?

Como se ve en estos ejemplos, las CTEs son muy útiles cuando se tiene que calcular algo en al menos dos pasos. Usted utiliza un CTE para preparar el cálculo preliminar, y luego simplemente utiliza la sentencia SELECT, hace referencia al CTE, y realiza otro nivel de cálculo.

En cálculos más complejos, también puedes utilizar varios CTEs o incluso CTEs anidados. Es similar a las subconsultas, pero las CTEs hacen que el código sea más legible y más fácil de dividir el cálculo en pasos. Y con las CTEs anidadas, puedes escribir una consulta y referenciarla inmediatamente como cualquier otra tabla.

Algunos de estos ejemplos muestran que también se pueden utilizar funciones de agregación y de ventana. Esto hace que las CTEs sean más potentes.

Además, los CTEs son estupendos para datos jerárquicos como estructuras organizativas y para recorrer un gráfico. Sin embargo, en estos casos es necesario escribir una CTE recursiva como lo hice en la pregunta 3.

Finalmente, si quieres escribir consultas recursivas y utilizarlas, el primer paso es aprender CTEs. Si necesitas ayuda, aquí tienes una guía sobre cómo abordar el aprendizaje de CTEs.

Como puedes ver, las CTEs tienen muchos beneficios y usos prácticos. Estos ejemplos te muestran sólo una visión de las soluciones a problemas prácticos que llevarían mucho más tiempo si no fuera por los CTEs. ¿Quieres más? No hay problema, echa un vistazo a otros usos prácticos de los CTEs.

Una vez que aprendas los CTEs y empieces a utilizarlos, intenta pensar primero en el problema. Desglosa los pasos del cálculo y luego traduce esta lógica en un código de expresión de tabla común SQL. Las CTEs son adecuadas para esto.

Dado que son una versión de las tablas temporales, sea claro a la hora de nombrarlas: utilice un nombre que indique inmediatamente lo que hace la CTE y sea coherente con las convenciones de nomenclatura. En general, es conveniente seguir las mejores prácticas de las CTE, que facilitan la lectura de su código (y el de los demás).

¿Quiere saber más sobre las expresiones de tabla comunes de SQL?

Si estás preparando una entrevista de trabajo en SQL, quieres ser mejor en tu trabajo actual, o simplemente quieres aprender algo nuevo en tu tiempo libre, echa un vistazo a nuestro Consultas recursivas y expresiones de tabla comunes curso.

En él encontrarás un enfoque sistemático de las CTEs, explicando la teoría que las sustenta, mostrándote la sintaxis y dándote algunos ejemplos más para practicar. Todo el curso forma parte del SQL avanzado que cubre dos temas adicionales: las funciones de ventana (que hemos utilizado en este artículo) y las extensiones de GROUP BY. ¡Buena suerte!