Production Search -- 9875ms

gistfile1.sql

SELECT  agent_id,  
	total_sales_rank, 
	total_sales_count, 
	city_sales_rank, 
	city_sales_count, 
	((10*total_sales_rank + 15*city_sales_rank)/cast(25 as float) * 100) as hl_score 
FROM (
	SELECT  agents.id as agent_id,
		percent_rank() over (order by coalesce(sum(agent_metrics.actual_sold_transactions), 0) asc) as total_sales_rank, 
		coalesce(sum(agent_metrics.actual_sold_transactions), 0) as total_sales_count, 
		percent_rank() over (order by coalesce(sum(agent_city_metrics.actual_sold_transactions), 0) asc) as city_sales_rank, 
		coalesce(sum(agent_city_metrics.actual_sold_transactions), 0) as city_sales_count 
	FROM "agents" 
	LEFT OUTER JOIN agent_metrics
		ON agent_metrics.agent_id = agents.id
		AND agent_metrics.acting_for = 'buyer'
        LEFT OUTER JOIN agent_city_metrics
		ON agent_city_metrics.agent_id = agents.id
		AND agent_city_metrics.city_id = 220
		AND agent_city_metrics.acting_for = 'buyer' 
	WHERE "agents"."hide_from_search" = 'f' 
	GROUP BY agents.id
) scores ORDER BY hl_score desc LIMIT 15