select

fe.block_timestamp,

fe.tx_hash as tx_id,

1 as action_count,

LOWER(trader) as address,

1 as quest_step,

'NEAR' as currency,

coalesce(fe.amount_out_usd, fe.amount_in_usd) as usd_amount,

usd_amount / ap.price as token_amount,

TRUE as valid,

coalesce(tx.gas_used * ap.price / pow(10, 16), 0) as fee_amount

FROM

near.defi.ez_dex_swaps fe

left join near.core.fact_transactions tx on fe.tx_hash = tx.tx_hash

LEFT JOIN (

select

*

from

NEAR.PRICE.EZ_PRICES_HOURLY

where

token_Address = 'wrap.near'

and is_native = TRUE

) ap ON date_trunc('hour', ap.hour) = date_trunc('hour', fe.block_timestamp)

WHERE

fe.platform = 'v2.ref-finance.near'

and tx.tx_succeeded = TRUE

and (fe.TOKEN_IN_CONTRACT in (

'ftv2.nekotoken.near',

'token.0xshitzu.near',

'dd.tg',

'blackdragon.tkn.near',

'token.lonkingnearbackto2024.near',

'purge-558.meme-cooking.near',

'gnear-229.meme-cooking.near',

'abg-966.meme-cooking.near'