# DT.Spotify.Counts.by.user.product.date.r


minDate <- "2012-09-01"
dateCol <- "download_datetime"

Q <- sprintf(paste0(
              "SELECT  count(*) AS total_streams_perDPU,  \n",
              "        product,  \n",
              "        customerid,  \n",
              "        DATEPART(month, %s) AS month, \n",
              "        DATEPART(year, %1$s) AS year \n",
              "FROM staging_raw_spotify \n",
              "WHERE %1$s >= %s \n",
              "GROUP BY product, customerid, month, year \n",
              "ORDER BY year, month DESC \n"
            ) , dateCol, minDate)

DT.Spotify.Counts.by.user.product.date <- runQry(Q)

jesusForData(DT.Spotify.Counts.by.user.product.date, info="Streams aggd at user level. 2012_09 to present", verbose=TRUE)

# ============================================================

if (FALSE)  
....  seee file # 11

# ........................... 

# OLD   ### THIS IS THE OLD VERSION... BEFORE ADDING IN MAX(DATE)
# OLD   ### AGGREGATING IN  THE USER COUNTRY
# OLD   
# OLD   if (FALSE)  
# OLD   {
# OLD       rm(Q)
# OLD       minDate <- "2012-09-01"
# OLD       dateCol <- "download_datetime"
# OLD   
# OLD       Q <- sprintf(paste0(
# OLD                     "SELECT  \n",
# OLD                     "        customerid,  \n",
# OLD                     "        country,  \n",
# OLD                     "        mobile,  \n",
# OLD                     "        DATEPART(year, %1$s) AS year, \n",
# OLD                     "        DATEPART(month, %s) AS month, \n",
# OLD                     "        product,  \n",
# OLD                     "        count(*) AS total_streams_perCnDPUMb  \n",
# OLD                     "FROM staging_raw_spotify \n",
# OLD                     "WHERE %1$s >= %s \n",
# OLD                     "GROUP BY year, month, product, mobile, country, customerid  \n",
# OLD                     "ORDER BY year, month, product, mobile, country DESC \n"
# OLD                   ) , dateCol, minDate)
# OLD   
# OLD   
# OLD       DT.Spotify.Counts.by.date.product.mobile.country.user <- runQry(Q)
# OLD   
# OLD       if (!nrow(DT.Spotify.Counts.by.date.product.mobile.country.user)) {
# OLD         notify("ERROR")
# OLD         stop()
# OLD       }
# OLD   
# OLD       notify("Query Complete, starting Saving")
# OLD       jesusForData(DT.Spotify.Counts.by.date.product.mobile.country.user, info="Streams aggd at country and user level. 2012_09 to present", verbose=TRUE)
# OLD       notify("Saving Complete, starting table")
# OLD       ProductByMobileByCountry <- DT.Spotify.Counts.by.country.user.product.date[, table(country, product, mobile, month=paste0(year, "_", month))]
# OLD       notify("Table Complete.\nALL DONE")
# OLD   }
# OLD