with TX_RECEIPT as (select block_timestamp, tx_hash, tx_signer, receipt_object_id, regexp_substr(status_value, 'Success') as reg_success,

receiver_id, logs

from near.core.fact_receipts left join (select tx_signer, tx_hash as tx_hash1

from near.core.fact_transactions)

on tx_hash = tx_hash1

where (receiver_Id = 'marketplace.paras.near'

or receiver_id='test.marketplaces.near' or receiver_id = 'testingitagain.near'

or receiver_id = 'mtvrsmarketplacecontract.near'

or receiver_id = 'marketplace.sora-art.near'

or receiver_id = 'market.themunkymonkey.near'

or receiver_id = 'market.mjol.near'

or receiver_id = 'jpn.near'

or receiver_id = 'higgsfield.near' or receiver_id = 'higgstest1.near'

or receiver_id = 'acovamain.near'

or receiver_id = 'zoneart.near' or receiver_id = 'beta-zoneart.near'

or receiver_id = 'megapont.marketplaces.near'

or receiver_id = 'market.l2e.near'

or receiver_id = 'market.nft.uniqart.near'

or receiver_id = 'marketplace.paras.near'

or receiver_id = 'market.tradeport.near'

or receiver_id = 'market.n-finity.near'

or receiver_id = 'apollo42.near'

or receiver_id = 'asac.marketplaces.near'

or receiver_id = 'market.v-art-digital.near')

and reg_success is not null),

logs_flatten as (select block_timestamp, tx_hash, tx_signer, receiver_id, seq, key, path, index, value, check_json(value) as checks

from TX_RECEIPT, table(flatten(input => logs))),

nft_tx_log as (select block_timestamp, tx_hash, tx_signer, receiver_id, 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:token_series_id as token_series_id,

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

from logs_flatten

where checks is null and type is not null),

NEAR_PRICE as (select date(timestamp) as date_price, avg(price_usd) as near_usd