##   Parts that will change per iteration
##   (1) country_code
##   (2) Metadata
##   (3) Group by sequence (dependent on Metadata)
##   (4) Column Names
##   (5) N
##   (6) maxMonth
##   
  meta <- {
    list(
      labels = c("labelid", "label_name", "label_priority", "label_owner", sfBoolCase("label_is_dthree", "is_D3"), supply_chain_group="label_sc_group", "label_country")
    , releases = c(upc="releaseid", "release_name", release_date="release_releasedate", "release_marketing_priority", "release_version", sfBoolCase("release_is_compilation"), sfBoolCase("release_is_deleted"), "release_genre")
    , tracks = c("isrc", "track_name")
    )
  }


get_query_top_labels <- function(country_code, maxMonth, N=10000) {

sprintf("
WITH TRank AS (
   SELECT year(activity_month) AS yr
        , country_code
        , labelid
        -- metadata additional
        , label_name AS label_name
        , label_priority AS label_priority
        , label_owner AS label_owner
        , CASE label_is_dthree WHEN 1 THEN 'Y' ELSE 'N' END AS is_D3
        , label_sc_group AS supply_chain_group
        , label_country AS label_country
        -- Ranks & Gross
        , rank() over (PARTITION BY year(activity_month) ORDER BY SUM(gross) desc) AS label_rank_for_year
        , SUM(gross) AS label_total_gross_for_year
   FROM   bi.accounting
   WHERE  (
          country_code='%1$s'
     AND  storeid=286
     AND  year(activity_month) BETWEEN 2013 and 2015
     AND  month(activity_month) <= %2$i
          )
   GROUP BY   1, 2, 3, 4, 5, 6, 7, 8 , 9
) 

,  subtotals AS (
  SELECT
      year(activity_month) AS yr
    , country_code -- just to be safe; but not actually needed
    , labelid
    , transac_type_abbr
    , SUM(gross) AS subtotalgross
  FROM BI.accounting
   WHERE  (
          country_code='%1$s'
     AND  storeid=286
     AND  year(activity_month) BETWEEN 2013 and 2015
     AND  month(activity_month) <= %2$i
          )
  GROUP BY 1, 2, 3, 4
)

SELECT tr.*, label_percent_premium_revenue_for_year 
FROM 
(
  SELECT yr, country_code, labelid
      , label_percent_by_revenue_for_year AS label_percent_premium_revenue_for_year
  FROM  
  (
    SELECT *, 
      -- # the TO_NUMERIC portion is only needed because there is a bug in snowflake
      TO_NUMERIC(subtotalgross, 38, 10) / sum(subtotalgross) OVER (PARTITION BY yr, labelid) AS label_percent_by_revenue_for_year
    FROM subtotals
  )
  WHERE transac_type_abbr = 'S'
) sb
JOIN TRank tr
ON sb.yr = tr.yr AND sb.country_code = tr.country_code and sb.labelid = tr.labelid
WHERE tr.labelid in 
(
  SELECT DISTINCT labelid
  FROM TRank
  WHERE label_rank_for_year <= %3$i
)
ORDER BY sb.yr ASC, label_rank_for_year ASC
"

, country_code        ## %1$s
, maxMonth  ## %2$i
, N         ## %3$i
)

}