SELECT

DISTINCT(SUBSTRING(REGEXP_REPLACE(EVENT_TYPE, '[<>]', ''), POSITION ('SyncReserveDetailEvent' IN REGEXP_REPLACE(EVENT_TYPE, '[<>]', ''))+22)) AS TOKEN_CONTRACT

FROM

aptos.core.fact_events

WHERE

EVENT_TYPE ILIKE '0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3::reserve::SyncReserveDetailEvent%'

AND EVENT_MODULE = 'reserve'

),

PRICES AS (

(SELECT

HOUR::DATE AS "Date",

CASE

WHEN TOKEN_ADDRESS = '0xdd89c0e695df0692205912fb69fc290418bed0dbe6e4573d744a6d5e6bab6c13::coin::T' THEN 'SOL'

WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WBTC' THEN 'zWBTC'

WHEN TOKEN_ADDRESS = '0xcfea864b32833f157f042618bd845145256b1bf4c0da34a7013b76e42daa53cc::usdy::USDY' THEN 'USDY'

WHEN TOKEN_ADDRESS = '0x84d7aeef42d38a5ffc3ccef853e1b82e4958659d16a7de736a29c55fbbeb0114::staked_aptos_coin::StakedAptosCoin' THEN 'tAPT'

WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC' THEN 'zUSDC'

WHEN TOKEN_ADDRESS = '0xcc8a89c8dce9693d354449f1f73e60e14e347417854f029db5bc8e7454008abb::coin::T' THEN 'WETH'

WHEN TOKEN_ADDRESS = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::amapt_token::AmnisApt' THEN 'amAPT'

WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT' THEN 'zUSDT'

WHEN TOKEN_ADDRESS = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH' THEN 'zWETH'

WHEN TOKEN_ADDRESS = '0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT' THEN 'CAKE'

WHEN TOKEN_ADDRESS = '0x1::aptos_coin::AptosCoin' THEN 'APT'

WHEN TOKEN_ADDRESS = '0x5e156f1207d0ebfa19a9eeff00d62a282278fb8719f4fab3a586a0a2c0fffbea::coin::T' THEN 'USDC'

WHEN TOKEN_ADDRESS = '0x111ae3e5bc816a5e63c2da97d0aa3886519e0cd5e4b046659fa35796bd11542a::stapt_token::StakedApt' THEN 'stAPT'

END AS "Asset",

AVG(PRICE) AS PRICE_USD

FROM

aptos.price.ez_prices_hourly

WHERE

TOKEN_ADDRESS IN (SELECT TOKEN_CONTRACT FROM ALL_TX WHERE TOKEN_CONTRACT !='0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC')

AND "Date" < CURRENT_DATE AND "Date" > '2024-01-01'