I am looking at a series of session dates of a user, and I am trying to “collapse” the event counts in a way that any event occurring in a 7 day time window is only counted once, starting from the first event and then again after the initial period of 7 days is over, and a new event (and a 7 day window) starts again.

I am trying to figure out a logic, where the count “breaks” and resets the window. Thank you!

WITH data1 AS (
    SELECT 'bob' AS user, DATE('2024-04-08' AS session_date
    UNION ALL
    SELECT 'bob', DATE('2024-04-11')
    UNION ALL
    SELECT 'bob', DATE('2024-04-12')
    UNION ALL
    SELECT 'bob', DATE('2024-04-17')
    UNION ALL
    SELECT 'bob', DATE('2024-04-18')
    UNION ALL
    SELECT 'bob', DATE('2024-04-22')
    UNION ALL
    SELECT 'bob', DATE('2024-04-23')
    UNION ALL
    SELECT 'bob', DATE('2024-04-25')
    
)

SELECT
    *,
    DATE_ADD(session_date, INTERVAL 7 day) AS session_date_end,
    COUNT(session_date) OVER (PARTITION BY user
                ORDER BY UNIX_DATE(session_date)
                RANGE BETWEEN CURRENT ROW AND 6 FOLLOWING) 
            AS next_7_days
FROM data1