select
            sq.id_segment,
            COALESCE(sq.fix_sum_m1, 0) as fix_sum_m1,
            COALESCE(sq.fix_sum_m2, 0) as fix_sum_m2,
            COALESCE(sq.fix_sum_m3, 0) as fix_sum_m3,
            COALESCE(sq.delta_sum_m1, 0) as delta_sum_m1,
            COALESCE(sq.delta_sum_m2, 0) as delta_sum_m2,
            COALESCE(sq.delta_sum_m3, 0) as delta_sum_m3,
            COALESCE(sq.hw_sum_m1, 0) as hw_sum_m1,
            COALESCE(sq.hw_sum_m2, 0) as hw_sum_m2,
            COALESCE(sq.hw_sum_m3, 0) as hw_sum_m3,
            COALESCE(sq.kk_abs_sum_m1, 0) as kk_abs_sum_m1,
            COALESCE(sq.kk_abs_sum_m2, 0) as kk_abs_sum_m2,
            COALESCE(sq.kk_abs_sum_m3, 0) as kk_abs_sum_m3,
            COALESCE(sq.kk_mtm_sum_m1, 0) as kk_mtm_sum_m1,
            COALESCE(sq.kk_mtm_sum_m2, 0) as kk_mtm_sum_m2,
            COALESCE(sq.kk_mtm_sum_m3, 0) as kk_mtm_sum_m3,
            rank() OVER (ORDER BY sq.fix_sum_m1 DESC) as fix_rank_m1,
            rank() OVER (ORDER BY sq.fix_sum_m2 DESC) as fix_rank_m2,
            rank() OVER (ORDER BY sq.fix_sum_m3 DESC) as fix_rank_m3,
            rank() OVER (ORDER BY sq.delta_sum_m1 DESC) as delta_rank_m1,
            rank() OVER (ORDER BY sq.delta_sum_m2 DESC) as delta_rank_m2,
            rank() OVER (ORDER BY sq.delta_sum_m3 DESC) as delta_rank_m3,
            rank() OVER (ORDER BY sq.hw_sum_m1 DESC) as hw_rank_m1,
            rank() OVER (ORDER BY sq.hw_sum_m2 DESC) as hw_rank_m2,
            rank() OVER (ORDER BY sq.hw_sum_m3 DESC) as hw_rank_m3,
            rank() OVER (ORDER BY sq.fix_sum_m1 DESC) * 0.3 +
            rank() OVER (ORDER BY sq.delta_sum_m1 DESC) * 0.2 +
            rank() OVER (ORDER BY sq.hw_sum_m1 DESC) * 0.5 as final_rank_m1,
            rank() OVER (ORDER BY sq.fix_sum_m2 DESC) * 0.3 +
            rank() OVER (ORDER BY sq.delta_sum_m2 DESC) * 0.2 +
            rank() OVER (ORDER BY sq.hw_sum_m2 DESC) * 0.5 as final_rank_m2,
            rank() OVER (ORDER BY sq.fix_sum_m3 DESC) * 0.3 +
            rank() OVER (ORDER BY sq.delta_sum_m3 DESC) * 0.2 +
            rank() OVER (ORDER BY sq.hw_sum_m3 DESC) * 0.5 as final_rank_m3,
            (
                rank() OVER (ORDER BY sq.fix_sum_m1 DESC) * 0.3 +
                rank() OVER (ORDER BY sq.delta_sum_m1 DESC) * 0.2 +
                rank() OVER (ORDER BY sq.hw_sum_m1 DESC) * 0.5 +
                rank() OVER (ORDER BY sq.fix_sum_m2 DESC) * 0.3 +
                rank() OVER (ORDER BY sq.delta_sum_m2 DESC) * 0.2 +
                rank() OVER (ORDER BY sq.hw_sum_m2 DESC) * 0.5 +
                rank() OVER (ORDER BY sq.fix_sum_m3 DESC) * 0.3 +
                rank() OVER (ORDER BY sq.delta_sum_m3 DESC) * 0.2 +
                rank() OVER (ORDER BY sq.hw_sum_m3 DESC) * 0.5
            ) / 3 as final_rank
            FROM
                (SELECT
                    main_query.id_segment,
                    AVG(main_query.fix_sum_m1) AS fix_sum_m1,
                    AVG(main_query.fix_sum_m2) AS fix_sum_m2,
                    AVG(main_query.fix_sum_m3) AS fix_sum_m3,
                    AVG(main_query.delta_sum_m1) AS delta_sum_m1,
                    AVG(main_query.delta_sum_m2) AS delta_sum_m2,
                    AVG(main_query.delta_sum_m3) AS delta_sum_m3,
                    AVG(main_query.hw_sum_m1) AS hw_sum_m1,
                    AVG(main_query.hw_sum_m2) AS hw_sum_m2,
                    AVG(main_query.hw_sum_m3) AS hw_sum_m3,
                    AVG(main_query.kk_abs_sum_m1) AS kk_abs_sum_m1,
                    AVG(main_query.kk_abs_sum_m2) AS kk_abs_sum_m2,
                    AVG(main_query.kk_abs_sum_m3) AS kk_abs_sum_m3,
                    AVG(main_query.kk_mtm_sum_m1) AS kk_mtm_sum_m1,
                    AVG(main_query.kk_mtm_sum_m2) AS kk_mtm_sum_m2,
                    AVG(main_query.kk_mtm_sum_m3) AS kk_mtm_sum_m3
                    from
                    (select
                        jq2.id_segment,
                        COALESCE(sum(CASE WHEN data_type = 'fix' and contest_data.contest_month = 1 THEN price END) / coalesce(MIN(headcount_m1), 1), 0) as fix_sum_m1,
                        COALESCE(sum(CASE WHEN data_type = 'fix' and contest_data.contest_month = 2 THEN price END) / coalesce(MIN(headcount_m2), 1), 0) as fix_sum_m2,
                        COALESCE(sum(CASE WHEN data_type = 'fix' and contest_data.contest_month = 3 THEN price END) / coalesce(MIN(headcount_m3), 1), 0) as fix_sum_m3,
                        COALESCE(sum(CASE WHEN data_type = 'delta' and contest_data.contest_month = 1 THEN price END) / coalesce(MIN(headcount_m1), 1), 0) as delta_sum_m1,
                        COALESCE(sum(CASE WHEN data_type = 'delta' and contest_data.contest_month = 2 THEN price END) / coalesce(MIN(headcount_m2), 1), 0) as delta_sum_m2,
                        COALESCE(sum(CASE WHEN data_type = 'delta' and contest_data.contest_month = 3 THEN price END) / coalesce(MIN(headcount_m3), 1), 0) as delta_sum_m3,
                        COALESCE(sum(CASE WHEN data_type = 'hw' and contest_data.contest_month = 1 and contest_sap.id_contest_sap is not null THEN (CASE WHEN price < 4949 or po = '800790' THEN PRICE ELSE price * 5 END) END) / coalesce(MIN(headcount_m1), 1), 0) as hw_sum_m1,
                        COALESCE(sum(CASE WHEN data_type = 'hw' and contest_data.contest_month = 2 and contest_sap.id_contest_sap is not null THEN (CASE WHEN price < 4949 or po = '800790' THEN PRICE ELSE price * 5 END) END) / coalesce(MIN(headcount_m2), 1), 0) as hw_sum_m2,
                        COALESCE(sum(CASE WHEN data_type = 'hw' and contest_data.contest_month = 3 and contest_sap.id_contest_sap is not null THEN (CASE WHEN price < 4949 or po = '800790' THEN PRICE ELSE price * 5 END) END) / coalesce(MIN(headcount_m3), 1), 0) as hw_sum_m3,
                        COALESCE(sum(CASE WHEN data_type = 'kk_abs' and contest_data.contest_month = 1 THEN price END) / coalesce(MIN(headcount_m1), 1), 0) as kk_abs_sum_m1,
                        COALESCE(sum(CASE WHEN data_type = 'kk_abs' and contest_data.contest_month = 2 THEN price END) / coalesce(MIN(headcount_m3), 1), 0) as kk_abs_sum_m2,
                        COALESCE(sum(CASE WHEN data_type = 'kk_abs' and contest_data.contest_month = 3 THEN price END) / coalesce(MIN(headcount_m1), 1), 0) as kk_abs_sum_m3,
                        COALESCE(sum(CASE WHEN data_type = 'kk_mtm' and contest_data.contest_month = 1 THEN price END) / coalesce(MIN(headcount_m1), 1), 0) as kk_mtm_sum_m1,
                        COALESCE(sum(CASE WHEN data_type = 'kk_mtm' and contest_data.contest_month = 2 THEN price END) / coalesce(MIN(headcount_m3), 1), 0) as kk_mtm_sum_m2,
                        COALESCE(sum(CASE WHEN data_type = 'kk_mtm' and contest_data.contest_month = 3 THEN price END) / coalesce(MIN(headcount_m1), 1), 0) as kk_mtm_sum_m3
                        from contest_data
                        left join contest_sap on contest_data.contest_month = contest_sap.contest_month and contest_data.po = contest_sap.sap_code
                        left join (
                            select
                            MIN(id_segment) as id_segment,
                            sum(CASE WHEN contest_headcount.contest_month = 1 THEN headcount END) as headcount_m1,
                            sum(CASE WHEN contest_headcount.contest_month = 2 THEN headcount END) as headcount_m2,
                            sum(CASE WHEN contest_headcount.contest_month = 3 THEN headcount END) as headcount_m3,
                            id_outlet_team
                            from contest_microsegment
                            left join contest_headcount using(id_outlet_team)
                            WHERE segment_type = 'jam'
                            group by id_outlet_team
                        ) jq2 using(id_outlet_team)
                        where id_channels IN (1, 2)
                        and id_segment is not null
                        group by jq2.id_segment, id_outlet_team) main_query
                        group by id_segment) sq
                order by final_rank ASC