SELECT

hour::date AS day,

symbol,

AVG(price) AS price

FROM aptos.price.ez_prices_hourly

GROUP BY 1,2

),

supply AS (

SELECT

block_timestamp,

tx_hash,

event_data:account_addr AS wallet,

tokens.symbol,

event_data:amount AS raw_amount,

raw_amount/pow(10,decimals) AS amount,

(raw_amount/pow(10,decimals))*price AS amount_usd,

price,

'Suppply' AS event,

event_data

FROM aptos.core.fact_events ev

JOIN aptos.core.fact_transfers trans using(TX_HASH)

JOIN aptos.core.dim_tokens tokens using(TOKEN_ADDRESS)

INNER JOIN prices p ON (block_timestamp::date = day AND p.symbol = tokens.symbol)

WHERE event_type = '0xc6bc659f1649553c1a3fa05d9727433dc03843baac29473c817d06d39e7621ba::lending::SupplyEvent'

GROUP BY all

),

withdraw AS (

SELECT

block_timestamp,

tx_hash,

event_data:account_addr AS wallet,

tokens.symbol,

event_data:amount/pow(10,decimals) AS amount,

(event_data:amount/pow(10,decimals))*price AS amount_usd,