# counts per release and track.r


# screen -xRR SoS

setScience(proj="Spotify Playlist Webscrape", subProj="counts")
setGitBranchToSystem(); .g()


wh <- "SCIENCE"
dbname <- "prod"
schema <- "spotify"

## namespace.schema.table_name
tbl.in.full <- ("(SELECT * FROM prod.spotify.sample_sos limit 3000)")
tbl.in.full <- "prod.spotify.sample_sos"
tbl.in.full <- "prod.production.staging_raw_spotify_v2"

sfWarehouseOn(wh=wh, size="XXL")
setSnowflake(wh=wh, dbname=dbname)
#      count what? 
#      number of users
#      number of streams
#      number of playlists
#      is on discover weekly
#      
#      
#      isrc, playlist, first date streamed (in last 100 days)
#      isrc, userid, first date streamed

qrys <- list()

qrys[["isrc_playlist_streams_and_dates"]] <- 
  (setQry(sprintf("SELECT isrc, upc, source_uri, min(tmstamp) as first_time_streamed, count(*) as number_of_streams, count(distinct user_id) as number_of_users_streaming_track_from_this_playlist, current_timestamp() as this_row_last_modified  \n FROM %s \n WHERE source = 'others_playlist' \n GROUP BY 1, 2, 3;", tbl.in.full)))

qrys[["isrc_first_time_streamed_by_user_id_by_source"]] <- 
  (setQry(sprintf("SELECT isrc, upc, user_id, source, min(tmstamp) as first_time_streamed_by_source, current_timestamp() as this_row_last_modified  \n FROM %s \n GROUP BY 1, 2, 3, 4;", tbl.in.full)))

qrys[["isrc_weekly_counts_by_source"]] <- 
  setQry(sprintf("SELECT isrc, upc, source, source_uri
    , CASE WHEN (month(tmstamp) = 12 and week(tmstamp) = 1) THEN year(tmstamp) + 1 ELSE year(tmstamp) END + week(tmstamp)/100 as yr_wk
    , count(*) as number_of_streams
    , count(distinct user_id) as number_of_users_streaming_track_from_this_playlist
    , current_timestamp() as this_row_last_modified
    FROM %s \n GROUP BY 1, 2, 3, 4, 5;", tbl.in.full))

### ----------------- UPDATE TABLES ------------------------ ##
minDate.DW <- {today() - 100} %>% {if(week(.) > 37) .-7 else .} %>% {year(.) + (week(.) / 100)}
qrys[["isrc_weekly_counts_for_unknown_playlist"]] <- 
  setQry(sprintf("SELECT * FROM %s WHERE (yr_wk >= %s) AND (source = 'others_playlist') AND (source_uri IS NULL);", dbschematbl(dbname, schema, "isrc_weekly_counts_by_source"), minDate.DW))

## NOTE:  I have not tested with 'this_row_last_modified' -- it might cause error
qrys[["isrc_first_time_streamed_by_user_id"]] <- 
  (setQry(sprintf("SELECT isrc, upc, user_id, min(first_time_streamed_by_source) as first_time_streamed_by_any_source, this_row_last_modified  \n FROM %s \n GROUP BY 1, 2, 3, 5;", dbschematbl(dbname, schema, "isrc_first_time_streamed_by_user_id_by_source"))))

sfWaitForWarehouse(wh=wh)

## TESTING
if (FALSE)   { DT <- sfQry(tail(qrys, 1)[[1]]); print(DT) }

## EXECUTE THE QUERIES
for (nm in names(qrys))
  sfPopulateTable(qry=qrys[[nm]], tbl=nm, wh=wh, dbname=dbname, schema=schema, overwrite=TRUE, verbose=TRUE)

sourceSupportFns(proj="Looker")
create_lookml_model_from_schema(schema=schema, dbname=dbname, wh=wh, verbose=TRUE)
traceback()

## Bring the warehouse back down
if (FALSE)
{Sys.sleep(25*60); sfQry("ALTER WAREHOUSE \"LOOKER_WH_LARGE\" SET WAREHOUSE_SIZE = 'LARGE';")}


&&& I left off here .... I was doing something with checking the LAG on the number of streams.  
&&&   But then what?   What did I want to do with the delta? 
&&& Left off here, maybe? 
sfQry(
"
SELECT 
yr_wk, number_of_streams, lag(number_of_streams, 1) OVER (PARTITION BY isrc, upc ORDER BY yr_wk ASC) as last_streams
, number_of_streams - lag(number_of_streams, 1) OVER (PARTITION BY isrc, upc ORDER BY yr_wk ASC) AS delta_streams_wow
FROM prod.spotify.isrc_weekly_counts_for_unknown_playlist
WHERE isrc= 'QM6N21564803'
"
)
      yr_wk number_of_streams last_streams delta_streams_wow
 1: 2015.37               234           NA                NA
 2: 2015.38              1181          234               947
 3: 2015.39              1281         1181               100
 4: 2015.40               974         1281              -307
 5: 2015.41               796          974              -178
 6: 2015.42               627          796              -169
 7: 2015.43               501          627              -126
 8: 2015.44              3440          501              2939
 9: 2015.45              6444         3440              3004
10: 2015.46              5221         6444             -1223
11: 2015.47              1159         5221             -4062