stop("This file should not be auto-sourced")

colNames.production.staging_raw_itunes <- 
c("ingestion_time", "filename","filesize", "provider","service_provider_country", "vendor_identifier","upc", "isrc","artist", "title","record_company", "product_type_identifier","units", "royalty_price","download_date", "order_id","postal_code", "customer_identifier","report_date_local", "sale_return","customer_currency", "country_code","royalty_currency", "preorder","isan", "customer_price","apple_id", "cma","asset", "vendor_offer_code","grid", "promo_code","parent_id", "season_pass","belongs_to_ioda", "attributable_purchase","primary_genre")

sort(colNames.production.staging_raw_itunes)

cbind(sort(colNames.production.staging_raw_itunes))

netUnits = ifelse(sale_return=='R', units * -1, units)

if (FALSE) {

# ---------------------------------------------------- #
    units *  ((-1) ^ ((sale_return = 'R') IS TRUE)::int)

    # Paulo's suggestion
    units * (case (sale_return) when 'R' then -1 else 1 end)

# ---------------------------------------------------- #


 SELECT upc, customerid, user_country,
        SUM (netUnits) AS t_units12WeekFor {MONTH}_byCountry
        COUNT (netUnits) AS u_customers12WeekFor {MONTH}_byCountry
 FROM (
      SELECT upc, customer_identifier AS customerid, country_code AS user_country,
             SUM ( units *  ((-1) ^ ((sale_return = 'R') IS TRUE)::int)  )  AS netUnits
      FROM  production.staging_raw_itunes
      WHERE download_date >= ' 2013-06-11 '
        AND download_date <= ' 2013-09-02 '
      GROUP BY upc, country_code, customer_identifier
 ) {m_inner}
GROUP BY upc, country_code, customer_identifier




        COUNT (customer_identifier)          AS  t_count12WeekFor_Aug_byCountry


 FROM  production.staging_raw_itunes
 WHERE download_date >= ' 2013-06-11 '
   AND download_date <= ' 2013-09-02 '
   AND sale_return = 'S'
 GROUP BY upc, country_code


 SELECT upc, country_code AS user_country,
        COUNT (customer_identifier)          AS  t_count12WeekFor_Aug_byCountry
 FROM  production.staging_raw_itunes
 WHERE download_date >= ' 2013-06-11 '
   AND download_date <= ' 2013-09-02 '
   AND sale_return = 'R'
 GROUP BY upc, country_code




 SELECT upc, country_code AS user_country,
        COUNT (DISTINCT customer_identifier) AS  u_count12WeekFor_Aug_byCountry ,
        COUNT (customer_identifier)          AS  t_count12WeekFor_Aug_byCountry
 FROM  production.staging_raw_itunes
 WHERE download_date >= ' 2013-06-11 '
   AND download_date <= ' 2013-09-02 '
   AND sale_return <> 'return'
   AND sale_return <> 'r'
 GROUP BY upc, country_code


 distinct USERS  by  upc


 a USER can listen to a TRACK, ALBUM or artist


}