Snowflake - Dense_rank starting at 2 rather than 1-CodePudding

I'm using the following statement in a query:

 CASE WHEN apercent IS NULL THEN NULL ELSE dense_rank() over (partition by adate order by apercent desc) END as arank

For some reason the ranked results are starting at 2 rather than 1. There are no tied values, but there are some NULL values. I'm assuming it's because of the NULLs. Any way to update this to force the rank to begin at 1?

Current Results:

arank	apercent
2	23.57
3	13.61
4	10.67
5	6.14
6	6.00
7	5.79
8	5.57
9	5.27
10	4.80
NULL	NULL

Desired Result:

arank	apercent
1	23.57
2	13.61
3	10.67
4	6.14
5	6.00
6	5.79
7	5.57
8	5.27
9	4.80
NULL	NULL

CodePudding user response：

A simple solution is to use NULLS LAST:

(CASE WHEN apercent IS NOT NULL
      THEN dense_rank() over (partition by adate order by apercent desc nulls last)
 END) as arank

Alternatively, you could partition by NULL values so they are not included in the counting for non-NULL values:

(CASE WHEN apercent IS NOT NULL
      THEN dense_rank() over (partition by adate, apercent is null order by apercent desc)
 END) as arank

CodePudding user response：

If your percentages can't be negative, you could mimic nulls last by using coalesce

case when apercent is null then null 
     else dense_rank() over (partition by adate order by coalesce(apercent,0.00) desc) 
end as arank