select date_trunc(hour,TIMESTAMP) as RECORDED_HOUR, SYMBOL, TOKEN_CONTRACT, avg(PRICE_USD) as PRICE_USD

from near.core.fact_prices

group by 1,2,3

),

near_fees as (

select TX_HASH, TRANSACTION_FEE/1e24 as TX_FEE, TX_FEE*PRICE_USD as FEE_USD, PRICE_USD as PRICE

from near.core.fact_transactions a

join near_prices b

on date_trunc(hour, a.BLOCK_TIMESTAMP)=date_trunc(hour, b.RECORDED_HOUR) and SYMBOL='wNEAR'

where a.BLOCK_TIMESTAMP>=CURRENT_DATE-1000

),

near_transfers as (

select a.*, TX_FEE as FEE, FEE_USD

from (

select BLOCK_TIMESTAMP, TX_HASH, TRADER as user, AMOUNT_IN as usd_amount

from near.core.ez_dex_swaps

where token_in ilike '%usd%' or token_in ilike 'dai'

union

select BLOCK_TIMESTAMP, TX_HASH, TRADER as user, AMOUNT_IN as usd_amount

from near.core.ez_dex_swaps

where token_out ilike '%usd%' or token_out ilike 'dai'

union

select BLOCK_TIMESTAMP, TX_HASH, TRADER as user, AMOUNT_IN*PRICE_USD as usd_amount

from near.core.ez_dex_swaps a

join near_prices b

on date_trunc(hour, a.BLOCK_TIMESTAMP)=date_trunc(hour, b.RECORDED_HOUR) and SYMBOL='wNEAR'

where token_in='wNEAR'

union

select BLOCK_TIMESTAMP, TX_HASH, TRADER as user, AMOUNT_IN*PRICE_USD as usd_amount

from near.core.ez_dex_swaps a

join near_prices b

on date_trunc(hour, a.BLOCK_TIMESTAMP)=date_trunc(hour, b.RECORDED_HOUR) and SYMBOL='wNEAR'

where token_out='wNEAR'