-- Chinook (tienda de música digital) — análisis de negocio en SQL
-- Cada bloque empieza con "-- name: <id>" para que run_chinook.py lo ejecute por separado.

-- name: paises
-- Ingresos, clientes y ticket medio por país (top 10)
SELECT c.Country                                  AS pais,
       COUNT(DISTINCT c.CustomerId)               AS clientes,
       COUNT(i.InvoiceId)                         AS facturas,
       ROUND(SUM(i.Total), 2)                     AS ingresos,
       ROUND(SUM(i.Total) / COUNT(i.InvoiceId), 2) AS ticket_medio
FROM Customer c
JOIN Invoice i ON i.CustomerId = c.CustomerId
GROUP BY c.Country
ORDER BY ingresos DESC
LIMIT 10;

-- name: mensual
-- Ingresos por mes con acumulado anual y variación interanual (funciones ventana)
WITH m AS (
    SELECT strftime('%Y', InvoiceDate) AS anio,
           strftime('%m', InvoiceDate) AS mes,
           SUM(Total)                  AS ingresos
    FROM Invoice
    GROUP BY anio, mes
)
SELECT anio, mes,
       ROUND(ingresos, 2) AS ingresos,
       ROUND(SUM(ingresos) OVER (PARTITION BY anio ORDER BY mes), 2) AS acumulado_anio,
       ROUND(100.0 * (ingresos - LAG(ingresos, 12) OVER (ORDER BY anio, mes))
             / LAG(ingresos, 12) OVER (ORDER BY anio, mes), 1)       AS var_interanual_pct
FROM m
ORDER BY anio, mes;

-- name: generos
-- Cuota de ingresos por género musical
SELECT g.Name                                                     AS genero,
       ROUND(SUM(il.UnitPrice * il.Quantity), 2)                  AS ingresos,
       ROUND(100.0 * SUM(il.UnitPrice * il.Quantity)
             / SUM(SUM(il.UnitPrice * il.Quantity)) OVER (), 1)   AS cuota_pct
FROM InvoiceLine il
JOIN Track t ON t.TrackId = il.TrackId
JOIN Genre g ON g.GenreId = t.GenreId
GROUP BY g.Name
ORDER BY ingresos DESC
LIMIT 8;

-- name: segmentos
-- Segmentación de clientes por gasto total en cuartiles (NTILE)
WITH gasto AS (
    SELECT c.CustomerId, SUM(i.Total) AS total, COUNT(i.InvoiceId) AS compras
    FROM Customer c
    JOIN Invoice i ON i.CustomerId = c.CustomerId
    GROUP BY c.CustomerId
),
q AS (
    SELECT *, NTILE(4) OVER (ORDER BY total DESC) AS cuartil FROM gasto
)
SELECT cuartil,
       COUNT(*)                                             AS clientes,
       ROUND(AVG(total), 2)                                 AS gasto_medio,
       ROUND(100.0 * SUM(total) / (SELECT SUM(total) FROM gasto), 1) AS cuota_ingresos_pct
FROM q
GROUP BY cuartil
ORDER BY cuartil;

-- name: genero_top_pais
-- Género más vendido en cada país (ROW_NUMBER por partición)
WITH ventas AS (
    SELECT c.Country AS pais, g.Name AS genero, SUM(il.Quantity) AS unidades
    FROM InvoiceLine il
    JOIN Invoice  i ON i.InvoiceId  = il.InvoiceId
    JOIN Customer c ON c.CustomerId = i.CustomerId
    JOIN Track    t ON t.TrackId    = il.TrackId
    JOIN Genre    g ON g.GenreId    = t.GenreId
    GROUP BY c.Country, g.Name
),
r AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY pais ORDER BY unidades DESC, genero) AS rn
    FROM ventas
)
SELECT pais, genero, unidades
FROM r
WHERE rn = 1
ORDER BY unidades DESC
LIMIT 10;

-- name: comerciales
-- Rendimiento de cada agente de ventas: clientes, ingresos y % sobre el total
SELECT e.FirstName || ' ' || e.LastName AS agente,
       COUNT(DISTINCT c.CustomerId)     AS clientes,
       ROUND(SUM(i.Total), 2)           AS ingresos,
       ROUND(100.0 * SUM(i.Total) / (SELECT SUM(Total) FROM Invoice), 1) AS cuota_pct
FROM Employee e
JOIN Customer c ON c.SupportRepId = e.EmployeeId
JOIN Invoice  i ON i.CustomerId   = c.CustomerId
GROUP BY e.EmployeeId
ORDER BY ingresos DESC;