select

TO_TIMESTAMP(value[0]::string) as avg_date

,value[1] as avg_price

from (

select livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/sei-network/market_chart?vs_currency=usd&days=190&interval=daily&precision=3') as resp

)

,lateral flatten (input => resp:data:prices)

,lst_tx as (

select

DISTINCT tx_id

from sei.core.fact_msg_attributes

where block_timestamp::date>='2023-12-11'

and attribute_value='50000000usei'

and sei.core.fact_msg_attributes.tx_succeeded=true

,lst_cond1 as (

select

DISTINCT tx_id

from sei.core.fact_msg_attributes

where ATTRIBUTE_KEY= '_contract_address'

and ATTRIBUTE_VALUE= 'sei1pymn0l6hu50qw5uwlf0llns7z4u98jr6f4zvh5qa3jrp85dly24qk3usm7'

,lst_cond2 as (

select

DISTINCT tx_id

from sei.core.fact_msg_attributes

where ATTRIBUTE_KEY = 'validator'

and ATTRIBUTE_VALUE = 'seivaloper146m089lq8mkqw6w0mmlhxz6247g2taha89at74'

)

,lst_cond3 as (

select

DISTINCT tx_id