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

SELECT

(CASE

WHEN symbol IN ('USDC','MOD','USDT') THEN 'Stablecoins'

ELSE symbol

END

) symbol,

count(tx_hash) AS transactions,

count(DISTINCT wallet) AS wallets,