gistfile1.txt

select -- 5. Summarize
aoh.location_id,
count(clks.paid_cpc_cents_usd) as clicks,
count(aoh.gbv_usd) as conversions,
cast(count(aoh.gbv_usd)as decimal)/count(clks.paid_cpc_cents_usd)as cvr,
avg(aoh.gbv_usd) as avg_gbv_usd,
avg(clks.paid_cpc_cents_usd) as avg_spend
FROM
	(select -- 3. Pull conversion stats
	cc_provider,
	cc_click_type,
	cc_cpc,
	gbv,
	gbv_usd,
	currency, -- not important
	location_id,
	cc_referring_servlet, -- not important
	file_key
	from anm.orders_hourly
	where
	conversion_date >='2017-01-01'and conversion_date <='2017-05-28'
	and cc_click_type='DM_Hotel'
	and gbv_usd IS NOT NULL

	and location_id IN --2. Filter out BCOM inv with no overlap with Wotif's
		(select location_id
		from
		(select distinct location_id 
		from a_commerce_location_info 
		where provider_name='BookingCom' 
		and ds>='2017-01-01' and ds<='2017-05-28' 
		and location_id is not null
		and country='Australia') bcom
		where
		location_id
		NOT IN
		(select distinct location_id -- 1. Find distinct Wotif properties
		from a_commerce_location_info 
		where provider_name='WotifCom' 
		and ds>='2017-01-01' and ds<='2017-05-28' 
		and location_id is not null
		and country='Australia')
		)
	) aoh
left outer join sst.commerce_clicks clks -- 4. Match conv stats to click stats
on clks.click_file_key=aoh.file_key
and clks.location_id=aoh.location_id
and clks.ds>='2017-01-01' and clks.ds<='2017-05-28'
GROUP BY 1 -- aoh.location_id