SELECT

'2024-03-01'::date as time

UNION ALL

SELECT

DATEADD(day, 1, time)

FROM

date_series

WHERE

time < date_trunc('day', current_timestamp())

),

token_price as (

select date_trunc('day', hour) as time, symbol,

iff(symbol = 'USDB',

lower('0xCD732d21c1B23A3f84Bb386E9759b5b6A1BcBe39'),

lower('0xb9d94A3490bA2482E2D4F21F0E76b92E5661Ded8')

) as address,

avg(price) as price,

from blast.price.ez_prices_hourly

where symbol in ('USDB', 'WETH') and hour > '2024-03-01'::date

group by 1, 2, 3

order by 1, 2, 3

),

hlp_hist_balance as (

select distinct

last_value(date_trunc('day', time)) over (PARTITION by DATE(date_trunc('day', time)), address order by time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS time,

last_value(address) over (PARTITION by DATE(date_trunc('day', time)), address order by time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS address,

last_value(amount) over (partition by DATE(date_trunc('day', time)), address order by time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount

from

(

select block_timestamp as time,

concat('0x', substring(input, 35, 74)) as address,

ethereum.public.udf_hex_to_int(output) as amount

from blast.core.fact_traces

where to_address = lower('0x97e94BdA44a2Df784Ab6535aaE2D62EFC6D2e303')

and input like '%0x9024e818%'