I have a table like this

| id      | state          | updatedate  |
|:--------|:---------------|:------------|
| 1       | state_review   | 1668603529  |
| 1       | state_review   | 1668601821  |
| 1       | state_review_2 | 1668601821  |
| 2       | state_review   | 1668601709  |
| 2       | state_review   | 1668600822  |
| 2       | state_review_2 | 1668600747  |
| 3       | state_review   | 1668559849  |
| 3       | state_review_2 | 1668539849  |
| 3       | state_review   | 1668529849  |
| 3       | state_review_2 | 1661599849  |
| 3       | state_review   | 1668599849  |

I'm trying to find how to count first occurance of changed state for all ids based on provided values, i have two incoming states from(state_review) to(state_review_2)

in this particular case there would be only three changed states that are going from state_review -> state_review_2

resulting table would look like this

| amount  |
|:--------|
| 3       |

I suspect window function might help with this but i'm not sure how to compare current state with all the others, states have to be ordered by id

Was trying to use this query, but that doesn't seem to work, instead of counting the latest unique transitions it counts all of them, if the first found transition doesn't match given states then skip the entire section for a certain id

SELECT
   COUNT(DISTINCT (
   CASE
      WHEN
         (
            q.state = 'state_review' 
            AND 'state_review' != 'state_review_2'
         )
      THEN
         ID 
   END
)) AS amount 
FROM
   (
      SELECT
         id,
         state
      FROM
         states_table
      WHERE
         updatedate >= 1668603529 
         AND updatedate <= 1671599849
         AND 
         (
            state = 'state_review' 
            OR state = 'state_review_2'
         )
      ORDER BY
         id, updatedate DESC
   )
   AS q

CodePudding user response：

Transitions between 2 predefined states can be obtained with a LAG function. Example with state_review and state_review_2

SELECT *
FROM(
    SELECT ID, LAG(State) OVER (PARTITION BY ID ORDER BY updatedate) As FromState, State, updatedate
    FROM States_table
) T
WHERE FromState = 'state_review' AND state = 'state_review2'

You can do variations of the above:

• To avoid double-counting when an id transitioned from state S1 to state S2 several times, change the sub-query with DISTINCT and without updatedate like so: SELECT DISTINCT ID, LAG(State) OVER (PARTITION BY ID ORDER BY updatedate) As FromState, State
• And of course, do SELECT COUNT(*) instead if all you want is the count.