/Users/rsaporta/git/orch/src/Spotify_Accounting_ETL/BizReview/Streams Per Territory.r


screen -xRR "StreamsPer"
tbl <- "STREAMS_FROM_RAW_ANALYTICS"
schema <- "SPOTIFY"
setSnowflake(wh="LOOKER_WH_LARGE")

setScience("Spotify_Accounting_ETL", subProj="BizReview", load=FALSE, subl=FALSE)
setGitBranchToSystem(); .g()

sfQry("SHOW TABLES IN SPOTIFY")
sfQry("DESC TABLE SPOTIFY.STREAMS_FROM_RAW_ANALYTICS")

sfQry("SELECT * FROM SPOTIFY.STREAMS_FROM_RAW_ANALYTICS limit 10")
sfQry("SELECT min(activity_date) as minn, max(activity_date) as maxx FROM SPOTIFY.STREAMS_FROM_RAW_ANALYTICS limit 10")

sfQry("SELECT min(activity_date) as minn, max(activity_date) as maxx FROM SPOTIFY.STREAMS_FROM_RAW_ANALYTICS limit 10")

qDateInfo("Staging_raw_spotify_V2", schema="production")

headDB(tbl=tbl, schema=schema)

DT.streams_from_raw_analytics <- sfQry(makeQry(tbl=tbl, schema=schema, colsToPull="*", aggFunc=NULL, limit=NULL, dateCol="activity_date"))

jesusForData(DT.streams_from_raw_analytics)

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

test_countries <- c('DE', 'CH', 'US', 'BR', 'GB', 'MX', 'SE')

setScienceIfNot("Spotify_Accounting_ETL", subProj="BizReview", load=FALSE)
loadIfNotExists("DT.streams_from_raw_analytics")

DT.plot.by_type <- DT.streams_from_raw_analytics[country_code %in% test_countries, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date, stream_type), .SDcols="streams"]
ggLinegraph(DT.plot.by_type, x="activity_date", y="streams", color="stream_type", title="Spotify Streams by AdSupported vs Premium", title="TEST COUNTRIES ONLY")

DT.plot.combined <- DT.streams_from_raw_analytics[activity_date >= "2015-08-01" & activity_date <= "2015-08-12" & country_code %in% test_countries, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date), .SDcols="streams"]
ggLinegraph(DT.plot.combined, x="activity_date", y="streams", title="STAGING_RAW_SPOTIFY_V2 - test countries")

DT.plot.by_country <- DT.streams_from_raw_analytics[country_code %in% test_countries, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date, country_code), .SDcols="streams"]
ggLinegraph(DT.plot.by_country[country_code != "US" & streams > 3e8], x="activity_date", y="streams", color="country_code", vline_value=c("2015-08-12", "2015-08-13"), title="TEST COUNTRIES ONLY")

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

dateCol <- "activity_date"
qBI <- makeQry(tbl="analytics", schema="bi", colsToAgg=c(streams="units"), colsToPull=c(dateCol, "country_code", "label_sc_group"), storeid = 286, country_code=test_countries, minDate="2015-08-01", dateCol=dateCol)
DT.bi_anal <- sfQry(qBI)

jesusForData(DT.bi_anal)

DT.plot.combined <- DT.bi_anal[activity_date >= "2015-08-01" & activity_date <= "2015-08-12", lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date), .SDcols="streams"]
ggLinegraph(DT.plot.combined, x="activity_date", y="streams", title="FACT_ANALYTICS - test countries")


DT.plot.by_country <- DT.streams_from_raw_analytics[, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date, country_code), .SDcols="streams"]
ggLinegraph(DT.plot.by_country[country_code != "US" & streams > 3e8], x="activity_date", y="streams", color="country_code", vline_value=c("2015-08-12", "2015-08-13"))


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

dateCol <- "download_activity_date"
qFA <- makeQry(tbl=c(FA="fact_analytics"), schema="production", colsToAgg=c(streams="units"), colsToPull=c(dateCol, "country_code"), storeid = 286, country_code=test_countries, minDate="2015-08-01", dateCol=dateCol, join=" production.dim_country DC ON FA.countryid = DC.countryid ", prependCols.with.tbl=FALSE)
DT.fact_anal <- sfQry(qFA)

jesusForData(DT.fact_anal)

DT.plot.combined <- DT.fact_anal[download_activity_date >= "2015-08-01" & download_activity_date <= "2015-08-12", lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(download_activity_date), .SDcols="streams"]
ggLinegraph(DT.plot.combined, x="download_activity_date", y="streams", title="FACT_ANALYTICS - test countries")

DT.plot.by_country <- DT.streams_from_raw_analytics[, lapply(.SD, function(x) sumn(as.numeric(x))), keyby=list(activity_date, country_code), .SDcols="streams"]
ggLinegraph(DT.plot.by_country[country_code != "US" & streams > 3e8], x="activity_date", y="streams", color="country_code", vline_value=c("2015-08-12", "2015-08-13"))