This is the scrap work checking on Richard Clayderman for bullethead and owned and operated


sfWarehouseOn(wh="Science", size="XL")
setSnowflake(wh="Science", dbname="prod", schema="producton")

sfQry("select * from production.dim_artist limit 5")
sfQry("select * from production.dim_artist where artistname like '%Clayderman%' order by artistname")


whereTerms <- list(labelid = 14241, artistid = 103580, storeid=c(1, 286))

sfDesc(tbl="fact_analytics", schema="production")

colsToAgg <- c("PAIDUNITS", "FREEUNITS", "UNITS")
colsToPull <- c("artistid", "labelid", "storeid", date=sql1stOfMonth("download_activity_date"))
DT.clay <- sfQry(makeQry(tbl="fact_analytics", schema="production", colsToAgg=colsToAgg, colsToPull=colsToPull, where=whereTerms, minDate="2015-08-01", dateCol="download_activity_date"))

DT.clay[, month := month(date)]
DT.clay[, year := year(date)]

byCols <- setdiff(colsToPull, "download_activity_date") %>% c("month")
byCols <- c("year", "month", "storeid")
DT.clay_aggd <- aggregateDT(DT.clay, by=byCols, colsToAgg=tolower(colsToAgg))

setDBall(cluster=4)
qShowTables(schema="production", namelike="%apple%")

qry.apple <- setQry("SELECT item_artist, datestamp AS date, count(*) as streams FROM production.staging_raw_apple_music where datestamp >= '2015-08-01' AND item_artist ilike '%Richard Clayderman%' group by 1, 2 order by 1, 2")
DT.apple_raw <- runQry(qry.apple, cluster=4)

DT.apple_raw[, sum(streams), keyby=item_artist][, print(item_artist)]


DT.apple_raw[item_artist %in% c("Richard Clayderman ", "Richard Clayderman")]


qry.spotify <- setQry("SELECT TRACK_ALBUM_ARTIST, CAST(TO_CHAR (download_date, 'YYYY-MM-01') AS date) AS date, count(*) as streams FROM production.staging_raw_spotify_v2 where download_date >= '2015-08-01' AND TRACK_ALBUM_ARTIST rlike '%Clayderman%' group by 1, 2 order by 1, 2")
DT.spotify_raw <- sfQry(qry.spotify)


sfDesc("production.staging_raw_spotify_v2")
DT.spot_names <- sfQry("SELECT DISTINCT TRACK_ALBUM_ARTIST, TRACK_ARTISTS FROM production.staging_raw_spotify_v2 WHERE download_date >= '2015-08-01'")

DT.spot_names[grepl("clayde", track_album_artist, ignore.case=TRUE)]

qry.spotify <- setQry("SELECT TRACK_ALBUM_ARTIST, CAST(TO_CHAR (download_date, 'YYYY-MM-01') AS date) AS date, count(*) as streams FROM production.staging_raw_spotify_v2 where download_date >= '2015-08-01' AND TRACK_ALBUM_ARTIST rlike 'Richard Clayderman.*' group by 1, 2 order by 1, 2")
DT.spotify_raw <- sfQry(qry.spotify)





sfDesc("production.staging_raw_spotify_v2")


select sum(quantity), datestamp
from staging_raw_apple_music ss
inner join apple_id_mapping m on m.apple_id = ss.apple_identifier
inner join dim_release dr on dr.releaseid = m.orchard_release_id
where dr.labelid = 14241
group by datestamp
order by datestamp desc;



DT.fact <- runQry(cluster = 4, 
"SELECT
     download_activity_date AS date
    , SUM(PAIDUNITS) AS PAIDUNITS
    , SUM(FREEUNITS) AS FREEUNITS
    , SUM(UNITS) AS UNITS
FROM   production.fact_analytics
WHERE  (labelid=14241
 AND  storeid = 1 AND TRANSACTIONTYPEID = 1)
 AND  download_activity_date >= '2015-08-01'
GROUP BY 1
ORDER BY 1
")

DT.fact_aggd <- DT.fact[, list(fact_sum = sumn(units)), keyby=list(month=month(date))]
merge(DT.fact_aggd, DT.paulo_agg)

FROM prod.production.staging_raw_spotify_v2 AS staging_raw_spotify_v2
LEFT JOIN prod.bi.country_view AS bi_country_view ON country_code