apt_CTE AS (

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 apt_price

FROM

raw_data,

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

),

CAKE_CTE AS (

WITH raw_data AS (

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

)

SELECT

'prices' AS key,

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

value[1] AS CAKE_price

FROM

raw_data,

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

),

event_summary AS (

SELECT

date_trunc('day', BLOCK_TIMESTAMP) AS day,

-- Added

SUM(CASE WHEN EVENT_TYPE = '0xc7efb4076dbe143cbcd98cfaaa929ecfc8f299203dfff63b95ccb6bfe19850fa::swap::AddLiquidityEvent<0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT, 0x1::aptos_coin::AptosCoin>' THEN COALESCE(TO_VARCHAR(EVENT_DATA['amount_y']), '0')::FLOAT / 100000000 ELSE 0 END) AS total_added_APT,

SUM(CASE WHEN EVENT_TYPE = '0xc7efb4076dbe143cbcd98cfaaa929ecfc8f299203dfff63b95ccb6bfe19850fa::swap::AddLiquidityEvent<0x159df6b7689437016108a019fd5bef736bac692b6d4a1f10c941f6fbb9a74ca6::oft::CakeOFT, 0x1::aptos_coin::AptosCoin>' THEN COALESCE(TO_VARCHAR(EVENT_DATA['amount_x']), '0')::FLOAT / 100000000 ELSE 0 END) AS total_added_CAKE,