## NEW SQL
# moved to 3 :     Q <- sprintf(paste0(
# moved to 3 :                   "SELECT  \n",
# moved to 3 :                   "        userid,  \n",
# moved to 3 :                   "        country,  \n",
# moved to 3 :                   "        mobile,  \n",
# moved to 3 :                   "        MAX(%s) AS lastStream_byUCnDPMb, \n",
# moved to 3 :                   "        DATE_TRUNC('month', download_datetime) AS month, \n",
# moved to 3 :                   "        product,  \n",
# moved to 3 :                   "        count(*) AS total_streams_perCnDPUMb  \n",
# moved to 3 :                   "FROM staging_raw_spotify \n",
# moved to 3 :                   "WHERE %1$s >= %s \n",
# moved to 3 :                   "GROUP BY month, product, mobile, country, userid  \n",
# moved to 3 :                   "ORDER BY month, product, lastStream_byUCnDPMb, mobile, country  DESC \n"
# moved to 3 :                 ) , dateCol, minDate)
# moved to 3 : 
# moved to 3 : 
# moved to 3 :     DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb <- runQry(Q)
# moved to 3 :     notify("Qry Done running.. beginning Saving")
# moved to 3 :     jesusForData(DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb, info="Now also includes MAX DATE per product-customer-mobile-country")
# moved to 3 :     notify("Saving Complete. BACK TO WORK!")



## Conversion
{
  ### Plan can be changed for three reasons
  ###    (1)  Conversion  (Product changed)
  ###    (2)  Moved       (Country changed)
  ###    (3)  Device Use  (Mobile  changed)

  ## Find customers with more than one product tier
  DT.Spotify.Counts.plusMAX_MIN_DATE.byUCnDPMb

  DT.UserProduct <- DT.mini[, list(MonthsUsed=.N), keyby=list(userid, product)]
  Customers.withMultiProduct <- DT.UserProduct[, .N, by=userid][N>1, userid]

  setkeyIfNot(DT.mini, "userid")

  ## Note to self, this method is faster than alternative. See Benchmark file
  DT.Upgraders <- DT.mini[Customers.withMultiProduct]
  cleanSpotify_(DT.Upgraders)
  DT.Upgraders <- DT.Upgraders[, list( year[-1L], month[-1L]
                           , ProductFrom = product[- (.N) ]
                           , ProductTo = product[-1L]
                           , ProductDiff=diff(as.integer(product))
                          )
                    , keyby=userid]
  colsOrdered <- c("userid", "year", "month", "ProductFrom", "ProductTo", "ProductDiff")
  setcolorderpt(DT.old, colsOrdered)

  jesusForData(DT.Upgraders)
  notify("DONE")
}



## Number of Unique Users in a Month, by month-product-country
## Number of Streams in a Month, by month-product-country
  kCols_DateProdCntry <- c("month", "product", "country")

  DT.Spotify.Counts.agg <- 
      merge(  DT.mini[, list(Total_Streams=sum(total_streams_perCnDPUMb), Avg_Streams=mean(total_streams_perCnDPUMb), Median_Streams=median(total_streams_perCnDPUMb)), keyby=keyColsProd]
            , DT.mini[, list(Unique_Users = .N), keyby=keyColsProd]
           )


DT.feb[, 
       by=keyColsAgg]

                              userid      month country mobile lastStream_byUCnDPMb product total_streams_perCnDPUMb
 1: 000001237d0dfa1443359ef2b26571f6 2013-06-01      PL  FALSE                      2013-06-15    Open                 2
 2: 000001237d0dfa1443359ef2b26571f6 2013-12-01      PL  FALSE                      2013-12-24    Open                37
 3: 00000127302642d9f3ffb2135913c9fe 2013-08-01      US   TRUE                      2013-08-21 Premium                 1
 4: 0000016eb4e0d336c42f9d4992945762 2013-08-01      DE  FALSE                      2013-08-22    Open                 7
 5: 0000016eb4e0d336c42f9d4992945762 2013-10-01      DE  FALSE                      2013-10-04    Open                 5
 6: 0000016eb4e0d336c42f9d4992945762 2013-11-01      DE  FALSE                      2013-11-17    Open                 1
 7: 0000016eb4e0d336c42f9d4992945762 2013-12-01      DE   TRUE                      2013-12-14    Open                 1
 8: 0000016eb4e0d336c42f9d4992945762 2013-12-01      DE  FALSE                      2013-12-22    Open                 2
 9: 0000016eb4e0d336c42f9d4992945762 2014-01-01      DE  FALSE                      2014-01-19    Open                 1
10: 0000016eb4e0d336c42f9d4992945762 2014-02-01      DE  FALSE                      2014-02-09    Open               114
11: 00000193ccf475696466d93cc133aade 2013-11-01      US  FALSE                      2013-11-26    Open                68
12: 00000193ccf475696466d93cc133aade 2013-12-01      US  FALSE                      2013-12-04    Open                 7