-- limpieza de la tabla resumen antes de actualizar
TRUNCATE TABLE customer_behaivor;

-- se prepara los campos para insertar los datos
INSERT INTO customer_behaivor (
    customer_id, 
    profit, 
    profit_star, 
    roi, 
    roi_star, 
    last_update
)

-- se calculan los avg (profit) por customer_id
WITH CustomerAverages AS (
    -- Obtenemos promedios históricos
    SELECT 
        customer_id,
        AVG(profit) AS avg_profit,
        AVG(roi) AS avg_roi,
        MAX(last_update) AS max_date
    FROM customer_behaivor_history_month
    GROUP BY customer_id
),

-- se calculan los percentiles
Percentiles AS (
    SELECT 
        *,
        -- percentil para profit
	PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_profit) OVER () as c_profit_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_profit) OVER () as c_profit_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_profit) OVER () as c_profit_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_profit) OVER () as c_profit_80,

        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_roi) OVER () as c_roi_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_roi) OVER () as c_roi_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_roi) OVER () as c_roi_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_roi) OVER () as c_roi_80
    FROM CustomerAverages
)
SELECT 
    customer_id,
    avg_profit,
    CASE 
        WHEN avg_profit <= c_profit_20 THEN 1
        WHEN avg_profit <= c_profit_40 THEN 2
        WHEN avg_profit <= c_profit_60 THEN 3
        WHEN avg_profit <= c_profit_80 THEN 4
        ELSE 5 
    END AS profit_star,
    avg_roi,
    CASE 
        WHEN avg_roi <= c_roi_20 THEN 1
        WHEN avg_roi <= c_roi_40 THEN 2
        WHEN avg_roi <= c_roi_60 THEN 3
        WHEN avg_roi <= c_roi_80 THEN 4
        ELSE 5 
    END AS roi_star,
    max_date
FROM Percentiles;