Snowflake SQL rows with minimum and maximum values for each partition

I need to find the maximum and minimum values for a summed column over table partitions.

The inner query is:

select 
ss_store_sk,
d.d_year,
d.d_moy,
sum(ss_quantity) as total_sales,
rank() over (partition by ss_store_sk order by sum(ss_quantity) desc) as "rank"
from store_sales
join date_dim as d on d.d_date_sk = ss_sold_date_sk
where d.d_year != 2003 and d.d_moy != 1
group by 
ss_store_sk,
d.d_year,
d.d_moy

and this will produce a table like below.

SS_STORE_SK	D_YEAR	D_MOY	TOTAL_SALES	rank
182	1999	12	60836090	1
182	1998	11	60792623	2
182	2001	10	60615582	3
182	2000	9	60459371	4
18	1998	12	232323	1
18	2001	11	123244	2
18	2000	10	3422	3

I can get the max value row for TOTAL_SALES by the following:

with minmax as (
inner query
)
select * from minmax where "rank" =1

But how do I get the minimum value row for TOTAL_SALES for each SS_STORE_SK? The result I need will look like below. But it will be sufficient to just be able to get the min ranked rows by TOTAL_SALES separately.

SS_STORE_SK	D_YEAR	D_MOY	TOTAL_SALES	rank
182	1999	12	60836090	1
182	2000	9	60459371	4
18	1998	12	232323	1
18	2000	10	3422	3

I am using Snowflake SQL.

Answer

Use to rank() window function: one to rank in descending order of sum(ss_quantity) and one to rank in ascending order. Then just select first row from both the rankings.

with minmax as
(
   select 
   ss_store_sk,
   d.d_year,
   d.d_moy,
   sum(ss_quantity) as total_sales,
   rank() over (partition by ss_store_sk order by sum(ss_quantity) desc) as "rank"
   rank() over (partition by ss_store_sk order by sum(ss_quantity) ) as "rank2"
   from store_sales
   join date_dim as d on d.d_date_sk = ss_sold_date_sk
   where d.d_year != 2003 and d.d_moy != 1
   group by 
   ss_store_sk,
   d.d_year,
   d.d_moy
)
select * from minmax where rank =1 or rank2=1