sql - Perform loop and calculation on BigQuery Array type

Question

My original data, B is an array of INT64:

And I want to calculate the difference between B[n+1] - B[n], hence result in a new table as follow:

I figured out I can somehow achieve this by using LOOP and IF condition:

DECLARE x INT64 DEFAULT 0;
LOOP
 SET x = x + 1
 IF(x < array_length(table.B))
 THEN INSERT INTO newTable (SELECT A, B[OFFSET(x+1)] - B[OFFSET(x)]) from table
 END IF;
END LOOP;

The problem is that the above idea doesn't work on each row of my data, cause I still need to loop through each row in my data table, but I can't find a way to integrate my scripting part into a normal query, where I can

SELECT A, [calculation script] from table

Can someone point me how can I do it? Or any better way to solve this problem?

Thank you.

Answer 1

Below actually works - BigQuery

select * replace(
  array(select diff from (
      select offset, lead(el) over(order by offset) - el as diff
      from unnest(B) el with offset
    ) where not diff is null
    order by offset
  ) as B
)
from `project.dataset.table` t

if to apply to sample data in your question - output is