Práctica avanzada de SQL: 10 ejercicios de práctica de SQL con soluciones

Dado que el dominio de SQL sigue siendo muy demandado tanto por los profesionales de datos como por los desarrolladores, nunca se insistirá lo suficiente en la importancia de la práctica. Siga leyendo para adentrarse en el mundo del SQL avanzado y realizar ejercicios prácticos para mejorar sus habilidades.

Este artículo le ofrece una colección de diez ejercicios de práctica de SQL desafiantes específicamente para aquellos que buscan mejorar sus habilidades de SQL avanzado. Los ejercicios cubren una selección de conceptos de SQL y le ayudarán a refrescar sus conocimientos de SQL avanzado. Cada ejercicio va acompañado de una solución detallada, lo que le permitirá poner a prueba sus conocimientos y profundizar en conceptos SQL complejos. Los ejercicios proceden de nuestros cursos prácticos de SQL avanzado. Si quieres ver más ejercicios como estos, echa un vistazo a estos cursos:

1. Funciones de ventana Conjunto de Prácticas
2. 2021 Mensual Ejercicios prácticos de SQLs - Avanzado
3. 2022 Mensual Ejercicios prácticos de SQLs - Avanzado

Comencemos.

Practicando su Camino a la Competencia en SQL

La práctica es un componente integral en el dominio de SQL; su importancia no puede ser exagerada. El camino hacia el dominio de SQL avanzado requiere dedicación, perseverancia y un fuerte compromiso con la práctica continua. Mediante la práctica regular de SQL avanzado, las personas pueden perfeccionar sus habilidades, ampliar sus conocimientos y desarrollar una comprensión profunda de las complejidades de la gestión y manipulación de datos.

SQL avanzado Los ejercicios sirven como herramientas inestimables, desafiando a los alumnos a aplicar sus conocimientos teóricos en escenarios prácticos y consolidando aún más su comprensión de conceptos complejos. Con cada sesión de práctica dedicada a SQL, podrá descubrir técnicas eficaces y adquirir la confianza necesaria para enfrentarse a los retos que plantean los datos en el mundo real.

Repasemos los ejercicios y sus soluciones.

SQL avanzado Ejercicios prácticos

Presentaremos varios ejercicios avanzados de SQL que cubren funciones de ventana, JOINs, GROUP BY, expresiones comunes de tabla (CTEs) y más.

Sección 1: SQL avanzado Ejercicios JOIN

En los siguientes ejercicios avanzados de SQL, utilizaremos una base de datos de ropa deportiva que almacena información sobre ropa, categorías de ropa, colores, clientes y pedidos. Contiene cinco tablas: color, customer, category, clothing, y clothing_order. Veamos los datos de esta base de datos.

La tabla color contiene las siguientes columnas:

• idalmacena el identificador único de cada color.
• name almacena el nombre del color.
• extra_fee almacena el recargo (si lo hay) que se añade a la ropa pedida en este color.

En la tabla customer, encontrará las siguientes columnas:

• id almacena el ID del cliente.
• first_name almacena el nombre del cliente.
• last_name almacena el apellido del cliente.
• favorite_color_idalmacena el ID del color favorito del cliente (hace referencia a la tabla de colores).

La tabla category contiene estas columnas:

• id almacena el ID único de cada categoría.
• name almacena el nombre de la categoría.
• parent_id almacena el ID de la categoría principal para esta categoría (si es una subcategoría). Si este valor es NULL, denota que esta categoría es una categoría principal. Nota: Los valores están relacionados con los de la columna id de esta tabla.

La tabla clothing almacena datos en las siguientes columnas:

• id almacena el ID único de cada artículo.
• name almacena el nombre de esa prenda.
• size almacena la talla de esa prenda: S, M, L, XL, 2XL o 3XL.
• price el precio del artículo.
• color_id almacena el color del artículo (hace referencia a la tabla de colores).
• category_id almacena la categoría del artículo (hace referencia a la tabla de categorías).

La tabla clothing_order contiene las siguientes columnas:

• id almacena el identificador único del pedido.
• customer_id almacena el ID del cliente que ha pedido la ropa (hace referencia a la tabla customer ).
• clothing_id almacena el ID del artículo pedido (hace referencia a la tabla clothing ).
• items almacena cuántas prendas de ese artículo ha pedido el cliente.
• order_date almacena la fecha del pedido.

Hagamos algunos ejercicios avanzados de SQL que se centran en JOINs.

Ejercicio 1: Listar Todos los Artículos de Ropa

Ejercicio:

Mostrar el nombre de los artículos de ropa (nombre de la columna clothes), su color (nombre de la columna color), y el apellido y nombre del cliente(s) que compró esta ropa en su color favorito. Ordene las filas según el color, en orden ascendente.

Solución:

SELECT
  cl.name AS clothes,
  col.name AS color,
  cus.last_name,
  cus.first_name
FROM clothing_order co
JOIN clothing cl
  ON cl.id = co.clothing_id
JOIN color col
  ON col.id = cl.color_id
JOIN customer c
  ON cus.id = co.customer_id
WHERE cus.favorite_color_id = cl.color_id
ORDER BY col.name;

Explicación de la solución:

Queremos mostrar los valores de las columnas de tres tablas diferentes (clothing, color, y customer), incluida la información sobre qué cliente pidió un determinado artículo (de la tabla clothing_order ). Por lo tanto, necesitamos unir estas cuatro tablas en sus columnas comunes.

Primero, seleccionamos de la tabla clothing_order (alias co) y la unimos con la tabla clothing (alias cl). Unimos las tablas utilizando la columna de clave principal de la tabla clothing (id) y la columna de clave ajena de la tabla clothing_order (clothing_id); esta columna de clave ajena vincula las tablas clothing y clothing_order.

A continuación, unimos la tabla color (alias col) con la tabla clothing (alias cl). Aquí utilizamos la columna de clave primaria de la tabla color (id) y la columna de clave foránea de la tabla clothing (color_id).

Por último, unimos la tabla customer (alias cus) con la tabla clothing_order (alias co). La clave foránea de la tabla clothing_order (customer_id) se une a la clave primaria de la tabla customer (id).

La cláusula ON almacena la condición para la sentencia JOIN. Por ejemplo, un artículo de la tabla clothing con un valor id de 23 se une con un pedido de la tabla clothing_order donde el valor clothing_id es igual a 23.

Siga este artículo para ver más ejemplos de JOINing de tres (o más) tablas. Y aquí le mostramos cómo hacer una JOIN IZQUIERDA de múltiples tablas.

Ejercicio 2: Obtener todos los clientes que no compran

Ejercicio:

Seleccionar el apellido y nombre de los clientes y el nombre de su color favorito para los clientes sin compras.

Solución:

SELECT
  cus.last_name,
  cus.first_name,
  col.name
FROM customer cus
JOIN color col
  ON col.id = cus.favorite_color_id
LEFT JOIN clothing_order o
  ON o.customer_id = cus.id
WHERE o.customer_id IS NULL;

Explicación de la solución:

Aquí necesitamos mostrar los nombres y apellidos de los clientes de la tabla customer y su nombre favorito color de la tabla de colores. Debemos hacerlo sólo para los clientes que aún no han realizado ningún pedido; por lo tanto, necesitamos información de la tabla clothing_order. Así que el siguiente paso es unir estas tres tablas.

En primer lugar, unimos la tabla customer (alias cus) con la tabla color (alias col). Para ello, utilizamos la siguiente condición: la columna de clave principal de la tabla color (id) debe ser igual a la columna de clave ajena de la tabla customer (favorite_color_id). Eso nos permite seleccionar el nombre del color favorito en lugar de su ID.

Así nos aseguramos de que sólo aparecen en la lista los clientes que aún no han realizado ningún pedido:

• LEFT JOIN la tabla clothing_order (alias o) con la tabla customer (alias cus) para garantizar que todas las filas de la tabla customer (incluso las que no coinciden) aparecen en la lista.
• En la cláusula WHERE, definimos una condición para mostrar sólo las filas con la columna customer_id de la tabla clothing_order igual a NULL (lo que significa que sólo se devolverán los clientes cuyos ID no estén en la tabla clothing_order ).

Existen diferentes tipos de JOINs, incluyendo INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN. Puede obtener más información siguiendo los artículos enlazados.

Ejercicio 3: Seleccionar todas las categorías principales y sus subcategorías

Ejercicio:

Seleccione el nombre de las categorías principales (que tienen un NULL en la columna parent_id) y el nombre de su subcategoría directa (si existe). Nombre la primera columna categoría y la segunda columna subcategoría.

Solución:

SELECT
  c1.name AS category,
  c2.name AS subcategory
FROM category c1
JOIN category c2
  ON c2.parent_id = c1.id
WHERE c1.parent_id IS NULL;

Explicación de la solución:

Cada categoría listada en la tabla category tiene su propio ID (almacenado en la columna id ); algunas también tienen el ID de su categoría padre (almacenado en la columna parent_id ). Así, podemos unir la tabla category consigo misma para listar las categorías principales y sus subcategorías.

El tipo de JOIN en el que unimos una tabla a sí misma se denomina coloquialmente autounión. Cuando se une una tabla a sí misma, hay que dar diferentes nombres de alias a cada copia de la tabla. Aquí tenemos una tabla category con el alias c1 y otra tabla category con el alias c2.

Seleccionamos la tabla name de la tabla category (con el alias c1) y nos aseguramos de enumerar sólo las categorías principales haciendo que su columna parent_id sea igual a NULL en la cláusula WHERE. A continuación, unimos la tabla category (alias c1) con la tabla category (alias c2). Esta última proporciona subcategorías para las categorías principales. Por lo tanto, en la cláusula ON, definimos que la columna parent_id de c2 debe ser igual a la columna id de c1.

Lea este artículo para obtener más información sobre las autouniones.

Los ejercicios de esta sección han sido extraídos de nuestro curso 2021 Mensual Ejercicios prácticos de SQL s - Avanzado. Todos los meses publicamos un nuevo curso práctico de SQL en nuestra sección Mensual Ejercicio de SQL; todos los meses impares, el curso es de nivel avanzado. Los cursos prácticos avanzados de SQL de 2021 se han recopilado en nuestro curso 2021 Monthly Ejercicios prácticos de SQLs - Advanced. Compruébalo para encontrar más ejercicios JOIN y otros retos SQL avanzados.

Sección 2: Ejercicios avanzados de GROUP BY

En los siguientes ejercicios de SQL avanzado, utilizaremos una base de datos de un club deportivo que almacena información sobre corredores y pruebas de atletismo. Contiene tres tablas: runner, event, y runner_event. Veamos los datos de esta base de datos.

La tabla runner contiene las siguientes columnas:

• id almacena el ID único del corredor.
• name almacena el nombre del corredor.
• main_distance almacena la distancia (en metros) que el corredor corre durante las pruebas.
• age almacena la edad del corredor.
• is_female indica si el corredor es hombre o mujer.

La tabla event contiene las siguientes columnas:

• id almacena el ID único del evento.
• name almacena el nombre de la prueba (por ejemplo, Maratón de Londres, Carrera de Varsovia o Carrera de Año Nuevo).
• start_date La fecha del evento.
• city almacena la ciudad en la que se celebra el evento.

La tabla runner_event contiene las siguientes columnas:

• runner_id almacena el ID del corredor.
• event_id almacena el ID del evento.

Hagamos algunos ejercicios avanzados de SQL que se centran en GROUP BY.

Ejercicio 4: Organizar Corredores en Grupos

Ejercicio:

Seleccionar la distancia principal y el número de corredores que corren la distancia dada (runners_number). Mostrar sólo aquellas filas en las que el número de corredores sea mayor que 3.

Solución:

SELECT
  main_distance,
  COUNT(*) AS runners_number
FROM runner
GROUP BY main_distance
HAVING COUNT(*) > 3;

Explicación de la solución:

Queremos obtener el recuento de corredores por cada distancia que corren. Para ello, tenemos que agrupar a todos los corredores por distancia y utilizar la función de agregado COUNT() para calcular cuántos corredores hay en cada grupo de distancia.

Seleccionamos la columna main_distance y GROUP BY esta columna. Ahora, cuando utilicemos las funciones agregadas COUNT(), nos va a dar el número de corredores que coinciden con cada valor main_distance.

La cláusula GROUP BY se utiliza para agrupar filas de una tabla basándose en una o más columnas. Divide el conjunto de resultados en subconjuntos o grupos, donde cada grupo comparte los mismos valores en la(s) columna(s) especificada(s). Esto nos permite realizar funciones agregadas (como SUM(), COUNT(), AVG(), etc.) en cada grupo por separado.

Estas son las preguntas más comunes de la entrevista GROUP BY.

Para mostrar sólo los grupos con más de tres corredores, utilizamos una cláusula HAVING que filtra los valores devueltos por la función agregada COUNT().

La cláusula HAVING suele utilizarse junto con la cláusula GROUP BY para filtrar los datos agrupados en función de condiciones específicas. Funciona de forma similar a la cláusula WHERE, pero opera sobre los datos agrupados en lugar de sobre filas individuales. Revise este artículo para aprender más sobre la cláusula HAVING.

Ejercicio 5: Cuántos Corredores Participan en Cada Evento

Ejercicio:

Muestre el nombre del evento y el número de miembros del club que participan en este evento (llame a esta columna runner_count). Tenga en cuenta que puede haber eventos en los que no participen miembros del club. Para estos eventos, runner_count debe ser igual a 0.

Solución:

SELECT
  event.name,
  COUNT(runner.id) AS runner_count
FROM event
LEFT JOIN runner_event
  ON runner_event.event_id = event.id
LEFT JOIN runner
  ON runner_event.runner_id = runner.id
GROUP BY event.name;

Explicación de la solución:

Aquí queremos mostrar el nombre del evento de la tabla event y el número de participantes de la tabla runner. Las tablas event y runner están vinculadas por una relación de muchos a muchos; para unir estas tablas, también necesitamos la tabla runner_event que relaciona eventos y corredores.

En primer lugar, seleccionamos en la tabla event. A continuación, LEFT JOIN con la tabla runner_event, que a su vez LEFT JOINed con la tabla runner. ¿Por qué utilizamos aquí LEFT JOIN? Porque queremos asegurarnos de que se muestran todos los eventos (incluso los que no tienen participantes).

Seleccionamos el nombre del evento y el recuento de todos los participantes; por lo tanto, necesitamos GROUP BY el nombre del evento para obtener el recuento de participantes por evento. Tenga en cuenta que utilizamos COUNT(runner_id) en lugar de COUNT(*). Esto es para asegurarnos de que mostramos cero para los eventos sin participantes (es decir, para los eventos que no están vinculados a ningún runner_id). Puede obtener más información sobre las distintas variantes de la función COUNT() aquí.

Ejercicio 6: Agrupar Corredores por Distancia Principal y Edad

Ejercicio:

Muestre la distancia y el número de corredores que hay para las siguientes categorías de edad: menores de 20 años, 20-29, 30-39, 40-49 y mayores de 50 años. Utilice los siguientes alias de columna: under_20, age_20_29, age_30_39, age_40_49, y over_50.

Solución:

SELECT
  main_distance,
  COUNT(CASE WHEN age < 20 THEN id END) AS under_20,
  COUNT(CASE WHEN age >= 20 AND age < 30 THEN id END) AS age_20_29,
  COUNT(CASE WHEN age >= 30 AND age < 40 THEN id END) AS age_30_39,
  COUNT(CASE WHEN age >= 40 AND age < 50 THEN id END) AS age_40_49,
  COUNT(CASE WHEN age >= 50 THEN id END) AS over_50
FROM runner
GROUP BY main_distance;

Explicación de la solución:

Esto es similar al Ejercicio 4 - queremos saber el número de corredores por valor de distancia. Así que seleccionamos la columna main_distance y GROUP BY esta columna. A continuación, utilizamos varias funciones agregadas de COUNT() para obtener el número de corredores por distancia. Sin embargo, aquí necesitamos dividir aún más a los corredores según su edad.

La sentencia CASE WHEN resulta muy útil en este caso, ya que puede utilizarse para evaluar condiciones y devolver diferentes valores en función de los resultados de dichas condiciones. Podemos pasarla como argumento a la función agregada COUNT() para obtener el número de corredores que cumplen una condición determinada. Veamos cómo funciona.

CASE WHEN age >= 20 AND age < 30 THEN id END

Esta sentencia CASE WHEN devuelve id sólo cuando la edad de un corredor es mayor o igual a 20 años y menor de 30 años. En caso contrario, devuelve NULL. Cuando se envuelve en la función agregada COUNT(), devuelve el recuento de corredores que cumplen la condición definida en la sentencia CASE WHEN.

Para obtener el número de corredores de cada uno de los cinco grupos de edad, necesitamos utilizar tantas funciones COUNT() y sentencias CASE WHEN como grupos de edad tengamos. Puedes leer sobre el recuento de filas combinando CASE WHEN y GROUP BY aquí.

Sección 3: Ejercicios avanzados de Funciones de ventana

En los siguientes ejercicios avanzados de SQL, utilizaremos una base de datos Northwind para una tienda online con numerosos alimentos. Contiene seis tablas: customers, orders, products, categories, order_items, y channels. Veamos los datos de esta base de datos.

La tabla customers tiene 15 columnas:

• customer_id almacena el ID del cliente.
• email almacena la dirección de correo electrónico del cliente.
• full_name El nombre completo del cliente.
• address almacena la calle y el número de casa del cliente.
• city La ciudad en la que vive el cliente.
• region La región del cliente (no siempre aplicable).
• postal_code El código postal del cliente.
• country El país del cliente.
• phone El número de teléfono del cliente.
• registration_date La fecha de registro del cliente.
• channel_id Almacena el ID del canal a través del cual el cliente encontró la tienda.
• first_order_id almacena el ID del primer pedido realizado por el cliente.
• first_order_date almacena la fecha del primer pedido del cliente.
• last_order_id almacena el ID del último pedido del cliente (es decir, el más reciente).
• last_order_date almacena la fecha del último pedido del cliente.

La tabla orders tiene las siguientes columnas:

• order_id ID del pedido.
• customer_id almacena el ID del cliente que realizó el pedido.
• order_date almacena la fecha en la que se realizó el pedido.
• total_amount almacena el importe total pagado por el pedido.
• ship_name stores el nombre de la persona a la que se envió el pedido.
• ship_address almacena la dirección (número de casa y calle) a la que se envió el pedido.
• ship_city la ciudad a la que se envió el pedido.
• ship_region almacena la región en la que se encuentra la ciudad.
• ship_postalcode almacena el código postal de destino.
• ship_country almacena el país de destino.
• shipped_date almacena la fecha de envío del pedido.

La tabla products tiene las siguientes columnas:

• product_id almacena el ID del producto.
• product_name almacena el nombre del producto.
• category_id almacena la categoría a la que pertenece el producto.
• unit_price almacena el precio de una unidad del producto (por ejemplo, por botella, paquete, etc.).
• discontinued indica si el producto ya no se vende.

La tabla categories tiene las siguientes columnas:

• category_id almacena el ID de la categoría.
• category_name almacena el nombre de la categoría.
• description almacena una breve descripción de la categoría.

La tabla order_items tiene las siguientes columnas

• order_id almacena el ID del pedido en el que se compró el producto.
• product_id almacena el ID del producto comprado en el pedido.
• unit_price almacena el precio unitario del producto. (Tenga en cuenta que puede ser diferente del precio de la categoría del producto; el precio puede cambiar con el tiempo y se pueden aplicar descuentos).
• quantity almacena el número de unidades compradas en el pedido.
• discount almacena el descuento aplicado al producto en cuestión.

La tabla channels tiene las siguientes columnas:

• id stores the ID of the channel.
• channel_name stores the name of the channel through which the customer found the shop.

Hagamos algunos ejercicios avanzados de SQL que se centran en las funciones de ventana.

Ejercicio 7: Listar los 3 Pedidos Más Caros

Ejercicio:

Cree una clasificación densa de los pedidos basada en su total_amount. Cuanto mayor sea el importe, más alto deberá ser el pedido. Si dos pedidos tienen el mismo total_amount, el pedido más antiguo debería ir más arriba (tendrá que añadir la columna order_date a la ordenación). Nombre la columna de ordenación rank. A continuación, seleccione sólo los pedidos con las tres clasificaciones densas más altas. Muestra el rango, order_id, y total_amount.

Solución:

WITH orders_with_ranking AS (
  SELECT
    DENSE_RANK() OVER(ORDER BY total_amount DESC, order_date) AS rank,
    order_id,
    total_amount
  FROM orders
)
SELECT *
FROM orders_with_ranking
WHERE rank <= 3;

Explicación de la solución:

Empecemos por la primera parte de la instrucción. Queremos crear una clasificación densa de pedidos basada en su total_amount (cuanto mayor sea el valor, mayor será la clasificación) y su order_date valor (cuanto mayor sea la fecha, mayor será la clasificación). Tenga en cuenta que el valor del rango sólo puede duplicarse cuando las columnas total_amount y order_date son ambas iguales para más de una fila.

Para ello, utilizamos la función de ventana DENSE_RANK(). En su cláusula OVER(), especificamos el orden: descendente para los valores de total_amount y ascendente para los de order_date. También mostramos las columnas order_id y total_amount de la tabla orders.

Hasta ahora, hemos listado todos los órdenes junto con sus valores de rango denso. Pero queremos ver sólo los 3 primeros pedidos (en los que la columna de rango es menor o igual que 3). Analicemos los pasos que damos a partir de aquí:

1. Definimos una Expresión de Tabla Común (CTE) utilizando esta sentencia SELECT - es decir, utilizamos la cláusula WITH seguida del nombre de la CTE y luego colocamos la sentencia SELECT entre paréntesis.
2. Luego seleccionamos desde esta CTE, proporcionando la condición para la columna rango en la cláusula WHERE.

Quizá se pregunte por qué necesitamos una sintaxis tan compleja para definir una ETC y luego consultarla. Puede que diga que podríamos establecer la condición para la columna de rango en la cláusula WHERE de la primera consulta SELECT. Bueno, eso no es posible debido al orden de ejecución de las consultas SQL.

Tenemos que usar la Expresión de Tabla Común aquí porque no se pueden usar funciones de ventana en la cláusula WHERE. El orden de las operaciones en SQL es el siguiente:

1. FROM, JOIN
2. WHERE
3. GROUP BY
4. Aggregate functions
5. HAVING
6. Window functions
7. SELECT
8. ORDER BY

Sólo puede utilizar funciones ventana en las cláusulas SELECT y ORDER BY. Si desea hacer referencia a funciones ventana en la cláusula WHERE, debe colocar el cálculo de la función ventana en una CTE (como hicimos en nuestro ejemplo) o en una subconsulta y hacer referencia a la función ventana en la consulta externa.

Siga este artículo para obtener más información sobre CTEs y CTEs recursivas.

Para darle un poco de información sobre las funciones de clasificación disponibles, hay tres funciones que le permiten clasificar sus datos: RANK(), DENSE_RANK(), y ROW_NUMBER(). Veámoslas en acción.

Values to be ranked	RANK()	DENSE_RANK()	ROW_NUMBER()
1	1	1	1
1	1	1	2
1	1	1	3
2	4	2	4
3	5	3	5
3	5	3	6
4	7	4	7
5	8	5	8

La función RANK() asigna el mismo rango si varias filas consecutivas tienen el mismo valor. Entonces, la siguiente fila obtiene el siguiente rango como si las filas anteriores tuvieran valores distintos. Aquí, los rangos 1,1,1 van seguidos de 4 (como si fuera 1,2,3 en lugar de 1,1,1).

La función DENSE_RANK() también asigna el mismo rango si varias filas consecutivas tienen el mismo valor. Entonces, la fila siguiente obtiene el rango uno mayor que el anterior. En este caso, 1,1,1 va seguido de 2.

La función ROW_NUMBER() asigna números consecutivos a cada fila siguiente sin tener en cuenta los valores de las filas.

Aquí hay un artículo sobre cómo clasificar datos. También puedes aprender más sobre las diferencias entre las funciones rank de SQL.

Ejercicio 8: Calcular Deltas entre Pedidos Consecutivos

Ejercicio:

En este ejercicio, vamos a calcular la diferencia entre dos pedidos consecutivos del mismo cliente.

Muestre el ID del pedido (order_id), el ID del cliente (customer_id), el total_amount del pedido, el total_amount del pedido anterior basado en el order_date (nombre la columna previous_value), y la diferencia entre el total_amount del pedido actual y el pedido anterior (nombre la columna delta).

Solución:

SELECT
  order_id,
  customer_id,
  total_amount,
  LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS previous_value,
  total_amount - LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS delta
FROM orders;

Explicación de la solución:

Aquí seleccionamos el ID del pedido, el ID del cliente y el importe total de la tabla orders. La función LAG() recupera el valor anterior de total_amount. En la cláusula OVER(), definimos la función LAG() por separado para cada cliente y ordenamos el resultado por fecha de pedido. Por último, restamos el valor devuelto por la función LAG() del valor total_amount de cada fila para obtener el delta.

La columna previous_value almacena un valor nulo para la primera fila, ya que no hay valores anteriores. Por lo tanto, la columna delta también es nula para la primera fila. Los siguientes valores de la columna delta almacenan las diferencias entre pedidos consecutivos realizados por el mismo cliente.

Cabe mencionar que un delta representa la diferencia entre dos valores. Calculando el delta entre los importes de las ventas diarias, podemos determinar la dirección del crecimiento/decrecimiento de las ventas día a día.

Siga este artículo para saber más sobre el cálculo de diferencias entre dos filas. Y aquí se explica cómo calcular las diferencias interanuales.

Ejercicio 9: Calcular el Total Corrido de Compras por Cliente

Ejercicio:

Para cada cliente y sus pedidos, muestre lo siguiente:

• customer_id - el ID del cliente.
• full_name - el nombre completo del cliente.
• order_id - el ID del pedido.
• order_date - La fecha del pedido.
• total_amount - el total gastado en este pedido.
• running_total - el total actual gastado por el cliente en cuestión.

Ordenar las filas por ID de cliente y fecha de pedido.

Solución:

SELECT
  orders.customer_id,
  customers.full_name,
  orders.order_id,
  orders.order_date,
  orders.total_amount,
  SUM(orders.total_amount) OVER(PARTITION BY orders.customer_id ORDER BY orders.order_date) AS running_total
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id
ORDER BY orders.customer_id, orders.order_date;

Explicación de la solución:

Un total acumulado se refiere al cálculo que acumula los valores de una columna o expresión específica a medida que se procesan las filas en un conjunto de resultados. Proporciona una suma acumulada de los valores encontrados hasta la fila actual. El total acumulado se calcula sumando el valor actual a la suma de todos los valores anteriores. Esto puede ser especialmente útil en varios escenarios, como el seguimiento de las ventas acumuladas, el cálculo de saldos corrientes o el análisis del progreso acumulado a lo largo del tiempo.

Siga este artículo para obtener más información sobre el cálculo de un total acumulado. Y aquí hay un artículo sobre el cálculo de promedios acumulados.

Seleccionamos el ID del cliente, el ID del pedido, la fecha del pedido y el total del pedido de la tabla orders. Luego, unimos la tabla orders con la tabla customers en sus respectivas columnas customer_id para poder mostrar el nombre completo del cliente.

Utilizamos la función de ventana SUM() para calcular el total acumulado de cada cliente por separado (PARTITION BY orders.customer_id) y, a continuación, lo ordenamos de forma ascendente por fecha (ORDER BY orders.order_date).

Por último, ordenamos la salida de esta consulta por ID de cliente y fecha de pedido.

Sección 4: Ejercicios avanzados de consultas recursivas

En los siguientes ejercicios avanzados de SQL, utilizaremos una base de datos de un sitio web que almacena información sobre estudiantes y cursos. Contiene tres tablas: student, course, y student_course. Veamos los datos de esta base de datos.

La tabla student contiene las siguientes columnas:

• id almacena el número de identificación único de cada estudiante.
• name Almacena el nombre del alumno.
• email almacena el correo electrónico del estudiante.
• invited_by_id almacena el ID del estudiante que invitó a este estudiante al sitio web. Si el alumno se registró sin invitación, esta columna será NULL.

La tabla course consta de las siguientes columnas:

• id almacena el número de identificación único de cada curso.
• name almacena el nombre del curso.

La tabla student_course consta de las siguientes columnas

• id almacena el ID único de cada fila.
• student_id almacena el ID del estudiante.
• course_id almacena el ID del curso.
• minutes_spent almacena el número de minutos que el estudiante ha dedicado al curso.
• is_completed se establece en True cuando el estudiante termina el curso.

Los ejercicios de esta sección han sido extraídos de nuestro Conjunto de Prácticas Funciones de ventana . En este set, encontrarás más ejercicios de funciones de ventana sobre bases de datos que almacenan ventas al por menor, seguimiento de competiciones y tráfico de sitios web.

Hagamos algunos ejercicios avanzados de SQL que se centran en consultas recursivas.

Ejercicio 10: Encontrar la ruta de invitación para cada estudiante

Ejercicio:

Muestre la ruta de invitaciones para cada estudiante (nombre esta columna path). Por ejemplo, si María fue invitada por Alicia y Alicia no fue invitada por nadie, la ruta para María debería verse así: Alice->Mary.

Incluya en los resultados id, name y invited_by_id de cada alumno.

Solución:

WITH RECURSIVE hierarchy AS (
  SELECT
	id,
	name,
    invited_by_id,
    CAST(name AS text) AS path
  FROM student
  WHERE invited_by_id IS NULL
  UNION ALL
  SELECT
    student.id,
    student.name,
    student.invited_by_id,
    hierarchy.path || '->' || student.name
  FROM student, hierarchy
  WHERE student.invited_by_id = hierarchy.id
)
 
SELECT *
FROM hierarchy;

Explicación de la solución:

Este ejercicio requiere que creemos un valor personalizado para la columna ruta que contenga la ruta de invitación de cada cliente. Por ejemplo, Ann Smith fue invitado por Veronica Knight, que a su vez fue invitado por Karli Roberson; por lo tanto, obtenemos la columna de ruta como Karli Roberson->Veronica Knight->Ann Smith para el nombre Ann Smith.

Como puede observar, necesitamos un mecanismo de recursión para profundizar en la ruta de invitación. Podemos escribir una consulta recursiva definiéndola con la sentencia WITH RECURSIVE, seguida del nombre de la consulta.

El contenido de la consulta recursiva hierarchy es el siguiente:

• Seleccionamos las columnas id, name y invited_by_id de la tabla student. A continuación, utilizamos la función CAST() para convertir el tipo de columna name en el tipo de datos TEXT, lo que garantiza una concatenación fluida (con -> y los nombres siguientes) en la consulta principal. La condición de la cláusula WHERE garantiza que sólo los estudiantes que no han sido invitados aparezcan en la lista de esta consulta.
• El operador UNION ALL combina los conjuntos de resultados de dos o más sentencias SELECT sin eliminar los duplicados. En este caso, las consultas sobre las que se ejecuta UNION ALL tienen los mismos conjuntos de cuatro columnas; el conjunto de resultados de una se añade al conjunto de resultados de otra.
• En la siguiente sentencia SELECT, volvemos a seleccionar las columnas id, name, y invited_by_id de la tabla student. A continuación, concatenamos la columna ruta (que proviene de la consulta jerárquica recursiva definida en la primera sentencia SELECT ) con el signo -> y el nombre del alumno. En la cláusula WHERE, definimos que la columna invited_by_id de la tabla student es igual a la columna id de la consulta jerárquica recursiva, de modo que obtenemos el nombre del alumno que invitó al alumno actual; en la siguiente iteración, obtenemos el nombre del alumno que invitó a ese alumno, y así sucesivamente.

Esto se denomina consulta recursiva, ya que se consulta a sí misma para recorrer el camino de las invitaciones.

Avanzando Consulta a Consulta

Los ejercicios avanzados de SQL presentados en este artículo proporcionan una plataforma completa para perfeccionar sus habilidades de SQL, una consulta a la vez. Al profundizar en las funciones de ventana, JOINs, GROUP BY, y mucho más, habrá ampliado su comprensión de conceptos SQL complejos y adquirido experiencia práctica en la resolución de retos de datos del mundo real.

La práctica es la clave para dominar las habilidades SQL. A través de la práctica constante, puede elevar su competencia y transformar sus conocimientos teóricos en experiencia práctica. Este artículo muestra ejercicios de nuestros cursos; puede descubrir más ejercicios como éste inscribiéndose en nuestro:

1. Funciones de ventana Conjunto de Prácticas
2. 2021 Mensual Ejercicios prácticos de SQLs - Avanzado
3. 2022 Mensual Ejercicios prácticos de SQLs - Avanzado

¡Inscríbete ahora y empieza gratis! ¡Mucha suerte!