siwakonchabhoney-berp bgt reward
Updated 2024-08-09
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
›
⌄
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 = '0xc5cb3459723b828b3974f7e58899249c2be3b33d'
AND left(input, 10) = '0xc00007b0'
AND tx_succeeded = true
) subquery
GROUP BY
date_trunc('day', block_timestamp)
)
SELECT
date,
total_bgt_value,
SUM(total_bgt_value) OVER (
ORDER BY
date
) as cumulative_bgt_value
FROM
daily_totals
ORDER BY
date DESC;
QueryRunArchived: QueryRun has been archived