My table:

"letter"    "time"
"a" "2024-08-14 16:57:56.563112"
"b" "2024-08-14 17:57:56.563112"
"b" "2024-08-14 18:57:56.563112"
"b" "2024-08-14 19:57:56.563112"
"a" "2024-08-14 20:57:56.563112"
"a" "2024-08-14 21:57:56.563112"
"b" "2024-08-14 22:57:56.563112"
"b" "2024-08-14 23:57:56.563112"

I want to assign unique number to each section based on letter like this:

"letter"    "time"  "section_number"
"a" "2024-08-14 16:57:56.563112"    0
"b" "2024-08-14 17:57:56.563112"    1
"b" "2024-08-14 18:57:56.563112"    1
"b" "2024-08-14 19:57:56.563112"    1
"a" "2024-08-14 20:57:56.563112"    2
"a" "2024-08-14 21:57:56.563112"    2
"b" "2024-08-14 22:57:56.563112"    3
"b" "2024-08-14 23:57:56.563112"    3

the closest I get (fond here sql group by only rows which are in sequence)

SELECT 
    letter
    ,time
    ,ROW_NUMBER() OVER(ORDER BY  time)
    - ROW_NUMBER() OVER(Partition by letter ORDER BY  time) as section_number
    FROM test;

but it has duplicates (number 3)

"letter"    "time"  "section_number"
"a" "2024-08-14 16:57:56.563112"    0
"b" "2024-08-14 17:57:56.563112"    1
"b" "2024-08-14 18:57:56.563112"    1
"b" "2024-08-14 19:57:56.563112"    1
"a" "2024-08-14 20:57:56.563112"    3
"a" "2024-08-14 21:57:56.563112"    3
"b" "2024-08-14 22:57:56.563112"    3
"b" "2024-08-14 23:57:56.563112"    3