×

Welcome to TagMyCode

Please login or create account to add a snippet.
 

direct_deals.sql

0
0
 
0
Language: Text
Posted by: Patrick Villanueva
Added: Dec 15, 2017 7:26 PM
Views: 2658
Tags: no tags
  1. SELECT *
  2. FROM (
  3.     SELECT zone_id, GROUP_CONCAT(DISTINCT country_id) AS country_id, GROUP_CONCAT(DISTINCT os_id) AS os_id,
  4.  
  5.            # all geos | all os
  6.            ' ' AS `1 ALL GEOS | ALL OS`,
  7.            IFNULL(GROUP_CONCAT(IF(country_id = 9999 AND os_id = 9999, deal_id, NULL)), 0) AS 1_deal_ids,
  8.            COUNT(DISTINCT IF(country_id = 9999 AND os_id = 9999, 1, 0)) AS 1_deals,
  9.            SUM(IF(country_id = 9999 AND os_id = 9999, allocation_pct, 0)) AS 1_alloc,
  10.            SUM(IF(country_id = 9999 AND os_id = 9999, weight, 0)) AS 1_weight,
  11.  
  12.            # all geos | specific os
  13.            ' ' AS `2 ALL GEOS | SPECIFIC OS`,
  14.            IFNULL(GROUP_CONCAT(IF(country_id = 9999 AND os_id != 9999, deal_id, NULL)), 0) AS 2_deal_ids,
  15.            COUNT(DISTINCT IF(country_id = 9999 AND os_id != 9999, 1, 0)) AS 2_deals,
  16.            SUM(IF(country_id = 9999 AND os_id != 9999, allocation_pct, 0)) AS 2_alloc,
  17.            SUM(IF(country_id = 9999 AND os_id != 9999, weight, 0)) AS 2_weight,
  18.  
  19.            # specific geos | all os
  20.            ' ' AS `3 SPECIFIC GEOS | ALL OS`,
  21.            IFNULL(GROUP_CONCAT(IF(country_id != 9999 AND os_id = 9999, deal_id, NULL)), 0) AS 3_deal_ids,
  22.            COUNT(DISTINCT IF(country_id != 9999 AND os_id = 9999, 1, 0)) AS 3_deals,
  23.            SUM(IF(country_id != 9999 AND os_id = 9999, allocation_pct, 0)) AS 3_alloc,
  24.            SUM(IF(country_id != 9999 AND os_id = 9999, weight, 0)) AS 3_weight,
  25.  
  26.            # specific geos | specific os
  27.            ' ' AS `4 SPECIFIC GEOS | SPECIFIC OS`,
  28.            IFNULL(GROUP_CONCAT(IF(country_id != 9999 AND os_id != 9999, deal_id, NULL)), 0) AS 4_deal_ids,
  29.            COUNT(DISTINCT IF(country_id != 9999 AND os_id != 9999, 1, 0)) AS 4_deals,
  30.            SUM(IF(country_id != 9999 AND os_id != 9999, allocation_pct, 0)) AS 4_alloc,
  31.            SUM(IF(country_id != 9999 AND os_id != 9999, weight, 0)) AS 4_weight
  32.  
  33.     FROM `campaigns_deals_zones`
  34.     WHERE `status` = 1 AND zone_id = 39450
  35.     AND (country_id = 840 OR country_id = 9999)
  36.     AND (os_id = 100 OR os_id = 9999)
  37.     GROUP BY zone_id
  38. ) AS tbl

Write a comment

To post comments, please log in or register.