Query05.sql

-----------------------------------------
-- 5. CASO PRACTICO
-----------------------------------------

-- ANALISIS EXPLORATORIO
CREATE OR REPLACE VIEW BENEF_MARGE_REG AS
SELECT
    A.REGION,
    ROUND(SUM(S.PROFIT), 2)             AS TOTAL_BENEFICIO,
    ROUND(MIN(S.PROFIT), 2)             AS BENEFICIO_MINIMO,
    ROUND(MAX(S.PROFIT), 2)             AS BENEFICIO_MAXIMO,
    ROUND(AVG(S.PROFIT), 2)             AS BENEFICIO_PROMEDIO,
    ROUND(STDDEV(S.PROFIT), 2)          AS DESVIACION_BENEFICIO,
    ROUND(AVG(CASE 
                  WHEN S.TOTAL = 0 THEN NULL 
                  ELSE (S.PROFIT / S.TOTAL) * 100 
              END), 2)                  AS PROMEDIO_MARGEN_PORC
FROM SALES S 	JOIN ACCOUNTS A	    
ON S.ACCOUNT = A.ACCOUNT
GROUP BY A.REGION
ORDER BY TOTAL_BENEFICIO DESC;