Select trunc(TO_TIMESTAMP(value[0]::string),'hour') as hour,

'sei1hrndqntlvtmx2kepr0zsfgr7nzjptcc72cr4ppk4yav58vvy7v3s4er8ed' as token_address,

avg(value[1]) as token_price

from (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/seiyan/market_chart?vs_currency=usd&days=90') as api

,LATERAL FLATTEN (input => api:data:prices)

GROUP by 1,2),

tbl as (

select

BLOCK_TIMESTAMP,

SWAPPER,

TX_ID,

CURRENCY_in,

CURRENCY_out,

PLATFORM,

CASE WHEN (CURRENCY_in = 'sei1hrndqntlvtmx2kepr0zsfgr7nzjptcc72cr4ppk4yav58vvy7v3s4er8ed') then 'SEIYAN'

WHEN (CURRENCY_in = 'factory/sei1e3gttzq5e5k49f9f5gzvrl0rltlav65xu6p9xc0aj7e84lantdjqp7cncc/isei') then 'ISEI'

WHEN (CURRENCY_in = 'sei1ln7ntsqmxl8s502f83km9a475zyhcfhpj7v2fsm3pcmckdyys3tsktx9vk') then 'stSEI'

WHEN (CURRENCY_in = 'factory/sei1lg259fed9fn0340k000tyg3fama5awdmeczfmx/SeiyanInu') then 'SeiyaneInu'

WHEN (CURRENCY_in = 'sei1ajwmaynzjuekhs2tyv7qvvsu8dnw5d5ug0suzvt3353rz0t5ltwsflty4r') then 'SEINU'

WHEN (CURRENCY_in = 'ibc/0EC78B75D318EA0AAB6160A12AEE8F3C7FEA3CFEAD001A3B103E11914709F4CE') then 'ASTRO'

WHEN (CURRENCY_in = 'factory/sei189adguawugk3e55zn63z8r9ll29xrjwca636ra7v7gxuzn98sxyqwzt47l/9fELvUhFo6yWL34ZaLgPbCPzdk9MD1tAzMycgH45qShH') then 'USDCsol'

WHEN (CURRENCY_in = 'sei1vw6rxcg0kczy8728phdgxpuw78za2h9lgm4h7q0ughw364yz7luq26tchc') then 'AAAAAA'

WHEN (CURRENCY_in = 'sei10knxlv9e54z0xcywdhtycc63uf970aefeec73ycqwd75ep9nu45sh66m6a') then 'SEILOR'

WHEN (CURRENCY_in = 'sei1kttw09hrkccutqrns9058pmt8gjzr5gglum0tha760mz0rd6mktqwkg63d') then 'ZENI'

WHEN (CURRENCY_in = 'sei170949pwl4h36neazkwmdgtrh6nuenh66462n34839e23e8he3pqsnm5r2v') then 'MILI'

WHEN (CURRENCY_in = 'usei') then 'SEI'

ELSE 'other' END AS symbol_in,

CASE WHEN (CURRENCY_out = 'sei1hrndqntlvtmx2kepr0zsfgr7nzjptcc72cr4ppk4yav58vvy7v3s4er8ed') then 'SEIYAN'

WHEN (CURRENCY_out = 'factory/sei1e3gttzq5e5k49f9f5gzvrl0rltlav65xu6p9xc0aj7e84lantdjqp7cncc/isei') then 'ISEI'

WHEN (CURRENCY_out = 'sei1ln7ntsqmxl8s502f83km9a475zyhcfhpj7v2fsm3pcmckdyys3tsktx9vk') then 'stSEI'