minDate <- "2012-11-01"

DT.spotify.uniqueusers.monthly <- getMonthlyUniqueCounts(tbl="staging_raw_spotify", 
  countCol="customerid", dateCol="download_datetime", minDate=minDate) 

DT.itunes.uniqueusers.monthly   <- getMonthlyUniqueCounts(tbl="staging_raw_itunes", 
  countCol="customer_identifier", dateCol="download_activity_date", minDate=minDate) 


DT.spotify.streams.monthly <- getMonthlyUniqueCounts(tbl="staging_raw_spotify", countCol="customerid", dateCol="download_datetime", minDate=minDate) 

DT.itunes.streams.monthly  <- getMonthlyUniqueCounts(tbl="staging_raw_itunes", countCol="customer_identifier", dateCol="download_activity_date", minDate=minDate) 


jesusForData(DT.itunes.monthly, DT.spotify.monthly)




getMonthlyStreamsByStore <- function(tbl="fact_analytics", dateCol="d.displaydate", minDate="2012-11-01", storeids=c(1, 286), verbose=TRUE) {
## 5055  2012-11-01
  Q <- sprintf("SELECT st.storename AS storename, DATEPART(month, %s) AS month, \n\t DATEPART(year, %1$s) AS Year, \n\t sum(units) AS units, sum(paidunits) AS paidunits, sum(freeunits) AS freeunits \nFROM %s fa \nLEFT JOIN (SELECT dayid, displaydate FROM dim_day) d \nON fa.dayid=d.dayid\nLEFT JOIN (SELECT storeid, storename FROM dim_store) st \nON fa.storeid=st.storeid\nWHERE %1$s >= '%s' AND fa.storeid in (%s)\nGROUP BY storename, DATEPART(month, %1$s), DATEPART(year, %1$s)", dateCol, tbl, minDate, pasteC(storeids, C=", "))
  ret <- runQry(Q, verbose=verbose)

  try(setkey(ret, year, month))

  return(assign(".TMP.DT.MonthlyStream", ret, envir=globalenv()))
}

DT.monthly.streams <- getMonthlyStreamsByStore()



setScience("accounting", subl=FALSE, create=TRUE)
jesusForData(DT.monthly.streams)