CODEWITHSUNDEEP
sqlnullsnowflake-cloud-data-platformpartitiondense-rank
Nick Knauer
Nick Knauer

Reputation: 4243

Dense Rank Skip Null Values Partition by Multiple

I have a table that looks like below: 

       Date      User      Product
 11/15/2019       123         NULL
 11/21/2019       123            A
 11/21/2019       123            A
 11/23/2019       123            B

I want to run a dense_rank function that will skip the null values.

Below is what I currently have: 

CASE WHEN PRODUCT IS NOT NULL 
THEN DENSE_RANK() 
OVER (PARTITION BY USER ORDER BY DATE ASC) 
ELSE 1 
END DENSE_RANK_OUTPUT

My current output: 

       Date      User      Product     DENSE_RANK_OUTPUT
 11/15/2019       123         NULL                     1
 11/21/2019       123            A                     2
 11/21/2019       123            A                     2
 11/23/2019       123            B                     3

My desired output is: 

       Date      User      Product        DESIRED_OUTPUT
 11/15/2019       123         NULL                     1
 11/21/2019       123            A                     1
 11/21/2019       123            A                     1
 11/23/2019       123            B                     2

Upvotes: 2

Views: 1494

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You are close. Just use another key in the partition by:

(CASE WHEN PRODUCT IS NOT NULL 
      THEN DENSE_RANK() OVER (PARTITION BY USER, (PRODUCT IS NOT NULL) ORDER BY DATE ASC) 
      ELSE 1 
 END) as DENSE_RANK_OUTPUT

Upvotes: 4

Related Questions