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'

),

FILTER_TOTAL_BORROWED_DAILY AS (

SELECT

BLOCK_TIMESTAMP::DATE AS "Date",

TOKEN_CONTRACT,

LAST_VALUE(TOTAL_BORROWED_RAW) OVER (PARTITION BY TO_DATE(BLOCK_TIMESTAMP), TOKEN_CONTRACT ORDER BY TO_DATE(BLOCK_TIMESTAMP)) AS TOTAL_BORROWED_AMOUNT_RAW,

LAST_VALUE(TVL_RAW) OVER (PARTITION BY TO_DATE(BLOCK_TIMESTAMP), TOKEN_CONTRACT ORDER BY TO_DATE(BLOCK_TIMESTAMP)) AS TVL_AMOUNT_RAW

FROM ALL_TX

WHERE ROW_NUMBER = 1

),

TOTAL_BORROWED_DAILY AS (

SELECT

"Date",

TOKEN_CONTRACT,

TOTAL_BORROWED_AMOUNT_RAW,

TVL_AMOUNT_RAW

FROM FILTER_TOTAL_BORROWED_DAILY

GROUP BY

"Date",

TOKEN_CONTRACT,