SELECT

block_timestamp,

tx_hash,

receiver_id,

logs,

try_parse_json(value) as parse_logs,

parse_logs:type as type,

parse_logs:params:buyer_id as buyer_id,

parse_logs:params:owner_id as owner_id,

parse_logs:params:is_offer as is_offer,

parse_logs:params:is_auction as is_auction,

parse_logs:params:nft_contract_id as nft_contract_id,

parse_logs:params:token_id as token_id,

parse_logs:params:ft_token_id as ft_token_id,

parse_logs:params:price/pow(10,24) as near,

regexp_substr(status_value, 'Success') as reg_success

FROM near.core.fact_receipts,

table(flatten(input=>logs))

WHERE 1=1

-- AND receiver_id = 'marketplace.paras.near' -- 'apollo42.near', 'market.l2e.near', 'mtvrsmarketplacecontract.near', excluded, low volumes

AND type = 'resolve_purchase'

AND reg_success IS NOT NULL

),

near_sales_data AS (

SELECT

*,

nft_contract_id as project

FROM resolve_purchases

LEFT JOIN (SELECT tx_hash as tx_hash1, tx_signer FROM near.core.fact_transactions) ON tx_hash = tx_hash1

),

NEAR_PRICE AS (

SELECT

date(timestamp) as date_price,

avg(price_usd) as "NEAR (USD)"

FROM near.core.fact_prices