De SQL a Dashboard: Transformando Datos en Decisiones

-- Creación de Tablas
use tienda;
CREATE TABLE productos (
    id_producto SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    categoria VARCHAR(50),
    precio DECIMAL(10,2),
    stock INT
);
CREATE TABLE categorias (
    id_categoria SERIAL PRIMARY KEY,
    nombre VARCHAR(50),
    descripcion TEXT
);
CREATE TABLE proveedores (
    id_proveedor SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    contacto VARCHAR(100),
    telefono VARCHAR(20),
    ciudad VARCHAR(50)
);
CREATE TABLE empleados (
    id_empleado SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    cargo VARCHAR(50),
    correo VARCHAR(100),
    fecha_ingreso DATE
);
CREATE TABLE ventas (
    id_venta SERIAL PRIMARY KEY,
    id_cliente INT REFERENCES clientes(id_cliente),
    id_empleado INT REFERENCES empleados(id_empleado),
    fecha DATE,
    total DECIMAL(10,2)
);
CREATE TABLE detalle_venta (
    id_detalle SERIAL PRIMARY KEY,
    id_venta INT REFERENCES ventas(id_venta),
    id_producto INT REFERENCES productos(id_producto),
    cantidad INT,
    precio_unitario DECIMAL(10,2)
);
CREATE TABLE devoluciones (
    id_devolucion SERIAL PRIMARY KEY,
    id_venta INT REFERENCES ventas(id_venta),
    fecha DATE,
    motivo TEXT
);
CREATE TABLE pagos (
    id_pago SERIAL PRIMARY KEY,
    id_cliente INT REFERENCES clientes(id_cliente),
    fecha DATE,
    monto DECIMAL(10,2),
    metodo_pago VARCHAR(50)
);
-- Consultas 
-- Total Ventas por año y mes
SELECT 
EXTRACT(YEAR FROM fecha) AS año, EXTRACT(MONTH FROM fecha) AS mes, SUM(total) AS Total_Ventas
FROM ventas
GROUP BY año,mes
ORDER BY año,mes;

-- Top 10 prodcutos mas Vendidos
SELECT p.nombre, SUM(dv.cantidad) AS Cantidad_Vendida FROM productos p
JOIN detalle_venta dv ON dv.id_producto = p.id_producto
GROUP BY p.id_producto, p.nombre
ORDER BY SUM(dv.cantidad) Desc
LIMIT 10;

-- Categorias con mayor ingreso
SELECT p.categoria, SUM(dv.cantidad * dv.precio_unitario) AS Ingreso_Total, COUNT(p.categoria) FROM productos p
JOIN detalle_venta dv ON dv.id_producto = p.id_producto
GROUP BY p.categoria
ORDER BY ingreso_total Desc;

-- Clientes con mas ventas 
SELECT cl.nombre As "Nombre Cliente" , SUM(v.total) As ventas_Totales, COUNT(v.id_cliente) As Cantidad_ventas 
FROM clientes cl
JOIN ventas v ON v.id_cliente = cl.id_cliente
GROUP BY cl.id_cliente
ORDER BY   Cantidad_ventas Desc
LIMIT 10;

-- Total mensual de ingresos (Ventas - Devoluciones)
SELECT 
    EXTRACT(YEAR FROM v.fecha) AS año,
    EXTRACT(MONTH FROM v.fecha) AS mes,
    SUM(v.total) - COALESCE(SUM(dv.total), 0) AS ingresos_netos
FROM ventas v
LEFT JOIN devoluciones d ON v.id_venta = d.id_venta
LEFT JOIN ventas dv ON d.id_venta = dv.id_venta
GROUP BY año, mes
ORDER BY año, mes;

-- Empleados con mas ventas
SELECT e.nombre, SUM(v.total) FROM empleados e
JOIN ventas v ON v.id_empleado = e.id_empleado
GROUP BY v.id_empleado
ORDER BY v.total Desc;

-- Ventas por mes y por empleado
SELECT EXTRACT(YEAR FROM v.fecha) As Año, EXTRACT(MONTH FROM v.fecha) As Mes, e.nombre, SUM(v.total) 
FROM empleados e
JOIN ventas v ON e.id_empleado = v.id_empleado
GROUP BY Año, Mes, e.id_empleado, e.nombre
ORDER BY Año, Mes, v.total Desc;