how to find consecutive user login across week

I’m fairly new to SQL & maybe the complexity level for this report is above my pay grade

I need help to figure out the list of users who are logging to the app consecutively every week in the time period chosen(this logic eventually needs to be extended to a month, quarter & year ultimately but a week is good for now)

Table structure for ref

events: User_id int, login_date timestamp

The table events can have 1 or more entries for a user. This inherently means that the user can login multiple times to the app. To shed some light, if we focus on Jan 2020- Mar2020 then I need the following in the output

1. user_id who logged into the app every week from 2020wk1 to 2020Wk14 at least once
2. the week they logged in
3. number of times they logged in that week

I’m also okay if the output of the query is just the user_id. The thing is I’m unable to make sense out of the output that I’m seeing on my end after trying the following SQL code, perhaps working on this problem for so long might be the reason for that!

SQL code tried so far:

SELECT DISTINCT user_id 
               ,extract('year' FROM timestamp)||'Wk'|| extract('week' FROM timestamp)
               ,lead(extract('week' FROM timestamp)) over (partition by user_id, extract('week' FROM timestamp) order by extract('week' FROM timestamp))
FROM   events
WHERE   user_id = 'Anything that u wish to enter'

Answer

You can get the summary you want as:

select user_id, date_trunc('week', timestamp) as week, count(*)
from events
group by user_id, week;

But the filtering is tricker. It is better to go with dates rather than week numbers:

select user_id, date_trunc('week', timestamp) as week, count(*) as cnt,
       count(*) over (partition by user_id) as num_weeks
from events
where timestamp >= ? and timestamp < ?
group by user_id, week;

Then you can use a subquery:

select uw.*
from (select user_id, date_trunc('week', timestamp) as week, count(*) as cnt,
             count(*) over (partition by user_id) as num_weeks
      from events
      where timestamp >= ? and timestamp < ?
      group by user_id, week
     ) uw
where num_weeks = ?   -- 14 in your example