I have a bq table that recieves data quarter to date per id

So each id got the cumulative data of the quarter I need to create a view that takes each day's value minus the day's before per id

for id 1 (02/11 value minus 01/11 value, ...etc)

so the output should be like this

any help is really appreciated

CodePudding user response：

you might consider below query.

SELECT ID, date, 
       value - LEAD(value, 1, 0) OVER (
         PARTITION BY id ORDER BY UNIX_DATE(PARSE_DATE('%d/%m/%Y', date)) DESC
       ) AS new_value
  FROM sample_data;

Query results