-- Vista 1: Cantidad de ventas por libros.
CREATE OR REPLACE VIEW ventas_de_libros AS
(SELECT Fk_SKU, COUNT(*) AS N_Ventas
FROM Ventas
GROUP BY Fk_SKU 
HAVING Fk_SKU IN (SELECT Id_SKU_Libro FROM Libros));

-- Vista 2: Cantidad de ventas de vinilos.
CREATE OR REPLACE VIEW ventas_de_vinilos AS
(SELECT Fk_SKU, COUNT(*) AS N_Ventas
FROM Ventas
GROUP BY Fk_SKU 
HAVING Fk_SKU IN (SELECT Id_SKU_Vinilo FROM Vinilos));

-- Vista 3: Cantidad de ventas de revistas.
CREATE OR REPLACE VIEW ventas_de_revistas AS
(SELECT Fk_SKU, COUNT(*) AS N_Ventas
FROM Ventas
GROUP BY Fk_SKU
HAVING Fk_SKU IN (SELECT Id_SKU_Revista FROM Revistas));

-- Vista 4: Top 10 Autores de libros con más ventas
CREATE OR REPLACE VIEW top_10_ventas_autores_libros AS
(SELECT Fk_SKU, nombre, apellido, COUNT(*) AS cantidad_ventas
FROM ventas
INNER JOIN (
    SELECT autores.nombre AS nombre, autores.apellido AS apellido, libros.Id_SKU_Libro AS SKU
    FROM autores
    INNER JOIN libros ON autores.Id_Autor = libros.Fk_Autor
) AS sku_autores ON Fk_SKU = sku_autores.SKU
GROUP BY Fk_SKU, nombre, apellido
ORDER BY cantidad_ventas DESC
LIMIT 10);

-- Vista 5: Productos sin ventas
CREATE OR REPLACE VIEW sin_ventas AS
(SELECT Id_SKU, stock
FROM productos
WHERE Id_SKU NOT IN (
SELECT DISTINCT Fk_SKU
FROM Ventas));





;