Need help creating a simple logic in SQL

I have a table CURRENT_TABLE.jpg I need to write a logic to achieve the EXPECTED_TABLE.jpg. I think I need to compare the current value of the ID column with the next value of the ID column to get the sequence numbering just like is shown in column COUNT_SEQ in EXPECTED_TABLE.jpg. I'm not sure how this can be completed in SQL.