closings

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

SELECT op.uuid AS ordered_placement_id,
       SUM(die1.impressions) AS impressions,
       SUM(die1.clicks) AS clicks,
       array_to_string(array_accum(DISTINCT sites.name), ', ') AS site_name,
       array_to_string(array_accum(DISTINCT cbsas.name), ', ') AS market_name,
       manifests.campaign_id,
       agencies.name AS agency,
       advertisers.name AS advertiser
FROM   ordered_placements op
  INNER JOIN third_party_placements tpp1 ON tpp1.ordered_placement_id = op.id
  INNER JOIN delivery_items di1 ON di1.third_party_placement_id = tpp1.id
  INNER JOIN delivery_item_entries die1 ON die1.delivery_item_id = di1.id
  INNER JOIN placements p1 ON p1.ordered_placement_id = op.id
  INNER JOIN plan_rows pr1 ON pr1.id = p1.plan_row_id
  INNER JOIN plans plans_1 ON pr1.plan_id = plans_1.id
  INNER JOIN venues v ON v.plan_row_id = pr1.id
  INNER JOIN site_markets sm ON sm.id = v.site_market_id
  INNER JOIN sites ON sm.site_id = sites.id
  INNER JOIN cbsas ON sm.market_id = cbsas.id
  INNER JOIN manifests ON plans_1.manifest_id = manifests.id
  INNER JOIN agencies ON agencies.id = manifests.agency_id
  INNER JOIN advertisers ON advertisers.id = manifests.advertiser_id
WHERE die1.closing_id = 1
  AND plans_1.id = (
    SELECT MAX(plans_2.id)
      FROM plans plans_2
      INNER JOIN manifests ON manifests.id = plans_2.manifest_id
      INNER JOIN plans plans_3 ON plans_3.manifest_id = manifests.id
    WHERE plans_2.io_processed_at IS NOT NULL
      AND plans_3.id = plans_1.id
  )
GROUP BY agencies.name,advertisers.name,manifests.campaign_id,pr1.id,op.uuid;