Cómo mejorar tus consultas SQL utilizando CTEs (Expresiones de Tabla Comunes)

--Ejemplo 1: Obtener el nombre del cliente, la fecha del pedido y la cantidad total para cada pedido

/*
En este ejemplo, vamos a crear una CTE que obtendrá el nombre del cliente, la fecha del pedido y la cantidad total
para cada pedido. Usaremos una JOIN para combinar la información de las tablas "customers", "orders" y "order_items".

*/
WITH order_summary AS (
SELECT c.customer_name, o.order_date, SUM(oi.quantity * p.product_price) AS order_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY c.customer_name, o.order_date
)
SELECT * FROM order_summary;

/*
Explicación: En la CTE "order_summary", se une la tabla "customers" con "orders" y "order_items"
para obtener el nombre del cliente, la fecha del pedido y la cantidad total para cada pedido.
La tabla "products" se une a través de la tabla "order_items" para obtener el precio de cada producto
y se utiliza para calcular el costo total de cada pedido. Luego, se utiliza una función de agregación
SUM para sumar la cantidad total de cada pedido y agrupar por el nombre del cliente y la fecha del
pedido. En la consulta final, se seleccionan todas las columnas de la CTE "order_summary".

*/

--Ejemplo 2: Obtener el nombre de los proveedores que no han vendido ningún producto

/*

En este ejemplo, vamos a crear una CTE que obtendrá el nombre de los proveedores que no han vendido ningún producto.
Usaremos una LEFT JOIN para incluir todos los proveedores y una subconsulta para excluir a los proveedores que han
vendido productos.

*/
WITH suppliers_summary AS (
SELECT s.supplier_name, COUNT(p.product_id) AS num_products
FROM suppliers s
LEFT JOIN products p ON s.supplier_id = p.supplier_id
GROUP BY s.supplier_name
)
SELECT supplier_name FROM suppliers_summary
WHERE num_products = 0;

/*
Explicación: En la CTE "suppliers_summary", se realiza una LEFT JOIN entre las tablas "suppliers" y "products"
para obtener el número de productos que cada proveedor ha vendido. La función COUNT se utiliza para contar
el número de registros en la tabla "products" para cada proveedor. Luego, se utiliza una subconsulta en la
consulta final para seleccionar solo los proveedores que no tienen productos vendidos.

*/

--Ejemplo 3: Obtener los nombres de los productos que han sido ordenados más de una vez

/*

En este ejemplo, vamos a crear una CTE que obtendrá los nombres de los productos que han sido ordenados
más de una vez. Usaremos una subconsulta y una función de ventana para realizar esta consulta.

*/

WITH product_orders AS (
SELECT p.product_name, COUNT(oi.order_id) AS num_orders,
ROW_NUMBER() OVER (PARTITION BY p.product_id ORDER BY COUNT(oi.order_id) DESC) AS row_num
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
)
SELECT product_name, num_orders FROM product_orders
WHERE row_num <= 1 AND num_orders >= 1;

/*
Explicación: En la CTE "product_orders", se unen las tablas "products" y "order_items" Entonces,
en la CTE "product_orders", se están uniendo las tablas "products" y "order_items". Es probable
que la tabla "products" contenga información sobre los productos que la empresa vende, como su
nombre, precio y descripción, mientras que la tabla "order_items" probablemente contenga información
sobre los pedidos de los clientes, como el número de pedido, la fecha y la cantidad de productos
que se han ordenado.

Al unir estas dos tablas, la CTE "product_orders" probablemente esté creando una lista de todos los
productos que se han ordenado y los detalles de esas órdenes, como la cantidad y el precio. Esto
podría ser útil para la empresa para rastrear la popularidad de los productos y para ayudar en
la planificación de la producción y el inventario.

*/

-- Ejemplo 3: Multiple CTEs

/*
En este ejemplo, vamos a mostrar cómo se pueden utilizar múltiples CTEs en una sola consulta. Para ello,
vamos a utilizar la base de datos que hemos creado en los ejemplos anteriores.

Supongamos que queremos obtener una lista de todos los pedidos realizados por los clientes, junto con
la información del cliente y el total de ventas por cliente. Para hacerlo, necesitaremos unir las tablas
customers, orders y order_items y agruparlas por cliente. También necesitamos calcular la cantidad total
vendida por cliente.

Podemos hacer esto utilizando múltiples CTEs. Primero, crearemos una CTE para unir las tablas customers,
orders y order_items. Luego, crearemos otra CTE para calcular el total de ventas por cliente. Finalmente,
uniremos estas dos CTEs para obtener la lista de pedidos junto con la información del cliente y el total
de ventas por cliente.

*/
WITH orders_by_customer AS (
SELECT c.customer_name, o.order_id, o.order_date, oi.product_id, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
),
sales_by_customer AS (
SELECT customer_name, SUM(product_price * quantity) AS total_sales
FROM orders_by_customer obc
JOIN products p ON obc.product_id = p.product_id
GROUP BY customer_name
)
SELECT obc.customer_name, obc.order_id, obc.order_date, p.product_name, obc.quantity, sbc.total_sales
FROM orders_by_customer obc
JOIN sales_by_customer sbc ON obc.customer_name = sbc.customer_name
JOIN products p ON obc.product_id = p.product_id
ORDER BY obc.customer_name, obc.order_id;

/*
En la primera CTE, orders_by_customer, unimos las tablas customers, orders y order_items y
seleccionamos las columnas que necesitamos para calcular el total de ventas por cliente.

En la segunda CTE, sales_by_customer, calculamos el total de ventas por cliente a partir de la primera CTE.

Finalmente, unimos las dos CTEs en la consulta principal y seleccionamos las columnas
que queremos mostrar en el resultado final.

Este es solo un ejemplo de cómo se pueden utilizar múltiples CTEs en una consulta. Las
posibilidades son prácticamente infinitas, y solo están limitadas por la creatividad y
la lógica de quien las escribe.

*/

/*
Queremos calcular la participación de cada producto en las ventas totales de cada cliente.
Para ello, primero necesitamos calcular el total de ventas de cada cliente. Luego, podemos
unir esta información con la tabla order_items para obtener el precio total de cada producto
vendido por cada cliente. Finalmente, podemos calcular la participación de cada producto
dividiendo su precio total por el total de ventas del cliente.
*/

WITH orders_by_customer AS (
SELECT c.customer_name, o.order_id, o.order_date, oi.product_id, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
),
sales_by_customer AS (
SELECT customer_name, SUM(product_price * quantity) AS total_sales
FROM orders_by_customer obc
JOIN products p ON obc.product_id = p.product_id
GROUP BY customer_name
),
total_sales AS (
SELECT SUM(total_sales) AS sum_total_sales
FROM sales_by_customer
)
SELECT obc.customer_name, obc.order_id, obc.order_date, p.product_name, obc.quantity, sbc.total_sales,
ROUND(sbc.total_sales / ts.sum_total_sales * 100, 2) AS participacion_porcentaje,
ROUND(sbc.total_sales / ts.sum_total_sales, 2) AS participacion_venta_promedio
FROM orders_by_customer obc
JOIN sales_by_customer sbc ON obc.customer_name = sbc.customer_name
JOIN products p ON obc.product_id = p.product_id
CROSS JOIN total_sales ts
ORDER BY obc.customer_name, obc.order_id;