SELECT

op.*,

f.value AS balance_change

FROM stellar.core.ez_operations op,

LATERAL FLATTEN(input => PARSE_JSON(asset_balance_changes)) f

WHERE contract_id = 'CBQDHNBFBZYE4MKPWBSJOPIYLW4SFSXAXUTSXJN76GNKYVYPCKWC6QUK'

-- AND block_timestamp::DATE >= current_date - 2

and (f.value:from::string in (:userAddresses) or f.value:to::string in (:userAddresses))

and block_timestamp :: date >= :startsAt

),

usdc_tx AS (

SELECT

balance_change:from::STRING AS address,

transaction_hash AS tx_id

FROM base

WHERE balance_change:asset_code::STRING = 'USDC'

and balance_change:from::string in (:userAddresses)

),

base2 as (

SELECT DISTINCT

t.transaction_hash AS tx_id,

t.block_timestamp AS block_timestamp,

t.balance_change:to::STRING AS address,

1 AS action_count,

1 AS quest_step,

'USDC' AS currency,

-- Use a scalar subquery instead of a JOIN

COALESCE(

(SELECT AVG(value[1]::FLOAT)

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)

WHERE TO_DATE(value[0]::STRING) = DATE(t.block_timestamp)

), 0

) as price,

t.balance_change:amount::FLOAT AS amount,