I'm looking to calculate NURR, CURR and RURR each day for some user data from an app. I've started with NURR (New User Retention Rate), which is defined as:

The percentage of users who first opened the app between t-13 and t-7, that open the app between t-6 and t-0

For each user I have a unique ID, the date of each session in the app, and the date of their first session.

| user_id | session_date | first_session |
|---------|--------------|---------------|
|    1    |  2025-02-17  |  2025-02-17   |
|    1    |  2025-02-18  |  2025-02-18   |
|    2    |  2025-02-17  |  2025-01-31   |
|    2    |  2025-02-18  |  2025-02-18   |

I know how to calculate NURR for a specific day, e.g., today.

SELECT SAFE_DIVIDE(COUNT(DISTINCT this_week.user_id), COUNT(DISTINCT last_week.user_id))
FROM
(SELECT DISTINCT user_id
FROM sessions
WHERE session_date <= current_date() - 7
AND session_date >= current_date() - 13
AND first_session <= current_date() - 7
AND first_session >= current_date() - 13) last_week
LEFT JOIN
(SELECT DISTINCT user_id
FROM sessions
WHERE session_date <= current_date()
AND session_date >= current_date() - 6) this_week
ON last_week.user_id = this_week.user_id

However, I don't know how to build on this to calculate NURR for different days, e.g.,

|    date    | NURR |
|------------|------|
| 2025-02-18 | 0.35 |
| 2025-02-17 | 0.34 |
| 2025-02-16 | 0.33 |
| 2025-02-15 | 0.34 |
| 2025-02-14 | 0.35 |

From the research I've done, it sounds like the answer might involve OVER and partitions, but all of the examples I can find use very simple aggregate functions, so I can't figure out how to apply it to my case.