WITH raw_data AS (

SELECT livequery.live.udf_api('https://api.coingecko.com/api/v3/coins/aptos/market_chart?vs_currency=usd&days=max') AS resp

)

SELECT

'prices' AS key,

TO_TIMESTAMP(value[0]::string) AS day_price,

value[1] AS THALA_price

FROM

raw_data,

LATERAL FLATTEN (input => resp:data:prices)

),

APTOS_CTE AS (

SELECT

date_trunc('day', date) AS date,

tvl_usd

FROM

external.defillama.fact_chain_tvl

WHERE

chain = 'Aptos'

SELECT *,

TVL_USD/THALA_PRICE as TVL_Asset

FROM THALA_CTE a

LEFT JOIN APTOS_CTE b ON a.day_price = b.date;