Clean Data Using Sql - Take Column Difference

I have data in SQL as follows: Actual Table +-------------+--------+------+ | Id | Weight | Type | +-------------+--------+------+ | 00011223344 | 35 | A | | 000112

Solution 1:

Hmmmm . . . If I'm following this:

select t.*,
       (casewhen id like'000%'then weight
             else weight -sum(casewhen id like'000%'then weight else0end) over (partitionby actual_id)
        end) as actual_weight
from (select t.*,
             max(id) over (partitionby stuff(id, 1, 3, '')) as actual_id
      from t
     ) t;

Here is a db<>fiddle.