Query ID 1260 - Diff Check

query_diff_1260.txt

QA Query:
--1260
WITH master_products as(
   select
      *
   from
      (
         SELECT
            row_number() over (
               partition by product_code,
               a.marketplace,
               a.region
               order by
                  org_type
            ) as row_num,
            master_enabled,
            product_status,
            product_code,
            a.marketplace,
            a.brand,
            category,
            model_number,
            upc,
            isbn,
            pasin,
            sub_category,
            a.region,
            short_name,
            product_title,
            segment,
            CONCAT(SPLIT(product_image_url, '.jpg') [OFFSET(0)],'.jpg') AS product_image_url,
            product_url,
            replenishment_status,
            release_date,
            coalesce(b.account_name, 'UNKNOWN') account_name
         FROM
            CC_I2O_DATA_REPO.final_product_master_filtered a
            left join CC_I2O_DATA_REPO.vantage_account_brand_marketplace_mapping b 
            on lower(a.brand) = lower(b.brand)
            and lower(a.marketplace) = lower(b.marketplace)
            and a.region = b.region
         WHERE
            master_enabled = true
      )
   where
      1 = 1 [filter2] AND row_num = 1 
)
 select period, reporting_range,ifnull(round(((SAFE_DIVIDE(sum(views*wbb),sum(views)))), 2),0) as won_buybox
 from (select period,reporting_range,product_code,region,marketplace,
 --org_type,
 views,wbb from CC_I2O_DATA_MART.viz_product_metrics
 -- mandatory filters
 Where [filter1]
 )a
 join master_products b
 on a.product_code=b.product_code and a.region=b.region and a.marketplace=b.marketplace
 --and a.org_type=b.org_type
 group by period,reporting_range
 order by period;

Prod Query:
--1260
 select period, reporting_range,ifnull(round(((SAFE_DIVIDE(sum(views*wbb),sum(views)))), 2),0) as won_buybox
 from (select period,reporting_range,product_code,region,marketplace,
 --org_type,
 views,wbb from CC_I2O_DATA_MART.viz_product_metrics
 -- mandatory filters
 Where [filter1]
 )a
 join(select  product_code,region,marketplace,org_type from (
 select  product_code,a.region,a.marketplace,org_type,a.brand,category,upc,product_status,account_name,short_name from CC_I2O_DATA_REPO.final_product_master_filtered a
 left join CC_I2O_DATA_REPO.vantage_account_brand_marketplace_mapping b on a.brand=b.brand and a.region=b.region and a.marketplace=b.marketplace where a.master_enabled=true)
 --dynamic filters
 WHERE 1=1 [filter2])b
 on a.product_code=b.product_code and a.region=b.region and a.marketplace=b.marketplace
 --and a.org_type=b.org_type
 group by period,reporting_range
 order by period

Differences:
  --1260
- WITH
- master_products
- as(
- select
- *
- from
- (
- SELECT
- row_number()
- over
- (
- partition
- by
- product_code,
- a.marketplace,
- a.region
- order
- by
- org_type
- )
- as
- row_num,
- master_enabled,
- product_status,
- product_code,
- a.marketplace,
- a.brand,
- category,
- model_number,
- upc,
- isbn,
- pasin,
- sub_category,
- a.region,
- short_name,
- product_title,
- segment,
- CONCAT(SPLIT(product_image_url,
- '.jpg')
- [OFFSET(0)],'.jpg')
- AS
- product_image_url,
- product_url,
- replenishment_status,
- release_date,
- coalesce(b.account_name,
- 'UNKNOWN')
- account_name
- FROM
- CC_I2O_DATA_REPO.final_product_master_filtered
- a
- left
- join
- CC_I2O_DATA_REPO.vantage_account_brand_marketplace_mapping
- b
- on
- lower(a.brand)
- =
- lower(b.brand)
- and
- lower(a.marketplace)
- =
- lower(b.marketplace)
- and
- a.region
- =
- b.region
- WHERE
- master_enabled
- =
- true
- )
- where
- 1
- =
- 1
- [filter2]
- AND
- row_num
- =
- 1
- )
  select
  period,
  reporting_range,ifnull(round(((SAFE_DIVIDE(sum(views*wbb),sum(views)))),
  2),0)
  as
  won_buybox
  from
  (select
  period,reporting_range,product_code,region,marketplace,
  --org_type,
  views,wbb
  from
  CC_I2O_DATA_MART.viz_product_metrics
  --
  mandatory
  filters
  Where
  [filter1]
  )a
+ join(select
+ product_code,region,marketplace,org_type
+ from
+ (
+ select
+ product_code,a.region,a.marketplace,org_type,a.brand,category,upc,product_status,account_name,short_name
+ from
+ CC_I2O_DATA_REPO.final_product_master_filtered
+ a
+ left
  join
- master_products
+ CC_I2O_DATA_REPO.vantage_account_brand_marketplace_mapping
  b
+ on
+ a.brand=b.brand
+ and
+ a.region=b.region
+ and
+ a.marketplace=b.marketplace
+ where
+ a.master_enabled=true)
+ --dynamic
+ filters
+ WHERE
+ 1=1
+ [filter2])b
  on
  a.product_code=b.product_code
  and
  a.region=b.region
  and
  a.marketplace=b.marketplace
  --and
  a.org_type=b.org_type
  group
  by
  period,reporting_range
  order
  by
- period;
?       -

+ period