gistfile1.sql

SELECT core_agent_id, agents.*, 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.*, agents.id as core_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' GROUP BY agents.id) scores, agents WHERE (agents.id = core_agent_id) ORDER BY hl_score desc LIMIT 15