WITH daily_totals AS (

SELECT

date_trunc('day', block_timestamp) as date,

sum(bgt_value) as total_bgt_value

FROM

(

SELECT

block_timestamp,

utils.udf_hex_to_int('s2c', regexp_substr_all(SUBSTR(output, 3, len(output)), '.{64}')[0] :: string) :: bigint / pow(10, 18) as bgt_value

FROM

berachain.testnet.fact_traces

WHERE

from_address = '{{contract_address}}'

AND left(input, 10) = '0xc00007b0'

AND tx_succeeded = true

AND block_timestamp > '2024-06-12'

) subquery

GROUP BY

date_trunc('day', block_timestamp)

date,

total_bgt_value,

SUM(total_bgt_value) OVER (ORDER BY date) as cumulative_bgt_value

daily_totals

ORDER BY

date DESC;