I have two tables. One table contains information related to student portal events and another table for weekly student activity. From those two tables, I would like to be able to count the number of students that interacted with the dashboard once and again in the next week. By again in the next week, I mean if the dashboard was first visited Jan 1st the next week would be between Jan 8th-14th. How can this be achieved in SQL?

student_portal_events
 

student_id	event_date	type	event_id
7896YD60	01/23/2022	login	view
789G44VM	01/22/2022	login	view
785KT3Q2	01/20/2022	dsh-engage	dsh-invoke
78SVD53Z	01/20/2022	login	view
78C9P4LP	01/20/2022	dsh-engage	dsh-invoke
78NVX7MN	01/20/2022	login	view
78U1XHS5	01/22/2022	dsh-engage	dsh-invoke
78JWSL7D	01/22/2022	dsh-engage	dsh-invoke
78EDG616	01/22/2022	dsh-engage	dsh-invoke

 

weekly_student_activity

 

student_id	week_ending_date	activity_type	weekly_activity_count
7896YD60	01/29/2022	login	0
789G44VM	01/29/2022	login	0
785KT3Q2	01/29/2022	login	0
78SVD53Z	01/29/2022	login	0
78C9P4LP	01/29/2022	login	0
78NVX7MN	01/29/2022	feedback-submission	5
78U1XHS5	01/29/2022	assignment-edit	7
78JWSL7D	02/05/2022	assignment-edit	6
78EDG616	02/05/2022	feedback-submission	2