sql - How to count the change of some values within a column from 1 value to the next while having some common values in another column?

Question

Let's say there are users of a product, and the table has:

1. a column of the month the user started to use the product
2. a column for the user id
3. a column for the status of the user in that month

I would like to get a field that identify the change of status of any user from 1 month to the next month and count how many "active_to_idle" are there, and how many "idle_to_active" are there.

In this case, I would like the SQL query to return active_to_idle = 1 and idle_to_active = 1.

I tried using subquery but failed to give the results I wanted. I tried "CASE WHEN" like this:

select month, userid, segment,
case
when 
((month = 1 and segment = 'active') and (month = 2 and segment = 'idle')) OR
((month = 2 and segment = 'active') and (month = 3 and segment = 'idle')) OR
((month = 3 and segment = 'active') and (month = 4 and segment = 'idle'))
then 'active_to_idle'
when 
((month = 1 and segment = 'idle') and (month = 2 and segment = 'active')) OR
((month = 2 and segment = 'idle') and (month = 3 and segment = 'active')) OR
((month = 3 and segment = 'idle') and (month = 4 and segment = 'active'))
then 'idle_to_active'
else 'no_change'
end as transition
from table1

I know where the mistake is, it will always produce 'no_change', because there is no row that is both month = 1 and month = 2. But I don't really know how to write the correct code to identify and count the 'transition'. I tried search for Windows Function, but not sure if we can use it here.

My SQL skill is not that advanced yet, could someone please share some help/advice how can I fix the code? Much appreciated, many thanks.

Answer 1

You can use lead() to get the next segment, filter out the rows where there is no change, and then aggregate:

select segment, next_segment, count(*)
from (select t1.*,
             lead(segment) over (partition by userid order by month) as next_segment
      from table1 t1
     ) t1
where next_segment <> segment
group by segment, next_segment;