gistfile1.txt

          SELECT zip_code_ads.account_id, zip_code_ads.category_id,
            SUM(CASE zip_code_ads.ad_type_id WHEN 1 THEN (case is_purchased when true then 1 else 0 end) ELSE 0 END) exclusive_count,
            SUM(CASE zip_code_ads.ad_type_id WHEN 2 THEN (case is_purchased when true then 1 else 0 end) ELSE 0 END) enhanced_count,
            SUM(CASE zip_code_ads.ad_type_id WHEN 3 THEN (case is_purchased when true then 1 else 0 end) ELSE 0 END) listing_count,
            SUM(CASE zip_code_ads.ad_type_id WHEN 4 THEN (case is_purchased when true then 1 else 0 end) ELSE 0 END) free_count,
            SUM(CASE zip_code_ads.ad_type_id WHEN 5 THEN (case is_purchased when true then 1 else 0 end) ELSE 0 END) temp_count,
            SUM(COALESCE(zip_code_prices.excl_price, excl.price) * (CASE zip_code_ads.ad_type_id WHEN 1 THEN (case is_purchased when true then 1 else 0 end) ELSE 0 END)) exclusive_value,
            SUM(COALESCE(zip_code_prices.enh_price, enha.price) * (CASE zip_code_ads.ad_type_id WHEN 2 THEN (case is_purchased when true then 1 else 0 end) ELSE 0 END)) enhanced_value,
            SUM(COALESCE(zip_code_prices.listing_price, list.price) * (CASE zip_code_ads.ad_type_id WHEN 3 THEN (case is_purchased when true then 1 else 0 end) ELSE 0 END)) listing_value
          FROM zip_code_ads
          LEFT JOIN category_ad_type_prices excl ON zip_code_ads.category_id = excl.category_id and excl.ad_type_id = 1
          LEFT JOIN category_ad_type_prices enha ON zip_code_ads.category_id = enha.category_id and enha.ad_type_id = 2
          LEFT JOIN category_ad_type_prices list ON zip_code_ads.category_id = list.category_id and list.ad_type_id = 3
          LEFT JOIN zip_code_prices ON zip_code_ads.zip_code_id = zip_code_prices.id
    
          GROUP BY zip_code_ads.account_id, zip_code_ads.category_id