prices 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)

),

event_summary AS (

SELECT

date_trunc('day', BLOCK_TIMESTAMP) AS day,

SUM(CASE WHEN EVENT_TYPE = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::liquidity_pool::LiquidityAddedEvent<0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC, 0x1::aptos_coin::AptosCoin, 0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::curves::Uncorrelated>' THEN EVENT_DATA['added_x_val'] / 1000000 ELSE 0 END) AS total_added_USDC,

SUM(CASE WHEN EVENT_TYPE = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::liquidity_pool::LiquidityAddedEvent<0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC, 0x1::aptos_coin::AptosCoin, 0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::curves::Uncorrelated>' THEN EVENT_DATA['added_y_val'] / 100000000 ELSE 0 END) AS total_added_APT,

-SUM(CASE WHEN EVENT_TYPE = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::liquidity_pool::LiquidityRemovedEvent<0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC, 0x1::aptos_coin::AptosCoin, 0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::curves::Uncorrelated>' THEN EVENT_DATA['returned_x_val'] / 1000000 ELSE 0 END) AS total_removed_USDC,

-SUM(CASE WHEN EVENT_TYPE = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::liquidity_pool::LiquidityRemovedEvent<0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDC, 0x1::aptos_coin::AptosCoin, 0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::curves::Uncorrelated>' THEN EVENT_DATA['returned_y_val'] / 100000000 ELSE 0 END) AS total_removed_APT

FROM

aptos.core.fact_events

GROUP BY

day

es.day,

SUM(es.total_added_USDC + es.total_removed_USDC) OVER (ORDER BY es.day) AS cumulative_USDC,

SUM(es.total_added_APT + es.total_removed_APT) OVER (ORDER BY es.day) AS cumulative_APT,

CASE

WHEN es.total_added_APT != 0 THEN ps.apt_price * (SUM(es.total_added_APT + es.total_removed_APT) OVER (ORDER BY es.day))

ELSE 0

END AS multiplied_apt_price