R
setScience("misc", subProj="GSA", load=FALSE)

setGitBranchToSystem()
.g()

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

- Top 100 labels by revenue in Germany, Austria, Germany
- Top 100 artists by revenue in Germany, Austria, Germany
- Top 100 albums by revenue in Germany, Austria, Germany
- Top 100 tracks by revenue in Germany, Austria, Germany


tbl <- "fact_sales"
tbl <- sprintf("%s F JOIN bi.label_view L ON F.labelid = L.labelid", tbl)
byCs <- c("artistid", "labelid", "releaseid", "trackid")
for (byC in byCs)
colsToPull <- c("activityyear", sprintf("F.%s", byC), Supply_Chain_Group = "L.label_sc_group")
whereIn <- list(countryid=c(4, 11, 16), activityyear=c(2014, 2015))
# subQ <- makeQry(tbl=tbl, aggFunc="sum", colsToPull=colsToPull, colsToAgg=c("revenue"="gross"), whereIn=whereIn, limit=NULL)
subQ <- makeQry(tbl=tbl, aggFunc="sum", colsToPull=colsToPull, colsToAgg=c("revenue"="gross"), whereIn=whereIn, limit=250*2)
subQ %<>% gsub("(\\s+LIMIT)", "\nORDER BY ACTIVITYYEAR, REVENUE DESC\n\\1", .)
DT2 <- subQ %>% sfQry

-------------------------------------------------
SELECT   activityyear, F.labelid, L.label_sc_group AS "Supply_Chain_Group"
      , SUM(gross) AS "revenue"
      , PARTITION()
FROM  production.fact_sales F 
JOIN bi.label_view L 
ON F.labelid = L.labelid    
WHERE (countryid in (4, 11, 16) 
  AND activityyear in (2014, 2015))  
GROUP BY 1, 2, 3
ORDER BY ACTIVITYYEAR, REVENUE DESC
LIMIT 500
-------------------------------------------------

RANK() OVER (PARTITION BY fact_sales.ACCOUNTINGYEAR ORDER BY sum(fact_sales.GROSS) DESC) as "_rank"


-------------------------------------------------
SELECT * FROM (
  SELECT ww.*, MIN("_rank") OVER (PARTITION BY "country_view.region_group","fact_sales.label_id") as "_min_rank"
  FROM (
SELECT 
  fact_sales.LABELID AS "fact_sales.label_id",
  country_view.region_group AS "country_view.region_group",
  fact_sales.ACCOUNTINGYEAR || '-' || (CONCAT('Q', fact_sales.ACCOUNTINGQUARTER)) AS "fact_sales.accounting_year_and_quarter",
  sum(fact_sales.GROSS) AS "fact_sales.gross_revenue",
RANK() OVER (PARTITION BY fact_sales.ACCOUNTINGYEAR || '-' || (CONCAT('Q', fact_sales.ACCOUNTINGQUARTER)) ORDER BY sum(fact_sales.GROSS) DESC) as "_rank"
FROM PRODUCTION.FACT_SALES_WITH_ROWS AS fact_sales
LEFT JOIN BI.COUNTRY_VIEW AS country_view ON fact_sales.COUNTRYID = country_view.COUNTRYID

WHERE 
  (fact_sales.ACCOUNTINGYEAR IN (2014,2015)) AND 
  (CONCAT('Q', fact_sales.ACCOUNTINGQUARTER) = 'Q1') AND 
  (country_view.region_group = 'GSA')
GROUP BY 1,2,3
ORDER BY 4 DESC  ) AS ww
) AS xx  WHERE xx."_min_rank" <= 500 LIMIT 30000
-------------------------------------------------



setSnowflake(wh=wh, dbname=dbname)
get_dim_country(refresh=TRUE)[country_name %in% c("Germany", "Asutria", "Switserland")]
DT.country <- sfQry("SELECT * FROM bi.country_view order by 1")
DT.country[country_code %in% c("DE", "AS", "AU", "CH", "CZ", "CN") | ]
DT.country[region_group %in% "GSA"]


   SELECT ARTISTID
        , LABELID
        , STOREID
        , GENREID
        , RELEASEID
        , TRACKID
        , COUNTRYID
        , TRANSACTIONTYPEID
        , CATALOGID, ISRCID, IMPRINTID, GROSS, SALES, ACTUAL_NET, ADJUSTED_GROSS
        , DISTRIBUTION_FEES, DPD_PUBLISHING, NET_RECEIPT, OMS_FEES, PARTNER_SHARE
        , RINGTONE_PUBLISHING, RETAIL_PRICE, SUBACCOUNTID, DATECREATED
        , ORIGINAL_CURRENCY_ID, FX_SPREAD_FEE, PAYOUT_CURRENCY_ID, ACTIVITY_FX_RATE
        , FX_ADJUSTED_EXCHANGE_RATE, FX_GROSS, FX_ACTUAL_NET, FX_ADJUSTED_GROSS
        , FX_DISTRIBUTION_FEES, FX_DPD_PUBLISHING, FX_NET_RECEIPT, FX_OMS_FEES
        , FX_RINGTONE_PUBLISHING, ACCOUNTINGYEAR
        , ACCOUNTINGQUARTER, ACCOUNTINGMONTH, ACTIVITYYEAR, ACTIVITYQUARTER, ACTIVITYMONTH
   FROM   production.fact_sales
   LIMIT  5