Add column with rolling calculation group by

You can use max with partition by user and mode to calculate rolling sum for mode and then use max and max_by to get corresponding values in outer select:

-- sample data
WITH dataset (date, user_id, mode_name, mode_time) AS (
    values ('2021-10-01', 1, 'game', 10),
        ('2021-10-02', 1, 'game', 10),
        ('2021-10-02', 1, 'tv', 30),
        ('2021-10-09', 1, 'music', 10),
        ('2021-10-15', 1, 'music', 40),
        ('2021-10-01', 2, 'game', 10),
        ('2021-10-01', 2, 'music', 10),
        ('2021-10-04', 2, 'game', 10),
        ('2021-10-04', 2, 'music', 20),
        ('2021-10-05', 2, 'tv', 40),
        ('2021-10-11', 2, 'tv', 40),
        ('2021-10-12', 2, 'game', 20)
) 

--query
SELECT date, user_id, mode_name, mode_time,
    max_by(mode_name, mode_time_rolling_time) OVER (
        PARTITION BY user_id
        ORDER BY date
    ) AS favourite_mode,
    max(mode_time_rolling_time) OVER (
        PARTITION BY user_id
        ORDER BY date
    ) AS favourite_mode_time
FROM(
        SELECT *,
            sum(mode_time) OVER (
                PARTITION BY user_id,
                mode_name
                ORDER BY date
            ) AS mode_time_rolling_time
        FROM dataset
    )
ORDER BY user_id, date

Output: