SELECT

TX_HASH,

BLOCK_TIMESTAMP,

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

EVENT_DATA:total_cash_available AS TVL_RAW,

EVENT_DATA:total_borrowed_decimal AS TOTAL_BORROWED_RAW,

ROW_NUMBER() OVER(PARTITION BY TX_HASH ORDER BY EVENT_INDEX DESC) AS ROW_NUMBER

FROM

aptos.core.fact_events

WHERE

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

AND EVENT_MODULE = 'reserve'

),

TOTAL_AMOUNTS_RAW AS (

SELECT

TOKEN_CONTRACT,

TVL_RAW,

TOTAL_BORROWED_RAW

FROM ALL_TX

WHERE ROW_NUMBER = 1

QUALIFY ROW_NUMBER() OVER (PARTITION BY TOKEN_CONTRACT ORDER BY BLOCK_TIMESTAMP DESC) = 1

),

TOTAL_AMOUNTS_WITH_SYMBOLS AS (

SELECT

TOKEN_CONTRACT,

CASE

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

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

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

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

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

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