setScience("new_hires_challenge", subProj="DataPull", create=TRUE, subl=TRUE)
setGitBranchToSystem(); .g()


wh <- getWH_already_on()
dbname <- "prod"


colsToPull <- 
c(
  "tmstamp"
,  "upc"
, "isrc"
, "device_type"
, "os"
, playlist_owner="source_playlist_owner"
, playlist_uri="source_uri"
, track_length="length"
, "user_access"
, country_code="user_country"
, "user_type"
, user_birthyear = "birth_year"
)

tbl <- "sos"
schema <- "spotify"

minDate <- "2014-09-01"
maxDate <- "2015-09-30"

qry.spot <- makeQry(tbl=tbl, schema=schema, dbname=dbname, source_playlist_owner=get_radial_usernames()
          , colsToPull=colsToPull, colsToAgg=c("streams"="*"), aggFunc="count", minDate=minDate, maxDate=maxDate, dateCol="tmstamp", where="user_type != 'trial'", orderby=1)
DT.spot <- sfQry(qry.spot, wh=wh, dbname=dbname)
setIDCols(DT.spot)


colsToPull.tracks <- 
c("isrc"
,"upc"
,"labelid"
,"track_number"
,"track_duration")

DT.tracks <- sfQry(makeQry(schema="bi", tbl="TRACK_WITH_UPC_VIEW", colsToPull=colsToPull.tracks, limit=NULL, isrc=unique(DT.spot[tmstamp >= "2015-06-01"]$isrc)), wh=wh, dbname=dbname)
setIDCols(DT.tracks)



sfQry("select * from bi.RELEASE_WITH_METADATA_VIEW limit 12")

colsToPull.rel <- 
c(upc="RELEASEID"
, "ARTIST_COUNTRY_CODE"
, "LABEL_COUNTRY_CODE"
, "LABEL_IS_DTHREE"
, "LABEL_IS_IODA"
, "LABEL_IS_O_AND_O"
, "RELEASE_DATE"
, "RELEASE_GENRE"
, "RELEASE_IS_COMPILATION"
, "RELEASE_IS_DIGITAL_ONLY"
, "RELEASE_IS_ITUNES_PREVIEWABLE"
, "RELEASE_NUMBER_OF_TRACKS"
)

DT.releases <- sfQry(makeQry(schema="bi", tbl="RELEASE_WITH_METADATA_VIEW", colsToPull=colsToPull.rel, limit=NULL, upc=unique(DT.spot[tmstamp >= "2015-06-01"]$upc)), wh=wh, dbname=dbname)
setBoolCols(DT.releases)
setIDCols(DT.releases)


DT.territories <- sfQry("SELECT country_code, country_name, region_group, continent FROM bi.country_view order by country_code ASC", wh=wh)

jesusForData()