lib(reshape2)
DT.test_ranks <- data.table:::dcast.data.table(DT.test, country_code + labelid ~ year, value.var="label_rank_for_year")
DT.test_gross <- data.table:::dcast.data.table(DT.test, country_code + labelid ~ year, value.var="label_total_gross_for_year")
setnames(DT.test_ranks, as.character(years_using), paste0("label_rank_for_", years_using))
setnames(DT.test_gross, as.character(years_using), paste0("label_total_gross_for_", years_using))
DT.test_merge <- merge(DT.test_ranks, DT.test_gross)
setcolorderpt(DT.test_merge, endCols=c(grossCols, rankCols))

DT.country[, c("year", "year.01", "year.02") := NULL]
DT.country[, c("label_name", "label_priority", "label_owner", "label_is_dthree", "label_sc_group", "label_country") := NULL]
identical(DT.country, DT.test_merge)


WINDOW w AS (ORDER first_name)

{
  sfQry("
    SELECT yr
         , country_code
         , labelid
         , transac_type_abbr
         , sgross as subtotal_gross
         , SUM(sgross) over (PARTITION BY yr, country_code, labelid) AS label_total_gross_for_year
         , rank() over (PARTITION BY yr ORDER BY SUM(sgross) desc) AS label_rank_for_year
       --   THIS DID NOT WORK EITHER         
         , rank() over (PARTITION BY yr ORDER BY (sum(SUM(sgross)) OVER (PARTITION BY yr)) desc) AS label_rank_for_year2
   FROM (
           SELECT year(activity_month) AS yr
                , country_code
                , labelid
                , transac_type_abbr
                , SUM(gross) AS sgross --label_subtotal_gross_for_year_for_transtype
           FROM   bi.accounting
           WHERE  (
                  country_code='DE'
             AND  storeid=286
             AND  year(activity_month) BETWEEN 2013 and 2015
             AND  month(activity_month) <= 5
             AND  labelid in (5, 12, 184, 15907)
                  )
           GROUP BY   1, 2, 3, 4
   )
   GROUP BY 1, 2, 3, 4, 5, 

   ORDER BY yr ASC, subtotal_gross, label_rank_for_year ASC
    ")
} -> DT.test_new ; DT.test_new; catn("inner query: "); DT.test_inner



{
qTest_new_rank <- "
   SELECT year(activity_month) AS yr
        , country_code
        , labelid
        , transac_type_abbr
   --     , SUM(gross) over (PARTITION BY year(activity_month)) AS label_total_gross_for_year
   --   , rank() over (PARTITION BY year(activity_month) ORDER BY SUM(gross) desc) AS label_rank_for_year
        , rank() over (PARTITION BY year(activity_month) ORDER BY SUM(gross) OVER (PARTITION by yr) DESC) AS label_rank_for_year
        , SUM(gross) AS gross --label_subtotal_gross_for_year_for_transtype
   FROM   bi.accounting
   WHERE  (
          country_code='DE'
     AND  storeid=286
     AND  year(activity_month) BETWEEN 2013 and 2015
     AND  month(activity_month) <= 5
     AND  labelid in (5, 12, 184, 15907)
          )
   GROUP BY   1, 2, 3, 4
   ORDER BY yr ASC, label_rank_for_year ASC
"
# DT.test_rank <- sfQry(qTest); DT.test_rank
# DT.test_rank_no_partition <- sfQry(qTest_no_partition); DT.test_rank_no_partition
# DT.test_rank_no_partition_ttype <- sfQry(qTest_no_partition_ttype); DT.test_rank_no_partition_ttype
DT.test_newrank <- sfQry(qTest_new_rank); DT.test_newrank
}
DT.test_rank_no_partition[order(label_total_gross_for_year)]
DT.test_rank[order(label_total_gross_for_year)]

{
  sfQry(
"
------------------------------------------------------------
WITH subtotals AS (
  SELECT
      year(activity_month) AS yr
    , country_code -- just to be safe
    , labelid
    , transac_type_abbr
    , SUM(gross) AS gross
  FROM BI.accounting
   WHERE  country_code='DE'
     AND  storeid=286
     AND  year(activity_month) BETWEEN 2013 and 2015
     AND  month(activity_month) <= 5
     AND LABELID between 100 AND 200
  GROUP BY 1, 2, 3, 4
  ORDER BY LABELID
  LIMIT 20000
)

SELECT * FROM 
(
  SELECT * 
  FROM  
  (
    SELECT *, 
      -- # the TO_NUMERIC portion is only needed because there is a bug in snowflake
      TO_NUMERIC(gross, 38, 10) / sum(gross) OVER (PARTITION BY yr, labelid) AS label_percent_premium_for_year
    FROM subtotals
  )
  WHERE transac_type_abbr = 'S'
) 
JOIN


ORDER BY labelid, yr
------------------------------------------------------------
"
)
} %>% setkeyIfNot(yr, transac_type_abbr, organize=TRUE) %>% {.} -> DT.test; print(DT.test)
DT.test[labelid == 184]


sfQry("
SELECT
    year
  , labelid
  , transac_type_abbr
  , sum(gross) as gross
FROM testing.window_test
-- WHERE (filters)
GROUP BY 1, 2, 3
  ")







DT.test <- sfQry(qTest)
DT.test[, list(rmin = min(label_rank_for_year), rmax = max(label_rank_for_year)), keyby=labelid][rmin > 1000]
DT.test[labelid == 15907]

sfQry("DESC TABLE bi.accounting") %P% .


SELECT *
FROM (q)
WHERE labelid in (
  SELECT distinct labelid
  FROM (q)
  WHERE rank <= N
  )