select date_trunc(day,TIMESTAMP) as date, avg(PRICE_USD) as token_price

from near.price.fact_prices

where symbol='wNEAR' and TIMESTAMP::date>='{{starting_date}}'

group by 1

),

near_txs as (

select distinct tx_hash

from near.core.fact_transactions

where BLOCK_TIMESTAMP::date>='{{starting_date}}' and TX_STATUS='Success'

),

near_nft_sales_batch as (

select BLOCK_TIMESTAMP,

case

when RECEIVER_ID='marketplace.paras.near' then 'Paras'

when RECEIVER_ID='market.l2e.near' then 'L2E'

when RECEIVER_ID='market.nft.uniqart.near' then 'UniqArt'

when RECEIVER_ID='market.tradeport.near' then 'TradePort'

when RECEIVER_ID='market.fewandfar.near' then 'FewAndFar'

when RECEIVER_ID='apollo42.near' then 'Apollo42'

end as marketplace,

args:buyer_id as buyer,

COALESCE(args:market_data:owner_id,args:sale:owner_id) as seller,

case

when split_part(COALESCE(args:market_data:token_id,args:sale:token_id),':',2)=''

then COALESCE(args:market_data:token_id,args:sale:token_id)

else split_part(COALESCE(args:market_data:token_id,args:sale:token_id),':',2)

end as nft,

case

when split_part(COALESCE(args:market_data:token_id,args:sale:token_id),':',2)='' then COALESCE(args:market_data:nft_contract_id,args:sale:nft_contract_id)

else COALESCE(args:market_data:nft_contract_id,args:sale:nft_contract_id)||':'||split_part(COALESCE(args:market_data:token_id,args:sale:token_id),':',1)

end as collection,

args:price/1e24 as price,

token_price*args:price/1e24 as usd_price, a.tx_hash