##  The numbers in the Spotify SoS data are fanning out.
##  This happens mostly with labels that have the same track on more than one release
##  
##  This file is a manual check for Som Livre, because SomLivre is where we found it


##  screen -xRR SpotifyRaw
##  R

setScience(proj="Spotify_Metadata_2016", subProj="checkingNumbers")

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

setSnowflake(wh=wh, dbname=dbname)

## First pull the releases
id.som_livre <- 21786

releases.som_livre <- sfQry(paste("SELECT releaseid from production.dim_release where labelid = ", id.som_livre))[[1]]

tbl.raw <- "staging_raw_spotify_V2"
schema.raw <- "production"
dateCol.raw <- "tmstamp"

whereClause.raw <- 
"
     TMSTAMP >= TO_TIMESTAMP('2015-01-01')
AND  TMSTAMP  < TO_TIMESTAMP('2016-01-01')
"

qry.raw <- makeQry(tbl=tbl.raw, schema=schema.raw, colsToPull=c("source", "source_uri"), colsToAgg=c(streams="*"), aggFunc="count", album_code=releases.som_livre, where=whereClause.raw, limit=NULL)
DT.raw <- sfQry(qry.raw)



tbl.view <- "sos_from_raw_view"
schema.view <- "spotify"
dateCol.view <- 

whereClause.view <- 
"
     TMSTAMP >= TO_TIMESTAMP('2015-01-01')
AND  TMSTAMP  < TO_TIMESTAMP('2016-01-01')
"

qry.raw <- makeQry(tbl=tbl.view, schema=schema.view, colsToPull=c("source", "source_uri"), colsToAgg=c(streams="*"), aggFunc="count", album_code=releases.som_livre, where=whereClause.view, limit=NULL)
DT.raw <- sfQry(qry.raw)


SELECT   source_original_values as source
       , COUNT(*) AS streams 
FROM   spotify.sos_from_raw_view    
WHERE 
(
          activity_datetime >= TO_TIMESTAMP('2015-01-01')
    AND   activity_datetime  < TO_TIMESTAMP('2016-01-01')
    AND   album_code in (%s)
)  
GROUP BY 1
ORDER BY 1;