I have a table that tracks state changes of an entity with timestamps. The schema of the table is as follows:

I want to compare the time it takes for the originalvalue to transition to the newvalue. For example, if the originalvalue is "Draft" and it becomes "Submitted", I need to find the corresponding newvalue in the next row and calculate the time between the originalvalue and newvalue changes.

To achieve this, I joined the table with itself on entityid where t1.newvalue = t2.originalvalue and t1.changetime <= t2.changetime:

  SELECT t1.entityid,
           t1.userid,
           t1.changetime as startStateTime
           t1.originalvalue as StartingState,
           t2.originalvalue as NextState,
           t2.changetime as nextStateTime
    FROM   table1 t1
    INNER JOIN table1 t2
               ON t1.entityid = t2.entityid
                  AND t1.newvalue = t2.originalvalue
                  and t1.changetime <= t2.changetime

However, this query sometimes results in duplicate rows where the same state transition appears multiple times with different timestamps. For example, the state "In Progress" appears multiple times, causing duplicates.

To filter these duplicates, I used the ROW_NUMBER() window function:

what i did it user row number

   WITH NumberedStates AS (
    SELECT t1.entityid,
           t1.userid,
           t1.changetime AS startStateTime,
           t1.originalvalue AS StartingState,
           t2.originalvalue AS NextState,
           t2.changetime AS nextStateTime,
           ROW_NUMBER() OVER (
               PARTITION BY t1.entityid, t1.originalvalue, t2.originalvalue
               ORDER BY t2.changetime ASC
           ) AS row_number
    FROM table1 t1
    INNER JOIN table1 t2
        ON t1.entityid = t2.entityid
        AND t1.newvalue = t2.originalvalue
        AND t1.changetime <= t2.changetime
)
SELECT entityid, userid, startStateTime, StartingState, NextState, nextStateTime
FROM NumberedStates 
WHERE row_number = 1

Although this works, the table is very large (millions of rows), and the query is slow. Is there a more efficient way to achieve the desired result or optimize this approach?