with near_out as

with near_price as

(

select recorded_hour::date as "date",avg(close) as price_usd from crosschain.core.fact_hourly_prices

where id='near'

and provider='coingecko'

group by "date"

),

near_txns as

(

select fr.block_timestamp,

fr.tx_hash,

parse_json(fr.actions):"receipt":"Action":"actions"[0]:"Transfer":"deposit" as amount,

parse_json(fr.actions):"receipt":"Action":"signer_id" as sender,

parse_json(fr.actions):"receiver_id" as receiver,

amount*np.price_usd as amount_usd

from near.core.fact_receipts fr

inner join near_price np

on fr.block_timestamp::date=np."date"

where 1=1

-- and tx_hash='BSudcmqufUaHRZcvPqdgexGHYBM7xrfDC9JF7uHy6wAy'

and( sender = '79501b068d4fcbbcea3e7c332891a7d8abfd9864657b9c2cc3c18c944b1db558'

or receiver = '79501b068d4fcbbcea3e7c332891a7d8abfd9864657b9c2cc3c18c944b1db558')

-- and receiver != '79501b068d4fcbbcea3e7c332891a7d8abfd9864657b9c2cc3c18c944b1db558'

and amount is not null

select * from near_txns

SELECT

*,