Check records that have gone through the same status more than once in a row

Question

I have a status history table and I need to know which id_user pass by the same status sequentially.

Table structure

create table user (
    id_user number,
    user_name number,
    status_name char(1),
    created_at timestamp,
    primary key (id_user)
);

create table user_status_hist (
    id_user_status_hist number,
    id_user number,
    status_name char(1),
    updated_at timestamp,
    primary key (id_user),
    constraint fk foreign key (id_user) references user(id_user)
);

imagine that in the example below, for user 123 it has passed 2 times in a row for status B. How can i find all cases like this in my table?

select id_user, status_name, updated_at
from user_status_history
where id_user = 123;

--------+-------------+------------+
id_user | status_name | updated_at |
--------+-------------+------------+
    123 |           A | 2020-11-01 |
--------+-------------+------------+
    123 |           B | 2020-11-02 |
--------+-------------+------------+
    123 |           B | 2020-11-05 |
--------+-------------+------------+

With this query i find cases where i have a user that pass more than one time for the same status, but i cannot see if is sequential considering the updated_at column.

select count(*), idt_card
from user_status_hist
group by id_user, status_name
having count(*) > 1;

How can i get a output like this below? (the "count" column would be the number of times he went through these status sequentialy)

--------+-------------+------------+
id_user | status_name | count      |
--------+-------------+------------+
    123 |           A |          3 |
--------+-------------+------------+
    456 |           B |          2 |
--------+-------------+------------+
    789 |           B |          6 |
--------+-------------+------------+

Answer 1

Use the LAG() analytic function. Since you must use it in a comparison, and analytic functions can only be computed in the SELECT clause (which comes after all the filters were applied), you must compute the analytic function in a subquery and reference it in an outer query.

select id_user, status_name, updated_at
from   ( 
         select id_user, status_name, updated_at, 
                lag(status_name) over (partition by id_user order by updated_at) 
                                                                  as prev_status
          from  user_status_hist
       )
where  status_name = prev_status
;

This will give you the full details of all occurrences. If you then want to group by id_user and status_name and count, you already know how to do that. (You can do it directly in the outer query of the solution shown above.)