# Unique Users.r

# setScience(proj="Accounting", subl=FALSE, create=TRUE)

analytics()
tbls <- qShowTables(tolower( sapply(strsplit(names(storesUsing), " "), "[[", 1)  ), justnames=TRUE)
tbls <- tbls[!grepl("itunes_iradio", tbls)]

## Neither amazon, deezer, or rdio provides UID


getMonthlyUniqueCounts <- function(tbl, userCol, dateCol, minDate, verbose=TRUE) {

  ## storename, strip away the staging_raw_
  tbl.short <- gsub("staging_raw_", "", tbl)

  ## Create the query
  Q <- sprintf(paste0(
          "SELECT %s AS storename, \n", 
          "       DATEPART(month, %s) AS month, \n",
          "       DATEPART(year, %1$s) AS Year, \n",
          "       count(DISTINCT %s) AS Unique_users \n",
          "FROM %s \n",
          "WHERE %1$s > '%s' \n",
          "GROUP BY DATEPART(month, %1$s), DATEPART(year, %1$s)"
          )
          , tbl.short, dateCol, userCol, tbl, minDate)

  # Execute
  ret <- runQry(Q, verbose=verbose)

  setkey(storename, year, month)
  return(assign(".TMP.DT.MonthlyCount", ret, envir=globalenv()))
}

getMonthlyUniqueCounts <- function(tbl, userCol, dateCol, minDate, verbose=TRUE) {

  ## storename, strip away the staging_raw_
  tbl.short <- gsub("staging_raw_", "", tbl)

  ## Create the query
  Q <- sprintf(paste0(
          "SELECT %s AS storename, \n", 
          "       DATEPART(month, %s) AS month, \n",
          "       DATEPART(year, %1$s) AS Year, \n",
          "       count(DISTINCT %s) AS Unique_users \n",
          "FROM %s \n",
          "WHERE %1$s > '%s' \n",
          "GROUP BY DATEPART(month, %1$s), DATEPART(year, %1$s)"
          )
          , tbl.short, dateCol, userCol, tbl, minDate)

  # Execute
  ret <- runQry(Q, verbose=verbose)

  setkey(storename, year, month)
  return(assign(".TMP.DT.MonthlyCount", ret, envir=globalenv()))
}


















stores.using = c(itunes=1, spotify=286)
minDate <- "2012-09-01"

tbl     <- "staging_raw_itunes"
userCol <- "customer_identifier"

DT.itunes.uniqueusers.monthly <-
      getMonthlyUniqueCounts(tbl="staging_raw_itunes", userCol="customer_identifier", dateCol, minDate, verbose=TRUE)


Q <- sprintf(paste(
        "SELECT DATEPART(month, download_activity_date) AS month, \n",
        "       DATEPART(year, download_activity_date) AS Year, \n",
        "       count(DISTINCT %s) AS Unique_users \n",
        "FROM %s \n",
        "WHERE download_activity_date > '%s' \n",
        "GROUP BY DATEPART(month, download_activity_date), DATEPART(year, download_activity_date)")
      , userCol, tbl, minDate)
DT.itunes.monthly <- runQry(Q, verbose=TRUE)


tbl <- "staging_raw_spotify"
userCol <- "customerid"
dateCol <- "download_datetime"


Q <- sprintf("SELECT DATEPART(month, %s) AS month, \n\t DATEPART(year, %1$s) AS Year, \n\t count(DISTINCT %s) AS Unique_users \nFROM %s \nWHERE %1$s > '%s' \nGROUP BY DATEPART(month, %1$s), DATEPART(year, %1$s)", dateCol, userCol, tbl, minDate)

DT.spotify.monthly <- runQry(Q, verbose=TRUE)
  Q <- sprintf("SELECT DATEPART(month, %s) AS month, \n\t DATEPART(year, %1$s) AS Year, \n\t count(%s) AS Total_Streams \nFROM %s \nWHERE %1$s > '%s' \nGROUP BY DATEPART(month, %1$s), DATEPART(year, %1$s)", dateCol, countCol, tbl, minDate)


SELECT dayid, storeid, countryid, transactiontypeid, royaltydollar, units, uuid FROM  fact_analytics WHERE dayid > '%s'

 [1] "artistid"          "dayid"             "labelid"           "storeid"           "genreid"
 [6] "releaseid"         "trackid"           "countryid"         "transactiontypeid" "zipid"
[11] "catalogid"         "isrcid"            "imprintid"         "currencyid"        "storeuserid"
[16] "processeddaytime"  "royalty"           "royaltydollar"     "units"             "paidunits"
[21] "freeunits"         "uuid"              "subaccountid"