Select

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

'XLM' as token,

avg(value [1]) as avg_price

from

(

SELECT

livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/stellar/market_chart?vs_currency=usd&days=365'

) as resp

),

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

group by

all

),

bridge as (

SELECT distinct

t.transaction_hash as tx_id,

t.block_timestamp as block_timestamp,

asset_balance_changes[0]:to::string as address,

1 as action_count,

TRUE as valid,

1 as quest_step,

'XLM' as currency,

asset_balance_changes[0]:amount::FLOAT AS amount_usd,

amount_usd/sp.avg_price as token_amount,

tx.fee_charged/pow(10,7) as fee_amount

from stellar.core.ez_operations t

LEFT JOIN stellar.core.fact_transactions tx

on t.transaction_hash = tx.transaction_hash

left join stellar_prices sp

on sp.hour::date = t.block_timestamp::date

where