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

),

deposits AS (

SELECT

block_timestamp,

tx_hash,

decoded_log:user AS user,

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

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

'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'

),

withdraws AS (

SELECT

block_timestamp,

tx_hash,

decoded_log:user AS user,

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

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

'Withdraw' AS label