WITH prices AS(

SELECT

hour::date AS price_day,

AVG(price) AS price

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0xb23d80f5fefcddaa212212f028021b41ded428cf'

AND hour::date >= '2024-06-01'

GROUP BY 1

),

current_prices AS(

SELECT

hour AS price_day,

price,

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0xb23d80f5fefcddaa212212f028021b41ded428cf'

AND hour = DATEADD(hour,-2,to_char(current_timestamp,'YYYY-MM-DD HH24:00:00'))

),

eth_deposits AS (

SELECT

block_timestamp,

tx_hash,

decoded_log:user :: string AS user,

decoded_log:amount / pow(10,18) AS amount,

price * (decoded_log:amount / pow(10,18)) AS amount_usd,

'Ethereum Deposit' AS label

FROM ethereum.core.ez_decoded_event_logs

INNER JOIN prices ON (block_timestamp::date = price_day)

WHERE contract_address = '0x4a3826bd2e8a31956ad0397a49efde5e0d825238'

AND event_name = 'DepositCreated'

AND block_timestamp::date >= '2024-06-01'