CTE_1 AS (

WITH raw_data AS (

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

)

SELECT

'prices' AS key,

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

value[1] AS price

FROM

raw_data,

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

),

event_summary AS (

SELECT

DATE_TRUNC('DAY', block_timestamp) AS day_swap,

platform,

SUM(

CASE

WHEN currency_in = 'usei' THEN amount_in

WHEN currency_out = 'usei' THEN amount_out

END

) AS amount

FROM sei.defi.fact_dex_swaps

GROUP BY 1,2

),

usdceth_summary AS (

SELECT

DATE_TRUNC('DAY', block_timestamp) AS day_swap,

platform,

SUM(

CASE

WHEN (currency_out <> 'usei'OR currency_out <> 'ibc/CA6FBFAF399474A06263E10D0CE5AEBBE15189D6D4B2DD9ADE61007E68EB9DB0') AND currency_in = 'factory/sei189adguawugk3e55zn63z8r9ll29xrjwca636ra7v7gxuzn98sxyqwzt47l/Hq4tuDzhRBnxw3tFA5n6M52NVMVcC19XggbyDiJKCD6H' THEN amount_in