OLD FILE    































# screen -xRR TopX






setScience(proj="Spotify_Accounting_ETL", subProj="BizReviewTopX")

# X="labelid"; year=2015; country_code=NULL; X.nm=gsub("_?id$", "", tolower(X)); dateCol="activity_month"; tbl="accounting"; schema="bi"; maxMonth="auto"
wh <- "LOOKER_WH_LARGE"
dbname <- "prod"


storeid = 286

# cols <- qShowCols("bi.accounting")
# extract("release", cols) %>% cbind
# extract("track", cols) %>% cbind





storeid = 286
types_using <- c("label", "track", "album") %>% paste0("id")
countries_using <- c("US", "GB", "SE", "ES", "NO", "DE", "NL", "MX", "AU", "BR", "DK", "FR", "IT", "FI", "CH", "AR")
years_using <- 2013:2015

limit_per_year = 100000 / 2

years_using %<>% sort %>% rev
maxDate_in_tbl <- qMaxDate(tbl=tbl, schema=schema, dateCol=dateCol, where=list(storeid=storeid), verbose=TRUE, snowflake_inuse=snowflake_inuse)
catn("The max month in the system for the given filters is ", as.character(maxDate_in_tbl))
maxMonth <- month(maxDate_in_tbl)

N.top = 250

country_code <- "GB"
type <- types_using[[1]]

for (type in typeid) 
{
  for (country_code in countries_using) {
    DT.country_type <- lapply(years_using, function(year) {
        create_qry_TopX_byYear(X=type, year=year, country_code=country_code, storeid=286, maxMonth=maxMonth, limit_per_year=limit_per_year) %>% 
          sfQry(wh=wh)
    })

    if (type == "trackid") {
      stop("Dont forget about TRACKS - RELEASES")
    }

    # Reduce(f=data.table:::merge.data.table, x=DT.country_type, moreArgs=(all=TRUE, by=c("year", "country_code", type)))
    # DT.country <- do.call(data.table:::merge.data.table, args=c(DT.country_type, all=list(TRUE), by=list(c("year", "country_code", type))))
    DT.country <- merge_list_DTs(DT.country_type, all=TRUE, by=c("country_code", type))

    ## FOR NOW, DROP THE METADATA
    if (FALSE) {
      DT.country[, c("year", "year.01", "year.02") := NULL]
      DT.country[, c("label_name", "label_priority", "label_owner", "label_is_dthree", "label_sc_group", "label_country") := NULL]
    }

    grossCols <- extract("gross", DT.country) %>% unname %>% sort %>% rev
    rankCols  <- extract("rank", DT.country)  %>% unname %>% sort %>% rev

    for (i in head(seq_along(rankCols), -1)) {
      col_curr_year <- rankCols[[i]]
      col_prev_year <- rankCols[[i+1]]

      extractDate <- . %>% gsub("(.*)(\\d{4})(.*)", "\\2", .)
      col_curr_year %>% extractDate
      col_prev_year %>% extractDate

      nm.newCol <- sprintf("rank_increase_%s_vs_%s", extractDate(col_curr_year), extractDate(col_prev_year))

      ### Change in rank is calculated as   increase := prev_rank - curr_rank
      ### If last year was 4, and this year was 2, the change should be +2;  'Upwards'
      ### If last year was 4, and this year was 6, the change should be -2;  'Downwards'
      if (i == 1) {
        DT.country[, most_recent_rank_change := ifelse(get(col_prev_year) - get(col_curr_year) >= 0, "Upwards", "Downwards")]
        DT.country[, most_recent_gross_change := ifelse(get(grossCols[[i]]) - get(grossCols[[i+1]]) >= 0, "Upwards", "Downwards")]
      }
      DT.country[, (nm.newCol) := get(col_prev_year) - get(col_curr_year)]
    } ## // end for-loop for adding change in rank

    setcolorderpt(DT.country, endCols=c(extract("_change$", names(DT.country)), grossCols, rankCols, extract("rank_increase_", names(DT.country))))


    DT.country[most_recent_gross_change != most_recent_rank_change & label_total_gross_for_2015 > 2000][order(label_total_gross_for_2015, decreasing=TRUE)]

    # confirming:      DT.confirm <- {
    # confirming:        sfQry("
    # confirming:      SELECT country_code, labelid, year(activity_month) as year, activity_month, sum(gross) AS gross
    # confirming:      FROM bi.accounting
    # confirming:      WHERE labelid = 16581
    # confirming:        AND year(activity_month) >= 2013
    # confirming:        AND country_code = 'SE'
    # confirming:        AND storeid = 286
    # confirming:      GROUP BY 1, 2, 3, 4
    # confirming:      ORDER BY 4")}
    # confirming:      DT.confirm[, list(gross=sum(gross)), keyby=list(country_code, labelid, year, first_five_months=month(activity_month) <= 5)] [(first_five_months)]
    # confirming:      DT.country[labelid==16581, cbind(sapply(grossCols, function(x) get(x)) %>% {.[order(names(.))]})]
    # confirming:  
    # confirming:      sapply(quantile(x, c(10, 20, 50, 80, 90)/100, na.rm=TRUE), . %>% {percTrue(x >= .)}) %>% fwp
    # confirming:  
    # confirming:      DT.country[above_quantile(label_total_gross_for_2015, .50) & above_quantile(label_total_gross_for_2014, .30) & above_quantile(label_total_gross_for_2013, .20)][order(`Rank Increase 2015 vs 2014`, decreasing=TRUE)]
    # confirming:      DT.country[above_quantile(label_total_gross_for_2015, .50) & above_quantile(label_total_gross_for_2014, .30) & above_quantile(label_total_gross_for_2013, .20)][`Rank Increase 2015 vs 2014` > `Rank Increase 2014 vs 2013` & `Rank Increase 2014 vs 2013` > 0][order(`label_rank_for_2015`, decreasing=FALSE)][1:22]
    # confirming:      DT.country[order(`label_total_gross_for_2015`, decreasing=TRUE)][1:22]

    envir <- globalenv()
    info <- type %>% gsub("_?id$", "", .) %>% topropper %>% sprintf("Top_%i_%ss_for_%s", N.top, ., country_code)
    nm.out <- paste0("DT.", info)
    assign(nm.out, value=DT.country, envir=envir)

    ## TODO: which are growing? 
    DT.country <- DT.country[orderch(rankCols, decreasing=FALSE)][get(rankCols[[1]]) <= N.top]
  
    DT.country

  } ## // end for-loop for country_code

  if (FALSE){
    nms.DTs <- type %>% gsub("_?id$", "", .) %>% topropper %>% sprintf("Top_%i_%ss_for_%s", N.top, ., countries_using) %>% sprintf("DT.%s", .)
    jesusForData(objNames=nms.DTs, git=FALSE)
  }
}

if (FALSE) 
    writeDT(get(nm.out, envir=envir), base.file.name=info, subfolder="BizReview/TopX", ext="csv", to="Nicolas Rizzi <nrizzi@theorchard.com>", cc=getRS(), subject=sprintf("%s (semi processed data)", topropper_keywords(info)))



select 
    labelid
  , sum(gross) as label_total_gross_for_2015
  , rank() over (order by sum(gross) desc) as label_rank_for_2015
from bi.accounting ACC
where 
      YEAR(ACC.activity_month) = 2015
  AND 1=1 /* no filter on 'accounting.rank_storeid_filter' */
  AND ( ACC.country_code  = 'US')
  AND 1=1 /* no filter on 'accounting.rank_supply_chain_filter' */
  AND 1=1 /* no filter on 'accounting.rank_continent_filter' */
  AND 1=1 /* no filter on 'accounting.rank_region_group_filter' */
  AND 1=1 /* no filter on 'accounting.rank_music_vs_video_filter' */
  AND 1=1 /* no filter on 'accounting.rank_stream_vs_download_filter' */
  AND 1=1 /* no filter on 'accounting.rank_transac_type_abbr_filter' */
group by 1


makeQry
colsToPull <- c(year="year(dateCol)", "country_code")
select 
    labelid
  , sum(gross) as label_total_gross_for_2015
  , rank() over (order by sum(gross) desc) as label_rank_for_2015
from bi.accounting ACC
where 
      YEAR(ACC.activity_month) = 2015
  AND 1=1 /* no filter on 'accounting.rank_storeid_filter' */
  AND ( ACC.country_code  = 'US')
  AND 1=1 /* no filter on 'accounting.rank_supply_chain_filter' */
  AND 1=1 /* no filter on 'accounting.rank_continent_filter' */
  AND 1=1 /* no filter on 'accounting.rank_region_group_filter' */
  AND 1=1 /* no filter on 'accounting.rank_music_vs_video_filter' */
  AND 1=1 /* no filter on 'accounting.rank_stream_vs_download_filter' */
  AND 1=1 /* no filter on 'accounting.rank_transac_type_abbr_filter' */
group by 1

------------------------------------------------------------------