Cacher is the code snippet organizer for pro developers

select main_contracts.*
	, sum(addons.total_fee_conversion_fee__c) as add_on_fees
from 
(select
    o.name,
    o.accountid,
    o.id,
    o.deal_type__c,
    o.contract_start_date__c,
    o.contract_end_date__c,
    case when o.deal_type__c ilike 'trial' then 'Trial'
      when o.deal_type__c ilike 'pilot' then 'Pilot'
      when o.deal_type__c is null then null
      else 'Subscription'
    end as contract_type,
    o.total_fee_conversion_fee__c,
    o.type,
    count(*) as add_ons
  from
    stitch_sfdc.sf_opportunity o --contracts
  where o.stagename = 'Closed Won'
    and o.isdeleted = FALSE
    and o.contract_start_date__c is not null
    and o.contract_end_date__c is not null
    and o.hire_type__c is null
    and o.type <> 'Add On'
  group by 1,2,3,4,5,6,7,8,9 
) as main_contracts
left join 
	stitch_sfdc.sf_opportunity addons --add-ons
  	on addons.contract_start_date__c >= main_contracts.contract_start_date__c
  	and addons.contract_end_date__c <= main_contracts.contract_end_date__c + interval '1 day'
  	and addons.accountid = main_contracts.accountid
	and addons.stagename = 'Closed Won'
    and addons.isdeleted = FALSE
    and addons.contract_start_date__c is not null
    and addons.contract_end_date__c is not null
    and addons.hire_type__c is null
    and addons.type = 'Add On' 	
group by 1,2,3,4,5,6,7,8,9,10