I have the following SQL table

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 :

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

Appreciate your help;

CodePudding user response：

One way would be with a recursive CTE, like

WITH RECURSIVE cte (username, month, cnt) AS
(
 SELECT username, month, 1
 FROM test
 UNION ALL
 SELECT test.username, test.month, cte.cnt 1
 FROM cte INNER JOIN test
  ON cte.username = test.username AND CASE WHEN cte.month = 12 THEN 1 ELSE cte.month   1 END = test.month
)
SELECT username, MAX(cnt)
FROM cte
GROUP BY username
ORDER BY username

The idea is that the CTE (named cte in my example) recursively joins back to the table on a condition where the user is the same and the month is the next one. So for user 345, you have:

The rows with cnt=1 are from the original table (with the extra cnt column hardcoded to 1), the row with cnt=2 is from the recursive part of the query (which found a match and used cnt 1 for its cnt). The query then selects the maximum for each user.

The join uses a CASE statement to handle 12 being followed by 1.

You can see it working with your sample data in this fiddle.

CodePudding user response：

The one shared by @EdmCoff is quite elegant.

Another one without recursive and just using conditional logic -

with data_cte as
(
select username, month_1,
case when (count(month_1) over (partition by username) = 1) then 1
when (lead(month_1) over (partition by username order by username) - month_1) = 1  OR (month_1 - lag(month_1) over (partition by username order by username)) = 1 then 1
when (month_1 = 12 and min (month_1) over (partition by username) =1) then 1
end cnt
from login_tab 
  )
  select username, count(cnt) from data_cte group by username

DB Fiddle here.