SQL: Select Count Of Instances Up To Each Current Instance For Each Category In Column Ordered By Date

I have a table including dates and a discrete category column (username).
I'd like to get the number of times each user has showed up in the table, but added as a column to the table.
The result would indicate how many times that user has showed up in the table up to the date in the date column.

For example:

Id      Date      Username
        ------    --------
6age        1/3/23     User1
8s9a        1/3/23     User1
d29j        1/3/23     User2
fj48        1/2/23     User1
39k9        1/2/23     User3
a8j3        1/1/23     User1
0ao2        1/1/23     User1
ajd6        1/1/23     User1
am50        1/1/23     User2
amv8        1/1/23     User3

Desired output:

Id        Date      Username    Instance
        ------    --------    --------
6age        1/3/23     User1       6
8s9a        1/3/23     User1       5
d29j        1/3/23     User2       2
fj48        1/2/23     User1       4
39k9        1/2/23     User3       2
a8j3        1/1/23     User1       3
0ao2        1/1/23     User1       2
ajd6        1/1/23     User1       1
am50        1/1/23     User2       1
amv8        1/1/23     User3       1

I am working with SQLite. The database has 22 million rows, so I am trying to find an optimized approach. Sadly, the ID is not numeric. Though, I know I can overcome that with row_number().

A working solution would feature two steps:

• update the schema of your table with the new field
• update matching unique ids, while using a computed row-number (partitioning on users, ordering on dates and ids)

ALTER TABLE tab ADD COLUMN Instance INT;

UPDATE tab
SET Instance = cte.rn
FROM (SELECT id, 
             ROW_NUMBER() OVER(PARTITION BY Username ORDER BY Date, id DESC) AS rn
      FROM tab) cte
WHERE tab.id = cte.id;

Output:

Check the demo here.