Conditional group by with window function in Snowflake query

Question

I have a table in Snowflake in following format:

create temp_test(name string, split string, value int)

insert into temp_test
values ('A','a', 100), ('A','b', 200), ('A','c',300), ('A', 'd', 400), ('A', 'e',500), ('B', 'a', 1000), ('B','b', 2000), ('B','c', 3000), ('B', 'd',4000), ('B','e', 5000)

First step, I needed only top 2 value per name (sorted on value), so I used following query to get that:

select name, split, value,
row_number() over (PARTITION BY (name) order by value desc) as row_num 
from temp_test
qualify row_num <= 2

Which gives me following resultset:

NAME    SPLIT   VALUE   ROW_NUM
A       e       500     1
A       d       400     2
B       e       5000    1
B       d       4000    2

Now, I need to sum values other than Top 2 and put it in a different Split named as "Others", like this:

NAME    SPLIT   VALUE   
A       e       500     
A       d       400     
A       Others  600        
B       e       5000    
B       d       4000
B       Others  6000     

How to do that in Snowflake query or SQL in general?

Answer 1

with data as (
    select name, split, value,
        row_number() over (partition by (name) order by value desc) as row_num 
    from temp_test
)
select
    name,
    case when row_num <= 2 then split else 'Others' end as split,
    sum(value) as value
from data
group by name, case when row_num <= 2 then row_num else 3 end