CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
micho
micho

Reputation: 5

SQL RANK() window function

I have a table that looks like this:

question_id    message_id    responder
          1             1    user_a
          1             2    user_b
          1             3    user_b
          1             4    user_a

I currently have RANK() OVER (PARTITION BY question_id, responder ORDER BY message_id) but that produces this:

question_id    message_id    responder    rank()
          1             1    user_a            1
          1             2    user_b            1
          1             3    user_b            2
          1             4    user_a            2

Desired output:

question_id    message_id    responder    rank()
          1             1    user_a            1
          1             2    user_b            2
          1             3    user_b            2
          1             4    user_a            3

Upvotes: 0

Views: 182

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

It seems you are wanting to tracking when the user id changes when ordered by message id. Which is what CONDITIONAL_CHANGE_EVENT does for you

CONDITIONAL_CHANGE_EVENT(responder) OVER (PARTITION BY question_id ORDER BY message_id) b

Upvotes: 2

Related Questions