Skip to content
Advertisement

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.

Advertisement

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

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement