sqlsnowflake-cloud-data-platformdense-rank

Rank over lateral flatten

I'm curious on how to do a rank over group of values on lateral flatten.

So let's say I have a column that looks like:

id     tree
1     AB -> BC -> CD
2     A -> Z
3     B

I would like to get a result looking like this:

id     tree                flattened        ranker
1     AB -> BC -> CD          AB             1
1     AB -> BC -> CD          BC             2
1     AB -> BC -> CD          CD             3
2     A -> Z                   A             1
2     A -> Z                   Z             2
3     B                        B             1

I have implemented the lateral flatten but I don't know how to rank it. Any help would be highly appreciated. Thank you

Solution

  • When you execute a lateral flatten, one of the returned columns is the INDEX. That is the equivalent of the rank that you are looking for, except the values start at 0.

    https://docs.snowflake.com/en/sql-reference/functions/flatten.html