SELECT

hour AS price_day,

price,

ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('DAY', hour) ORDER BY hour DESC) AS row_num

FROM ethereum.price.ez_prices_hourly

WHERE token_address = '0xb23d80f5fefcddaa212212f028021b41ded428cf'

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

qualify row_num = 1

ORDER BY 1 ASC

),

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,