create_v_dynamic_pricing_for_account.sql

CREATE VIEW v_dynamic_pricing_for_accounts AS
SELECT
  dynamic_pricing_bid_infos.account_id,
  case 
    when 'red' = ANY(array_agg(dynamic_pricing_bid_infos.exclusive_lead_status))
    then 'red'
    when 'yellow' = ANY(array_agg(dynamic_pricing_bid_infos.exclusive_lead_status))
    then 'yellow'
    else 'green'
  end exclusive_lead_status,
  case 
    when 'red' = ANY(array_agg(dynamic_pricing_bid_infos.call_status))
    then 'red'
    when 'yellow' = ANY(array_agg(dynamic_pricing_bid_infos.call_status))
    then 'yellow'
    else 'green'
  end call_status,
    array_agg(dynamic_pricing_bid_infos.profile_id) profile_ids
  sum(case when accepts_exclusive_leads then 1 else 0 end) > 0 accepts_exclusive_leads,
  sum(case when accepts_calls then 1 else 0 end) > 0 accepts_calls
FROM dynamic_pricing_bid_infos
GROUP BY dynamic_pricing_bid_infos.account_id