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,

case when asset_balance_changes[0]:amount::FLOAT < 10 then FALSE else TRUE end as valid,

1 as quest_step,

'USDC' as currency,

asset_balance_changes[0]:amount::FLOAT 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

where

-- t.block_timestamp::date >= '2025-01-30'

-- and tx.block_timestamp::date >= '2025-01-30'