Action Insights by Ad Format and Optimization Type

SELECT
    ac."status",
    ac.ad_format,
    ac.optimization_type,
    sum(ia.action_count) AS total_actions,
    sum(ia.action_value) AS total_value_of_actions,
    sum(ia.cost_per_action) AS total_cost_of_actions,
    avg(ia.action_count) AS avg_action_count,
    avg(ia.action_value) AS avg_value_per_action,
    avg(ia.cost_per_action) AS avg_cost_per_action,
    round(
        1.0 * sum(iv.avg_time_watched) / nullif(count(iv.video_action_id), 0),
        2
    ) as avg_time_watched_per_ad_with_video,
    round(
        1.0 * sum(iv."100_percent_watched_count") / nullif(count(i.insights_id), 0),
        2
    ) as fully_watched_video_percent
FROM
    fb_ads_ads_adcreatives ac
    LEFT JOIN fb_ads_insights i ON i.ad_id = ac.ad_id
    LEFT JOIN fb_ads_insights_actions ia ON ia.insights_id = i.insights_id
    LEFT JOIN fb_ads_insights_video_views iv on i.insights_id = iv.insights_id
GROUP BY 1, 2, 3;