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 |
--------+-------------+------------+

You might be able to do something using [match_recognize](https://stackoverflow.com/a/64543576/230471), though I didn't quite follow how you got the counts in your example. — William Robertson, Nov 12 '20 at 20:00

score 0 · Answer 1 · answered Nov 12 '20 at 19:45

0

You just need to include the columns you want in the select:

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

answered Nov 12 '20 at 19:45

Gordon Linoff

1,242,037
58
646
786

Thanks, but this wont solve the "sequentialy" problem. I need to return only users that pass by the same status sequentialy, like the example i put. – Malkath Nov 12 '20 at 19:51

score 0 · Accepted Answer · answered Nov 12 '20 at 19:50

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.)

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

2 Answers2