I need help with a query and I cannot wrap my head around what would be a good approach to deal with it.
I have the following data in the following shape:
| PK | ticket_number | timestamp | previous_state | current_state |
|---|---|---|---|---|
| 1 | 1 | 2022-05-01 11:55:00 | Under Consideration | |
| 2 | 1 | 2022-05-01 12:00:00 | Under Consideration | Backlog |
| 3 | 1 | 2022-05-01 12:00:00 | Backlog | Development |
| 4 | 1 | 2022-05-05 13:00:00 | Development | Review |
| 5 | 1 | 2022-05-01 13:05:00 | Review | Development |
| 6 | 1 | 2022-05-05 13:10:00 | Development | Done |
I want to calculate the duration of each stage that the ticket went through. However, for this I need to be able to match correctly the previous timestamp, and currently in my data it is possible that 2 states might have the same timestamp, which makes it difficult to use the LAG window function to get correctly the timestamp. But I could still get the correct order of the events because we have the previous state available in the table.
So, how can I make sure that I get the correct order of the events for duplicated timestamps, utilizing the previous_state column to get the previous timestamp.
The desired output would be this:
| PK | ticket_number | timestamp | previous_state | current_state | previous_pk | previous_timestamp |
|---|---|---|---|---|---|---|
| 1 | 1 | 2022-05-01 11:55:00 | Under Consideration | NULL | NULL | |
| 2 | 1 | 2022-05-01 12:00:00 | Under Consideration | Backlog | 1 | 2022-05-01 11:55:00 |
| 3 | 1 | 2022-05-01 12:00:00 | Backlog | Development | 2 | 2022-05-01 12:00:00 |
| 4 | 1 | 2022-05-05 13:00:00 | Development | Review | 3 | 2022-05-01 12:00:00 |
| 5 | 1 | 2022-05-01 13:05:00 | Review | Development | 4 | 2022-05-05 13:00:00 |
| 6 | 1 | 2022-05-05 13:10:00 | Development | Done | 5 | 2022-05-01 13:05:00 |