r.d("
year country_code labelid transac_type_abbr gross
2013           SE   15907                AS 20901
2013           SE   15907                 S  8474
2014           SE   15907                AS  4638
2014           SE   15907                 S   884
2015           SE   15907                AS  5499
2015           SE   15907                 S  1332
") %>% 
ingestIntoSQL(tbl="window_test", schema="testing", wh="LOOKER_WH_LARGE", drop=TRUE)


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

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 year, labelid)
  FROM subtotals
)
WHERE transac_type_abbr = 'S'
------------------------------------------------------------


There is such a thing as a window clause;  its purpose is to avoid retyping
It might not be supported in Snowflake

SELECT
  LAG(first_name, 1) OVER w "prev",
  first_name, 
  LEAD(first_name, 1) OVER w "next"
FROM people
WINDOW w AS (ORDER first_name)
ORDER BY first_name DESC

---------------------------------------------------------------------------------------------------
PROBLEMS:  

WHAT I WANT:  
    Yr .. Country .. Label .. TransType .. Total_Gross_For_Label_for_Year_for_TransType .. Total_Gross_For_Label_for_Year .. Rank_by_label_and_Year

What is the problem? 
If I include transtype, 
    it ranks each labelid-transtype group. 
      In other words, a label''s AdSup sales are 
      given a rank independantly of the label''s Premium sales
    Whereas, what I actually want is for all of the labels sales to be considered as one. 
The problem is in the ORDER BY clause inside the window. 
    ORDER BY SUM(gross)
Ideally, I would need to window _that_ sum as well
    ORDER BY SUM(gross) OVER (PARTITION by year(activity_month)) DESC
    but this fails
(There is a second problem, in which this will necessarilly result in ties; I think dense_rank() instead of rank() resolves this)

    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(sgross) OVER (PARTITION BY yr)) desc) AS label_rank_for_year
       --   ALSO TRIED: 
       -- , 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='SE'
             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
---------------------------------------------------------------------------------------------------