TRUNCATE TABLE item_provider_behaivor_history;

INSERT INTO item_provider_behaivor_history 
    (provider_id, item_id, profit, profit_star, roi, roi_star, date)
WITH Averages AS (

    SELECT 
        provider_id,
        item_id,
        AVG(profit) AS avg_profit,
        AVG(roi) AS avg_roi,
        MAX(date) AS last_date
    FROM purchase_behaivor_history
    GROUP BY provider_id, item_id
),
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 Averages
)
SELECT 
    provider_id,
    item_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,
    last_date
FROM Percentiles;