gistfile1.txt

 WITH high_impact_hosts AS (
            SELECT
                host_id,
                template_id,
                status,
                SUM(q.duration) / SUM(q.host_task_count) AS average_duration_per_task,
                MAX(created_date) AS created_date,
                MAX(task_timestamp_max) AS task_timestamp_max
            FROM "monthly_host_explorer_rollup" q
            
            WHERE "q".created_date >= ('2021-10-01'::date)::date
            AND "q".created_date <= ('2021-10-01'::date)::date
        AND "q"."type" = ANY(ARRAY['job']) 
        AND "q"."status" = ANY(ARRAY['successful']) 
            GROUP BY q.template_id, q.host_id, q.status
            ORDER BY SUM(q.duration) / SUM(q.host_task_count) DESC, MAX(q.task_timestamp_max) DESC


        ),
        high_impact_host_count AS (
            SELECT COUNT(*) AS host_count_in_template

            from high_impact_hosts
            GROUP BY template_id
            ORDER BY MAX(average_duration_per_task) DESC
        )
        SELECT * FROM high_impact_host_count
        WHERE host_count_in_template > 1;