Agent Availability Factor

gistfile1.sql

SELECT
	agents.*, agents.id as core_agent_id,
	(2*buyer_factors.buyer_estimated_current_sales + seller_factors.seller_estimated_current_sales) as availability_factor,
	buyer_factors.buyer_estimated_current_sales, 
	seller_factors.seller_estimated_current_sales 
FROM 
	(SELECT agent_id,
        case
          when last_year_q3_total_sales / cast ( nullif(sold_transactions_2011, 0) as float) <= 0.2
            then buyer_estimates.sales_change_weighted_average*sold_transactions_2011/4.0
          else buyer_estimates.sales_change_weighted_average*last_year_q3_total_sales
        end as buyer_estimated_current_sales, 
        buyer_estimates.sales_change_weighted_average as buyer_sales_change_weighted_average
	FROM 
		(SELECT agent_id, last_year_q3_total_sales, sold_transactions_2011,
			(sales_change_2009_2010 + sales_change_2010_2011*2) / 3.0 as sales_change_weighted_average
		FROM 
			(SELECT agent_id, last_year_q3_total_sales, sold_transactions_2011,
			    case
			      when sold_transactions_2009 = 0
				then least(sold_transactions_2010, 3)
			      else least((sold_transactions_2010 / cast ( nullif(sold_transactions_2009, 0) as float)), 3)
			    end as sales_change_2009_2010, 
			    case
			      when sold_transactions_2010 = 0
				then least(sold_transactions_2011, 3)
			      else least((sold_transactions_2011 / cast ( nullif(sold_transactions_2010, 0) as float)), 3)
			    end as sales_change_2010_2011
			FROM 
			  "agent_metrics" where agent_metrics.agent_id < 86000 and agent_metrics.agent_id > 85000 and agent_metrics.acting_for = 'buyer'
			) buyer_weights
		) buyer_estimates
	) buyer_factors,
	(SELECT 
	case
	  when last_year_q3_total_sales / cast ( nullif(sold_transactions_2011, 0) as float) <= 0.2
	    then seller_estimates.sales_change_weighted_average*sold_transactions_2011/4.0
	  else seller_estimates.sales_change_weighted_average*last_year_q3_total_sales
	end as seller_estimated_current_sales,
	seller_estimates.sales_change_weighted_average as seller_sales_change_weighted_average
	FROM 
		(SELECT agent_id, last_year_q3_total_sales, sold_transactions_2011,
			(sales_change_2009_2010 + sales_change_2010_2011*2) / 3.0 as sales_change_weighted_average 
		FROM 
			(SELECT agent_id, last_year_q3_total_sales, sold_transactions_2011,
			    case
			      when sold_transactions_2009 = 0
				then least(sold_transactions_2010, 3)
			      else least((sold_transactions_2010 / cast ( nullif(sold_transactions_2009, 0) as float)), 3)
			    end as sales_change_2009_2010, 
			    case
			      when sold_transactions_2010 = 0
				then least(sold_transactions_2011, 3)
			      else least((sold_transactions_2011 / cast ( nullif(sold_transactions_2010, 0) as float)), 3)
			    end as sales_change_2010_2011 
			FROM 
			"agent_metrics" WHERE agent_metrics.agent_id < 86000 and agent_metrics.agent_id > 85000 and agent_metrics.acting_for = 'seller'
			) seller_weights
		) seller_estimates
) seller_factors, agents WHERE agents.id < 86000 and agents.id > 85000