When will recursive query stop in this case?

Viewed 23

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.

Table Descriptions

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.

0 Answers
Related