Given this table description. I have written a query to find Users who logged in for 5 or more consecutive days.
WITH RECURSIVE
rec_t AS
(SELECT id, login_date, 1 AS days FROM Logins
UNION ALL
SELECT l.id, l.login_date, rec_t.days+1 FROM rec_t
INNER JOIN Logins l
ON rec_t.id = l.id AND DATE_ADD(rec_t.login_date, INTERVAL 1 DAY) = l.login_date
)
SELECT * FROM Accounts
WHERE id IN
(SELECT DISTINCT id FROM rec_t WHERE days = 5)
ORDER BY id
Code Explanation :
For every id and login date, match the CTE table with the same id and +1 login_date. the "days" column just increments +1 everytime the same user_id appears.
The Problem:
Although the query works fine, I just don't know where am I asking the query to stop the recursion. There isn't a "where" in RECURSIVE CTE definition. However, the inner join might help to dictate that there are no more login_date to match on. But I am uncertain that is the case.
