screen -xRR UserCountSpotify
R

setScience("Spotify_Stream_Count", subProj="UserCount", subl=TRUE, load=FALSE)

wh <- "LOOKER_WH_LARGE"
dbname <- "prod"

tbl.streams <- "staging_raw_spotify_v2"
schema <- "production"

setSnowflake(wh=wh, dbname=dbname)

headDB(tbl=tbl.streams, schema=schema)

## we cannot breakdown usercounts by account access, 
##   since that will skew the unique user counts
DT.user_access_counts <- sfQry(wh=wh, 
  "SELECT number_of_access, count(*) 
  FROM (
    SELECT user_id, count (Distinct user_access) as number_of_access
    FROM production.staging_raw_spotify_v2
    WHERE tmstamp >= '2015-03-01'
      AND user_access != 'deleted'
    GROUP BY 1
  ) GROUP BY 1")
## An alternate option is to count the streams per user per day per access type
##  and then aggregate that option, taking a weighted value for each user as 
##  number of users per access = sum per user of (days in acess / total days in month)



{
Q.monthly_user_counts <- 
"SELECT S.*, C.country_name, C.region_group, C.Continent
        , C.coremarkets_country_code, C.coremarkets_country_name 
FROM (
  SELECT
    date_trunc('month', tmstamp)::date AS month
  , user_country as country_code
  , gender as user_gender
  , count(distinct user_id) as number_of_unique_users
  , count(*) as streams
  FROM production.staging_raw_spotify_v2
  WHERE user_access != 'deleted'
  GROUP BY 1, 2, 3
) S
JOIN bi.country_view C
ON S.country_code = C.country_code
"

  tbl.counts <- "monthly_user_counts"
  schema.counts <- "spotify"

  Qry.create <- sprintf("CREATE OR REPLACE TRANSIENT TABLE %s AS %s", dbschematbl(dbname=dbname, schema=schema.counts, tbl=tbl.counts), Q.monthly_user_counts)

  sfQry(wh=wh, Qry.create)
}


{
Q.monthly_user_counts_by_product_less_accurate <- 
"SELECT S.*, C.country_name, C.region_group, C.Continent
        , C.coremarkets_country_code, C.coremarkets_country_name 
FROM (
  SELECT
    date_trunc('month', tmstamp)::date AS month
  , CASE WHEN user_access in ('free', 'open') THEN 'Ad-Supported' ELSE 'Premium' END AS adsupported_vs_premium
  , user_country as country_code
  , gender as user_gender
  , count(distinct user_id) as number_of_unique_users
  , count(*) as streams
  FROM production.staging_raw_spotify_v2
  WHERE user_access != 'deleted'
  GROUP BY 1, 2, 3, 4
) S
JOIN bi.country_view C
ON S.country_code = C.country_code
"

  tbl.counts <- "monthly_user_counts"
  schema.counts <- "spotify"

  Qry.create <- sprintf("CREATE OR REPLACE TRANSIENT TABLE %s AS %s", dbschematbl(dbname=dbname, schema=schema.counts, tbl="monthly_user_counts_by_product_less_accurate"), Q.monthly_user_counts_by_product_less_accurate)

  sfQry(wh=wh, Qry.create, verbose=TRUE)
}