SQL Query Sequential Month Logins

I have the following SQL table

username	Month
292	10
123	12
123	1
123	2
123	4
345	6
345	7

I want to query it, to get each username’s login streak in Count of sequential Month. meaning the end result I am looking for looks like this :

username	Streak
292	1
123	3
345	2

How can I achieve it ? taking into note the Month 12 –> Month 1 issue;

Appreciate your help;

Answer

This would give you the result you want:

select username, count(*)
from (
    select 
    username
    , month_1
    , coalesce(nullif(lead(month_1) 
                      over (partition by username 
                            order by coalesce(nullif(month_1,12),0))
                      - coalesce(nullif(month_1,12),0),-1),1) as MonthsTillNext
from login_tab 
  ) Step1
where MonthsTillNext=1
group by username

By calculating the difference from the next row, where the next row is defined as the next month_no in ascending order, treating 12 as 0 (refer to the ambiguity I mentioned in my comment). It then just leaves the rows for consecutive months rows, and counts them.

Beware though, in addition to the anomaly around month:12, there is another case not considered: if the months for the user are 1,2,3 and 6,7,8 this would count as Streak:6; is it what you wanted?