select

*

from near.core.ez_dex_swaps

where token_out = 'wNEAR'

and block_timestamp <= current_date - 1

),

near_token_price_usd as (

select

block_timestamp::date as days,

avg(amount_in / amount_out) as price_usd

from near.core.ez_dex_swaps

where token_in in ('USDT', 'USDC', 'DAI')

and token_out = 'wNEAR'

and amount_in is not null and amount_in > 0

and amount_out is not null and amount_out > 0

group by days

),

near_token_swap_out_with_price as (

select

swap.*,

price.price_usd

from near_token_swap_out_transactions swap

join near_token_price_usd price

on swap.block_timestamp::date = price.days

),

near_token_swap_out_fix_price as (

select

block_timestamp,

tx_hash,

token_in,

concat('Swap NEAR to ', token_in) as pool,

amount_out as amount_near,

amount_out * price_usd as amount_usd

from near_token_swap_out_with_price

),