WITH

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,

-- Added

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

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

-- Removed

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

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

-- Swaps

SUM(CASE WHEN EVENT_TYPE = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::liquidity_pool::SwapEvent<0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH, 0x1::aptos_coin::AptosCoin, 0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::curves::Uncorrelated>' THEN COALESCE(TO_VARCHAR(EVENT_DATA['x_in']), '0')::FLOAT / 1000000 - COALESCE(TO_VARCHAR(EVENT_DATA['x_out']), '0')::FLOAT / 1000000 ELSE 0 END) as total_swapped_wETH,

SUM(CASE WHEN EVENT_TYPE = '0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::liquidity_pool::SwapEvent<0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::WETH, 0x1::aptos_coin::AptosCoin, 0x190d44266241744264b964a37b8f09863167a12d3e70cda39376cfb4e3561e12::curves::Uncorrelated>' THEN COALESCE(TO_VARCHAR(EVENT_DATA['y_in']), '0')::FLOAT / 100000000 - COALESCE(TO_VARCHAR(EVENT_DATA['y_out']), '0')::FLOAT / 100000000 ELSE 0 END) as total_swapped_APT,