##   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
##   

## TEST: 
##             AND  labelid in (5, 12, 184, 15907)


  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("releaseid", "track_name")
    )
  }


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

setQry(
sprintf("
WITH TRank AS (
   SELECT year(activity_month) AS yr
        , country_code
        , labelid
        -- label 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
        -- artist_metadata
        , artistid
        , artist_name
        , artist_country
        -- release metadata
        , releaseid
        , release_name
        , release_releasedate AS release_date
        , release_marketing_priority
        , release_version
        , CASE release_is_compilation WHEN 1 THEN 'Y' ELSE 'N' END AS is_compilation
        , CASE release_is_deleted WHEN 1 THEN 'Y' ELSE 'N' END AS is_deleted
        , release_genre
        -- Ranks & Gross
        , rank() over (PARTITION BY year(activity_month) ORDER BY SUM(gross) desc) AS release_rank_for_year
        , SUM(gross) AS release_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
          )
   ---- DONT FORGET TO CHANGE GROUP BY FOR ADDED METADATA
   GROUP BY   1, 2, 3, 4, 5, 6, 7, 8 , 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
) 

,  label_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
)

,  artist_subtotals AS (
  SELECT
      year(activity_month) AS yr
    , country_code -- just to be safe; but not actually needed
    , artistid
    , 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
)

,  subtotals AS (
  SELECT
      year(activity_month) AS yr
    , country_code -- just to be safe; but not actually needed
    , releaseid
    , 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 Main.*
  , lsb.label_total_gross_for_year
  , lsb.label_percent_premium_revenue_for_year
  , asb.artist_total_gross_for_year
  , asb.artist_percent_premium_revenue_for_year

  , release_total_gross_for_year / lsb.label_total_gross_for_year AS release_as_percent_of_label_total_gross_for_year
  , release_total_gross_for_year / asb.artist_total_gross_for_year AS release_as_percent_of_artist_total_gross_for_year

  , release_percent_premium_revenue_for_year - lsb.label_percent_premium_revenue_for_year AS release_increase_from_label_percent_premium_revenue_for_year
  , release_percent_premium_revenue_for_year - asb.artist_percent_premium_revenue_for_year AS release_increase_from_artist_percent_premium_revenue_for_year
FROM 
(
          SELECT tr.*
              , sb.release_percent_premium_revenue_for_year 
          FROM 
          (
            SELECT yr, country_code, releaseid
                , release_percent_by_revenue_for_year AS release_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, releaseid) AS release_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.releaseid = tr.releaseid

          WHERE tr.releaseid in 
          (
            SELECT DISTINCT releaseid
            FROM TRank
            WHERE release_rank_for_year <= %3$i
          )
          ORDER BY sb.yr ASC, release_rank_for_year ASC
) Main
---------------- START ADDITIONAL LABEL DATA  --------
LEFT JOIN (
  SELECT 
    yr
  , country_code
  , labelid
  , sum(subtotalgross) as label_total_gross_for_year
  , 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 label_subtotals
  )
  WHERE transac_type_abbr = 'S'
  GROUP BY 1, 2, 3, 5
) lsb
ON Main.yr = lsb.yr AND Main.country_code = lsb.country_code and Main.labelid = lsb.labelid
---------------- END ADDITIONAL LABEL DATA  --------
---------------- START ADDITIONAL ARTIST DATA  --------
LEFT JOIN (
  SELECT 
    yr
  , country_code
  , artistid
  , sum(subtotalgross) as artist_total_gross_for_year
  , artist_percent_by_revenue_for_year AS artist_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, artistid) AS artist_percent_by_revenue_for_year
    FROM artist_subtotals
  )
  WHERE transac_type_abbr = 'S'
  GROUP BY 1, 2, 3, 5
) asb
ON Main.yr = asb.yr AND Main.country_code = asb.country_code and Main.artistid = asb.artistid
---------------- END ADDITIONAL ARTIST DATA  --------
;
"

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

}