How to Add Extra Rules vs the Previous Row when Ranking SQL?

user_4518612 0
Viewed 29

Let's say I have a table that shows the changes of customer support ticket.

timestamp date status rank dense_rank row_number
2021-03-22 05:03:22 2021-03-22 OPEN 1 1 1
2021-03-24 07:10:05 2021-03-24 DECLINED 2 2 2
2021-04-04 09:01:10 2021-04-24 DECLINED 3 3 3 (at random)
2021-04-04 09:01:10 2021-04-24 OPEN 3 3 4 (at random)

If we take a look at the 3rd and 4th records, they are the same exact timestamp.

And I want to sort this consistently based on the timestamp ascendingly (not row_number because it is at random, not rank and dense rank because it is not going to be ascending)

Now we have an additional rule, such as a ticket can't have a sequential same status. In the case of above & incorporating the rule, the sequence of the record should be:

open (2021-03-22) - declined (2021-03-24) - open (2021-04-24) - declined (2021-04-24)

Are there any ways to incorporate this additional rule into rank() over (partition by ... order by ...)?

snowflake-cloud-data-platform sql
1 Answers

Assuming that you want this sequence over the entire table, we can try:

SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp,
                                      CASE status WHEN 'OPEN' THEN 1 ELSE 2 END) AS rn
FROM yourTable
ORDER BY timestamp, CASE status WHEN 'OPEN' THEN 1 ELSE 2 END;

The second level of the ORDER BY clause sorts open records before records of any other status. If you wanted this sequence repeated for a given set of records within the table, then you would want to add a PARTITION BY clause to the call to ROW_NUMBER.

user_18632290
Related