screen -xRR Apple
R
## ------------------------------------------------------------------- ##

setScience("misc", subProj="Apple", load=FALSE, subl=FALSE)
cluster <- 7
wh <- "LOOKER_WH_LARGE"

to <- "Lee Westerfield <lee@theorchard.com>"
to <- "rsaporta@gmail.com"

setCluster(cluster=cluster)

tbl.spot <- "staging_raw_spotify_v2"
tbl.apple <- "staging_raw_apple_music"
schema <- "production"

minDate <- as.Date("2015-06-30")
maxDate.apple <- qMaxDate(tbl=tbl.apple, dateCol="download_date", schema=schema, cluster=cluster, snowflake=FALSE) %>% toUTCdate
maxDate.spot  <- qMaxDate(tbl=tbl.spot,  dateCol="download_date", schema=schema, cluster=cluster, snowflake=FALSE) %>% toUTCdate
maxDate <- as.Date("2015-07-26")

## ------------------------------------------------------------------ ##
## GET APPLE MUSIC
## ------------------------------------------------------------------ ##
    dateCol.apple <- "datestamp"
    colsWithaggFunc <- sprintf("%s(%s)", c("min", "max"), dateCol.apple) %>% setNames(nm=c("min_date", "max_date"))
    qry.apple <- makeQry(colsWithaggFunc=colsWithaggFunc, tbl=tbl.apple, schema=schema, colsToPull=c(country_code="storefront_name"), colsToAgg=c(streams="*"), minDate=minDate, maxDate=maxDate, dateCol=dateCol.apple, key="colsToPull", limit=NULL)
    DT.apple <- runQry(qry.apple, cluster=cluster)
    DT.apple[, country_rank := rank(-streams)]
    DT.apple[country_rank <= 10][order(country_rank)]
## ------------------------------------------------------------------ ##

## ------------------------------------------------------------------ ##
## GET SPOTIFY DATA 
## ------------------------------------------------------------------ ##
    ## DETERMINE WHETHER OR NOT TO USE SNOWFLAKE
    options(snowflake_inuse = unname(as.Date(qMinDate(tbl=tbl.spot, schema=schema, cluster=cluster)) <= minDate))

    dateCol.spot <- "download_date"
    colsWithaggFunc <- sprintf("%s(%s)", c("min", "max"), dateCol.spot) %>% setNames(nm=c("min_date", "max_date"))
    qry.spot <- makeQry(tbl=tbl.spot, schema=schema, colsWithaggFunc=colsWithaggFunc, colsToPull=c(country_code="user_country"), colsToAgg=c(streams="*"), user_access="premium", dateCol=dateCol.spot, minDate=minDate, maxDate=maxDate, key="colsToPull", limit=NULL)
    # DT.spot <- runQry(qry.spot)
    DT.spot <- sfQry(qry.spot, wh=wh)
    DT.spot[, min_date := toUTCdate(min_date)]
    DT.spot[, max_date := toUTCdate(max_date)]
    DT.spot[, country_rank := rank(-streams)]
    DT.spot[country_rank <= 10][order(country_rank)]
## ------------------------------------------------------------------ ##


## ------------------------------------------------------------------ ##
## GET SPOTIFY DATA 
## ------------------------------------------------------------------ ##
  DT.merged <- merge(DT.spot, DT.apple, by=c("country_code"), suffix=c("_spotifypremium", "_applemusic"), all=TRUE)
  DT.merged[, applemusic_perc_of_spotifypremium := streams_applemusic / streams_spotifypremium]

  addCountry.byCode_(DT.merged, colsToBring=c("region_group", "country_name"))
  region_group_to_factor_(DT.merged)
  setkeyIfNot(DT.merged, "region_group", "applemusic_perc_of_spotifypremium", verbose=FALSE)

  colOrder <- c("region_group", "country_code", "country_name", "applemusic_perc_of_spotifypremium", "country_rank_applemusic", "country_rank_spotifypremium", "streams_applemusic", "streams_spotifypremium", "max_date_applemusic", "max_date_spotifypremium")
  setcolorderpt(DT.merged, colOrder)


  DT.orchard_top10_applemusic_or_spotifypremium_countries_july2015 <- DT.merged[country_rank_applemusic <= 15 | country_rank_spotifypremium <= 15 | country_code %in% c("IN")]
  DT.orchard_top10_applemusic_or_spotifypremium_countries_july2015[, min_date_applemusic := NULL]
  DT.orchard_top10_applemusic_or_spotifypremium_countries_july2015[, min_date_spotifypremium := NULL]

  ## convert to string, for output
  DT.orchard_top10_applemusic_or_spotifypremium_countries_july2015[, applemusic_perc_of_spotifypremium := fwp(applemusic_perc_of_spotifypremium)]
  setkeyIfNot(DT.orchard_top10_applemusic_or_spotifypremium_countries_july2015, "country_rank_applemusic", "country_rank_spotifypremium", verbose=FALSE)

  writeDT(DT.orchard_top10_applemusic_or_spotifypremium_countries_july2015, to=to, subject="Music vs Spotify Premium - Top 10 Countries")

  jesusForData(DT.orchard_top10_applemusic_or_spotifypremium_countries_july2015)

------------------------------------------
loadFromJesus("~rsaporta/git/orch/data/misc/DT.apple_rows_by_country-20150807_1009-1312x4.RDS")

ggLinegraph(DT.apple_rows_by_country, x="datestamp", y="rows", facet_formula="country_code ~ .")