q <- "
   SELECT
        staging_raw_spotify_v2.source_uri AS source_uri,
        staging_raw_spotify_v2.user_id AS playlist_owner_id,
        TO_CHAR(TO_DATE(staging_raw_spotify_v2.download_date), 'YYYY-MM-DD') AS activity_date,
        TO_CHAR(staging_raw_spotify_v2.download_date, 'DY') AS weekday,
        COUNT(*) AS number_of_streams as number_of_streams
   FROM PRODUCTION.STAGING_RAW_SPOTIFY_V2 AS staging_raw_spotify_v2

   WHERE
        (staging_raw_spotify_v2.user_id in ('theorchardus', 'maykrofon', 'radialcanada', 'theorcharduk', 'radiallatinoamerica', 'radialargentina', 'radialbrasil', 'radialturkey', 'orchardscandinavia', '1113004802', '114125244', 'radialitalia', 'clubcorridos', 'radialphilippines')) AND 
        (staging_raw_spotify_v2.user_access != 'Deleted')
   GROUP BY 1,2,3,4
   ORDER BY 2 DESC,1 DESC,3 DESC
"

DT.Source_of_Stream_by_User <- sfQry(q)
DT.Source_of_Stream_by_User[, activity_date := as.Date(activity_date)]
DT.Source_of_Stream_by_User[, playlistid := gsub(".*playlist:", "", source_uri)]

writeDT(DT.Source_of_Stream_by_User, to=getTo("smore"), subject="Updated Version, with more users included")


~~~~~~~~~~~~~~~~~~~~~~~~~

loadFromJesus("~rsaporta/git/orch/data/misc/DT.Source_of_Stream_by_User-20150820_2244-4233x5.RDS")

uris_using <- DT.Source_of_Stream_by_User[, .N, keyby=list(source_uri)][order(N, decreasing=TRUE)][N >= 50, source_uri]

top_users <- c("114125244", "radialphilippines", "theorchardus")
P.streams_per_playlist_2 <- ggLinegraph(dotsize.scale=1, DT.Source_of_Stream_by_User[playlist_owner_id %ni% top_users], x="activity_date", y="number_of_streams", facet_formula=". ~ playlist_owner_id", facet_scale="free", color="playlistid", legend="top")
P.streams_per_playlist_topusers <- ggLinegraph(dotsize.scale=1, DT.Source_of_Stream_by_User[playlist_owner_id %in% top_users], x="activity_date", y="number_of_streams", facet_formula=". ~ playlist_owner_id", facet_scale="free", color="playlistid", legend="top")

setkeyIfNot(DT.Source_of_Stream_by_User, "playlist_owner_id")
# for (user in unique(DT.Source_of_Stream_by_User$playlist_owner_id)) {
PLOTS.playlists_stream_counts_by_user <- 
lapply(unique(DT.Source_of_Stream_by_User$playlist_owner_id), function(user) {
  ggLinegraph(dotsize.scale=1, DT.Source_of_Stream_by_User[.(user)], x="activity_date", y="number_of_streams", facet_formula=". ~ playlist_owner_id", facet_scale="free", color="playlistid", legend="top", smooth=TRUE)
})


f.plots <- plots.p("playlists_stream_counts_by_user_smoothed", ext="pdf")

## BROKEN: printToPDF(PLOTS.playlists_stream_counts_by_user)

pdf(f.plots, height=10, width=8)
for (P in PLOTS.playlists_stream_counts_by_user)
print(P)
dev.off()
.o(f.plots)

DT.Source_of_Stream_by_User[, .N, keyby=list(source_uri)]