Spend to hour per day versus Interactions per hour per day.

page_view.sql

SELECT spend_to_hour.date, spend_to_hour.hour_of_day,
spend_to_hour.interactions_per_hour, count(itx1.id) as interactions_to_hour, spend_to_hour.spend_to_hour,
spend_to_hour.spend_to_hour/count(itx1.id) as cost_per_engagement
FROM
	(SELECT itx.date, itx.hour_of_day, itx.interactions_per_hour,
	sum(amplifyprod.bidder_report_aggregates.our_internal_media_cost) as spend_to_hour
	FROM (SELECT date(amplifyprod.interactions.timestamp) as date,
		hour(amplifyprod.interactions.timestamp) as hour_of_day,
		count(distinct amplifyprod.interactions.id) as interactions_per_hour
		FROM amplifyprod.interactions
		WHERE amplifyprod.interactions.label = 'page-view'
		GROUP BY date, hour_of_day) as itx
	JOIN amplifyprod.bidder_report_aggregates
		ON date(amplifyprod.bidder_report_aggregates.start_time) = itx.date AND
		hour(amplifyprod.bidder_report_aggregates.end_time) <= itx.hour_of_day
	GROUP BY itx.date, itx.hour_of_day) AS spend_to_hour
JOIN amplifyprod.interactions as itx1
	ON date(itx1.timestamp) = spend_to_hour.date AND
	hour(itx1.timestamp) <= spend_to_hour.hour_of_day AND
	itx1.label = 'page-view'
GROUP BY spend_to_hour.date, spend_to_hour.hour_of_day,
spend_to_hour.interactions_per_hour, spend_to_hour.spend_to_hour