setScience(proj="Spotify_Accounting_ETL", subProj="genre", load=FALSE)
stores_using <- c("YouTube", "Spotify", "deezer", "Deezer", "Cricket Communications, Inc.", "Muve", "YouTube Movies", "Google Play", "rDio", "Rdio", "iTunes", "iTunes/Apple", "Amazon", "VEVO")
wh <- getWH_already_on()
dbname <- "prod"


setSnowflake(wh=wh, dbname=dbname)

dateCol <- "activity_month"
minDate <- "2014-03-01"
colsWithaggFunc=c(number_of_releases="count(distinct releaseid)")
colsToPull <- c(month=dateCol , "music_vs_video", "region_group", "stream_vs_download", "coremarkets_country_name", "store_name", cat_mid_front="release_cat_mid_front", genre="release_genre_group_less")
DT.units_by_genre <- makeQry(tbl="accounting", schema="bi", colsToPull=colsToPull, colsWithaggFunc=colsWithaggFunc, colsToAgg=c("units"), minDate=minDate, dateCol=dateCol, key="colsToPull", store_name="Spotify") %>% sfQry(wh=wh)
DT.units_by_genre[, cat_mid_front := setFactorOrder(cat_mid_front, c("Frontline", "Midline", "Catalog"))]

&&& Count the total number of releases having activity in that time period &&&
colsWithaggFunc=c(number_of_releases="count(distinct releaseid)")
DT.unique_releases <- makeQry(tbl="accounting", schema="bi", colsToPull=colsToPull, colsWithaggFunc=colsWithaggFunc, colsToAgg=c("units"), minDate=minDate, dateCol=dateCol, key="colsToPull") %>% sfQry(wh=wh)
&&& Count the total number of releases having activity in that time period &&&

DT.genre_aggd <- copy(DT.units_by_genre[stream_vs_download != "zOTHER TRANSACTIONTYPESz"])
DT.genre_aggd[store_name %ni% stores_using, store_name := "zAll Other Stores"]
DT.genre_aggd <- aggregateDT(DT.genre_aggd, colsToAgg="units", exclude=NULL)
DT.genre_aggd[, units_per_release := units / number_of_releases]
jesusForData(DT.genre_aggd)
# .g(); loadFromJesus("DT.genre_aggd", over=TRUE)

DT.genre_s_vs_d <- DT.genre_aggd[music_vs_video == "Music", list(units = sumn(as.numeric(units)), number_of_releases = sumn(number_of_releases)), keyby=list(month, genre, stream_vs_download, cat_mid_front)]
DT.genre_s_vs_d[, avg_streams_per_release := units / number_of_releases]

## Drop the two max months
for (i in 1:2)
  DT.genre_s_vs_d <- DT.genre_s_vs_d[month != max(month)]
DT.genre_s_vs_d[, change_in_units := percentIncrease(units), keyby=list(genre, stream_vs_download, cat_mid_front)]


genre_ordering <- DT.genre_s_vs_d[month >= "2015-01-01", sum(units), keyby=genre][order(V1, decreasing=TRUE), genre]
genre_ordering %<>% setdiff("zOTHER GENRESz") %>% c("zOTHER GENRESz")
DT.genre_s_vs_d[, genre := setFactorOrder(genre, genre_ordering)]

dict.colors <- c(
        "Hip-hop/Rap" = "#385BC6"
, "Country/Folk/Jazz" = "Orange"
,        "Electronic" = "#AA27FF"
,             "Latin" = "#54C68B"
,   "Rock/Metal/Punk" = "#D31B41"
  )

dict.colors %<>% names %>% setdiff(DT.genre_s_vs_d$genre, .) %>% {setNames(nm=., obj=rep("Dark Grey", length(.)))} %>% c(dict.colors, .)

P.spotify_genre_streams_per_release <- ggLinegraph(DT.genre_s_vs_d, color="genre", x="month", y="avg_streams_per_release", facet_formula=". ~ cat_mid_front", facet_scale="free", title="Spotify Genre breakdown\nshowing Average Number of Streams per relase", dict.colors=dict.colors, dict.fills=NULL, dotsize.scale=2)

ggsave.out(P.spotify_genre_streams_per_release, open=TRUE)
ggsave(P.spotify_genre_streams_per_release, filename=plots.p("spotify_genre_streams_per_release", ext="png"), height=10, width=14)
.o(plots.p("spotify_genre_streams_per_release", ext="png"))