PostgreSQL window function: row_number() over (partition col order by col2)
Consider partition by to be similar to the fields that you would group by, then, when the partition values change, the windowing function restarts at 1
EDITas indicated by a_horse_with_no_name, for this need we need dense_rank()unlike row_number() rank() or dense_rank() repeat the numbers it assigns. row_number() must be a different value for each row in a partition. The difference between rank() and dense_rank() is the latter does not "skip" numbers.
For your query try:
dense_rank() over (partition by Username, Game order by ct."date") as "Attempts"You don't partition by, and order by, the same field by the way; just order by would be sufficient if that was the need. It isn't here.