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

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

-- se calculan los avg (profit) por provider_id
WITH ProviderAverages AS (
    SELECT 
        provider_id,
        AVG(profit) AS avg_profit,
        AVG(roi) AS avg_roi,
        MAX(last_update) AS max_date
    FROM provider_behaivor_history_month
    GROUP BY provider_id
),

-- se calculan los percentiles
Percentiles AS (
    SELECT 
        *,
	-- percentil para profit
	PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_profit) OVER () as p_profit_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_profit) OVER () as p_profit_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_profit) OVER () as p_profit_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_profit) OVER () as p_profit_80,
	
	-- percentil para roi
	PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_roi) OVER () as p_roi_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_roi) OVER () as p_roi_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_roi) OVER () as p_roi_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_roi) OVER () as p_roi_80
    FROM ProviderAverages
)
SELECT 
    provider_id,
    avg_profit,
    CASE 
        WHEN avg_profit <= p_profit_20 THEN 1
        WHEN avg_profit <= p_profit_40 THEN 2
        WHEN avg_profit <= p_profit_60 THEN 3
        WHEN avg_profit <= p_profit_80 THEN 4
        ELSE 5 
    END AS profit_star,
    avg_roi,
    CASE 
        WHEN avg_roi <= p_roi_20 THEN 1
        WHEN avg_roi <= p_roi_40 THEN 2
        WHEN avg_roi <= p_roi_60 THEN 3
        WHEN avg_roi <= p_roi_80 THEN 4
        ELSE 5 
    END AS roi_star,
    max_date
FROM Percentiles;