Classify time close events into a group and assign them a number

The table in the Big Query database records multiple events of a user, such as a user sorted by time as follows:

date	user
2024-04-08	bob
2024-04-11	bob
2024-04-12	bob
2024-04-17	bob
2024-04-18	bob
2024-04-25	bob

Now we need to add a calculated column called 'session id': set the first event as a checkpoint, with session id set to 1; If the interval between the new event and the checkpoint is within 7 days, the session ID remains unchanged; If the interval between the new event and the checkpoint is greater than 7 days, the session ID will be incremented and the checkpoint will be reset to that event.

date	user	session_id
2024-04-08	bob	1
2024-04-11	bob	1
2024-04-12	bob	1
2024-04-17	bob	2
2024-04-18	bob	2
2024-04-25	bob	3

SPL code:

	A
1	=BigQryJDBC.query("select * from tb order by date where user=?","Bob")
2	>d=A1.date,s=1
3	=A1.derive(s+=if(date-d>7,(d=date,1)):session_id)

A1: Query the event records of a user through JDBC.

A2: Set variables, where d is the checkpoint date and the initial value is the date of the first event; s is a variable of session-id with an initial value of 1.

A3: Add the calculated column 'session id' according to the rules. When the difference between the current record date and the checkpoint date is greater than 7 days, reset the checkpoint date to the current record date and add s by 1.

Problem source：https://stackoverflow.com/questions/78393653/sql-date-window-reset-after