WITH DATA AS (
              WITH rpt AS (
                WITH rpt AS (
                  SELECT f.country,
                         city,
                         CASE WHEN audience='i' THEN 'All Campaign'
                              WHEN audience='s' THEN 'Visitors'
                              WHEN audience='c' THEN 'Clickers'
                              WHEN audience='v' THEN 'Converters'
                          END AS audience,
                         CAST(SUM(imp_events) AS float8) AS impressions,
                         CAST(SUM(clk_events) AS float8) AS clicks,
                         CAST(SUM(cnv_events) AS float8) AS conversions,
                         CAST(SUM(pv_events) AS float8) AS postviewconvs,
                         CAST(SUM(pc_events) AS float8) AS postclickconvs,
                         CAST(SUM(imp_users) AS float8) AS uniques,
                         SUM(CASE WHEN crate.label = 'CPC' THEN camp.cost_value*clk_events ELSE camp.cost_value*imp_events/1000 END) AS cost,
                         SUM(CASE WHEN audience='i' THEN SUM(imp_events) ELSE 0 END) OVER(partition BY camp.label,f.country,city) AS allcamp_impressions,
                         audience AS raw_audience
                  FROM datamart.db_campaign_geo_uniques f
                  INNER JOIN tb_as_campaign camp ON f.campaign_id = camp.id
                  INNER JOIN tb_as_lookup crate ON crate.id = camp.cost_type
                  WHERE campaign_id = 2487612
                    AND camp.advertiser = 406640
                    AND f.city != '?'
                  GROUP BY camp.label, f.country, city, f.audience
                )
                SELECT campaign,country,city,audience,impressions,clicks,uniques,conversions,postviewconvs,postclickconvs,cost,
                       dense_rank() OVER(partition BY campaign ORDER BY allcamp_impressions DESC) AS rnk
                FROM rpt
              )
              SELECT country,
                     city,
                     audience,
                     impressions,
                     clicks,
                     uniques,
                     CASE WHEN COALESCE(impressions,0) = 0 THEN NULL ELSE to_char(CAST(COALESCE(clicks,0) AS float8)/impressions * 100 END AS ctr,
                     conversions,
                     postviewconvs,
                     postclickconvs,
                     CASE WHEN COALESCE(conversions,0) = 0 OR COALESCE(cost,0) = 0  THEN NULL ELSE to_char(cost/conversions END AS cpa
              FROM rpt
              WHERE rnk <= 30
            )
            SELECT *
            FROM DATA