gistfile1.sql

SELECT
  category,
  segment,
  SUM(exposures) AS exposures,
  SUM(impressions)/(SUM(exposures) - SUM(indeterminate_visibility)) AS impression_rate,
  SUM(engagements)/SUM(exposures) AS engagement_rate
FROM (
	  SELECT
	    date,
	    ad_id,
	    category,
	    segment,
	    MAX(exposures) AS exposures,
	    MAX(impressions) AS impressions,
	    MAX(engagements) AS engagements,
	    MAX(indeterminate_visibility) AS indeterminate_visibility
	FROM
	  report_metrics
	WHERE
	  keyword in (#keywords#)
	  AND date BETWEEN '#start_date#' AND '#end_date#'
	  AND agency_id = '#account_id#'
	GROUP BY
	  ad_id, date, category, segment
	ORDER BY
	  category, segment, date
) AS metrics_by_day
GROUP BY
  metrics_by_day.category, metrics_by_day.segment
ORDER BY
  metrics_by_day.category, metrics_by_day.segment