date(TO_TIMESTAMP(value[0]::string)) as price_date,

'SEI' as symbol

, value[1] as daily_price

from (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/sei-network/market_chart?vs_currency=usd&days=max&interval=daily&precision=6') as resp),LATERAL FLATTEN (input => resp:data:prices))

UNION ALL

(SELECT

DATE(HOUR) AS PRICE_DATE,

SYMBOL,

AVG(PRICE) AS daily_price

FROM crosschain.price.ez_hourly_token_prices

WHERE SYMBOL IN ('WETH','OSMO','ATOM','WBTC')

AND HOUR >= '2023-08-15'

GROUP BY 1,2

ORDER BY 1,2)),

base as (select

DATE(BLOCK_TIMESTAMP) as DATE,

CASE

WHEN PLATFORM = 'fuzio' THEN 'Fuzio'

WHEN PLATFORM = 'astroport' THEN 'Astroport'

WHEN PLATFORM = 'seaswap' THEN 'SeaSwap'

END AS PLATFORMS,

TX_ID,

SWAPPER,

case

when CURRENCY_IN = 'usei' then 'SEI'

when CURRENCY_IN = 'sei1606hlfm6sxee9ha7myshcyelhqydjfaca6w37wljphkjpt53fzrqlw9y68' then 'SAY'

when CURRENCY_IN = 'sei123gd6c6je3nmtjuvesuwhxphtuxgtuu6gu5ck3h5zcn6qqzzmgaqe85df2' then 'SAILOR'

when CURRENCY_IN = 'sei16aa3whueaddmms3qw0apz7ylddg0vwtw2zugafmccdtrxrwyx0kqwxntat' then 'TURTLE'

when CURRENCY_IN = 'sei1ys77cs0qsqzx4dvtds0e077qc0sg4k7tg32lcmqemek8g0tpr57s5ckxd7' then 'MUSK'

when CURRENCY_IN = 'sei1y6t2swnwjewa07hxeuv3pvxd9x9vc8chtwtfz8awpyex0tuurp9qkdzq66' then 'sey'

when CURRENCY_IN = 'sei1smdzpfsy48kmkzmm4m9hsg4850czdvfncxyxp6d4h3j7qv3m4v0slzx8yy' then 'OIN'