----- Mercato WITH mercato_buy_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_address = '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26' AND event_resource = 'BuyEvent' ORDER BY event_index ), mercato_bid_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_address = '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26' AND event_resource = 'AcceptTokenBidEvent' UNION SELECT * FROM aptos.core.fact_events WHERE event_address = '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26' AND event_resource = 'AcceptCollectionBidEvent' ), mercato_bids_sales_events_raw AS ( SELECT *, 'sale' AS event_kind FROM mercato_buy_events_raw UNION SELECT *, 'bid_won' AS event_kind FROM mercato_bid_events_raw ), mercato_bids_sales_events1 AS ( SELECT *, ( CASE WHEN event_resource IN ( 'AcceptCollectionBidEvent', 'AcceptTokenBidEvent' ) THEN 'bid_won' WHEN event_resource = 'BuyEvent' THEN 'sale' END ) AS event_kind FROM ( SELECT *, LAG(event_index, 1, 0) OVER ( PARTITION BY tx_hash ORDER BY event_index ) AS prev_event_index FROM ( SELECT *, LEN(event_type) AS event_type_len FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM mercato_bids_sales_events_raw ) ) WHERE event_type_len > 70 ) WHERE event_address = '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26' AND event_resource IN ( 'BuyEvent', 'AcceptCollectionBidEvent', 'AcceptTokenBidEvent' ) ), aptos_changes AS ( SELECT * FROM aptos.core.fact_changes WHERE tx_hash IN ( SELECT tx_hash FROM mercato_bids_sales_events1 WHERE event_data:token_id.token_data_id.collection IS NULL ) AND inner_change_type = '0x4::token::Token' ), collection_names AS ( SELECT tx_hash, to_varchar(f.value) AS collection_name, f.index FROM aptos.core.fact_transactions tx, lateral flatten(input => payload:arguments [5]) f WHERE tx.tx_hash IN ( SELECT tx_hash FROM mercato_bids_sales_events1 WHERE event_data:token_id.token_data_id.collection IS NULL ) ), object_addresses AS ( SELECT tx_hash, to_varchar(f.value) AS object_address, f.index FROM aptos.core.fact_transactions tx, lateral flatten(input => payload:arguments [6]) f WHERE tx.tx_hash IN ( SELECT tx_hash FROM mercato_bids_sales_events1 WHERE event_data:token_id.token_data_id.collection IS NULL ) ), object_collection_info AS ( SELECT obj.tx_hash, coll.collection_name, obj.object_address FROM object_addresses obj LEFT JOIN collection_names coll ON obj.tx_hash = coll.tx_hash AND obj.index = coll.index ), collection_nft_info AS ( SELECT coll.*, change.change_data:name AS nft_name FROM object_collection_info coll LEFT JOIN aptos_changes change ON coll.tx_hash = change.tx_hash AND coll.object_address = change.address ), missed_object_names AS ( SELECT tx_hash, change_data:name AS nft_name FROM aptos.core.fact_changes WHERE tx_hash NOT IN ( SELECT tx_hash FROM collection_nft_info ) AND tx_hash IN ( SELECT tx_hash FROM mercato_bids_sales_events1 WHERE event_data:token_id.token_data_id.collection IS NULL ) AND inner_change_type = '0x4::token::Token' ), mercato_bids_sales_events2 AS ( SELECT main.*, ( CASE WHEN ARRAY_SIZE(SPLIT(change.nft_name, ' #')) > 1 THEN SPLIT(change.nft_name, ' #') [0] :: TEXT WHEN SPLIT(change.nft_name, '.') [2] = 'apt' THEN 'Aptos Subdomain Names V2' WHEN SPLIT(change.nft_name, ' ') [4] = '2023' THEN 'Aptos ONE Mainnet Anniversary 2023' WHEN ARRAY_SIZE(SPLIT(change.nft_name, '_')) > 1 THEN 'Ordinals Sub 1k Text' ELSE change.nft_name :: VARCHAR END ) :: variant AS collection_name, change.nft_name AS nft_name FROM mercato_bids_sales_events1 main JOIN missed_object_names change ON main.tx_hash = change.tx_hash ), mercato_bids_sales_events AS ( SELECT main.*, obj.collection_name :: variant AS collection_name, obj.nft_name FROM mercato_bids_sales_events1 main LEFT JOIN collection_nft_info obj ON main.tx_hash = obj.tx_hash AND main.event_data:token.inner = obj.object_address AND main.tx_hash NOT IN ( SELECT tx_hash FROM mercato_bids_sales_events2 ) UNION SELECT * FROM mercato_bids_sales_events2 ), main_part_mercato AS ( SELECT block_timestamp, block_number, version, tx_hash, event_index, prev_event_index, event_kind AS event_type, COALESCE(event_data:buyer, event_data:bid_buyer) AS buyer_address, COALESCE( event_data:seller, event_data:owner, event_data:bid_seller ) AS seller_address, COALESCE( event_data:token_id.token_data_id.creator || '::' || event_data:token_id.token_data_id.collection || '::' || event_data:token_id.token_data_id.name || '::' || event_data:token_id.property_version, event_data:token.inner ) AS nft_address, (CASE WHEN LEN(nft_address) > 70 THEN 'v1' ELSE 'v2' END) AS token_version, event_address AS platform_address, COALESCE( event_data:token_id.token_data_id.collection, collection_name ) AS project_name, COALESCE(event_data:token_id.token_data_id.name, nft_name) AS tokenid, 'Mercato' AS platform_name, ( CASE WHEN ARRAY_SIZE(SPLIT(event_module, '_')) > 1 THEN 'tradeport_' || SPLIT(event_module, '_') [1] ELSE 'tradeport' END ) AS platform_exchange_version, event_data:price :: NUMBER AS price FROM mercato_bids_sales_events ), deposit_events_mercator AS ( SELECT tx_hash, event_index, event_data:amount :: NUMBER AS fee, account_address FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM main_part_mercato ) AND event_resource = 'DepositEvent' AND event_module = 'coin' ), main_with_creator_fees_raw AS ( SELECT * FROM ( SELECT main.*, creator.fee, creator.event_index AS creator_ev_index, creator.account_address AS creator_royalties_address FROM main_part_mercato main JOIN deposit_events_mercator creator ON main.tx_hash = creator.tx_hash AND main.event_index > creator.event_index AND main.prev_event_index <= creator.event_index ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY creator_ev_index ) = 1 ), main_with_creator_fees AS ( SELECT * EXCLUDE (fee, creator_ev_index, creator_royalties_address) FROM ( SELECT *, ( CASE WHEN creator_royalties_address = '0x6a03eb973cd9385d62fc2842d02a4dd6b70e52f5da77a0689e57e48d93fae1b4' THEN 0 ELSE fee END ) AS creator_fee_raw FROM main_with_creator_fees_raw ) ), mercato_main_platform_fees AS ( SELECT * EXCLUDE prev_event_index FROM ( SELECT main.*, platform.fee AS platform_fee_raw, creator_fee_raw + platform_fee_raw AS total_fees FROM main_with_creator_fees main JOIN deposit_events_mercator platform ON main.tx_hash = platform.tx_hash AND main.event_index > platform.event_index AND main.prev_event_index <= platform.event_index AND platform.account_address IN ( '0x6a03eb973cd9385d62fc2842d02a4dd6b70e52f5da77a0689e57e48d93fae1b4', '0x41699a1297fba9645eae628d909966659d2da5a425911c3d7bccd54ffce6606a', '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26' ) ) ), ----- Topaz topaz_sale_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_address = '0x2c7bccf7b31baf770fdbcc768d9e9cb3d87805e255355df5db32ac9a669010a2' AND event_resource = 'BuyEvent' ), topaz_bid_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_address = '0x2c7bccf7b31baf770fdbcc768d9e9cb3d87805e255355df5db32ac9a669010a2' AND event_resource = 'FillCollectionBidEvent' UNION SELECT * FROM aptos.core.fact_events WHERE event_address = '0x2c7bccf7b31baf770fdbcc768d9e9cb3d87805e255355df5db32ac9a669010a2' AND event_resource = 'SellEvent' ), topaz_bids_sales_events_raw AS ( SELECT *, 'sale' AS event_kind FROM topaz_sale_events_raw UNION SELECT *, 'bid_won' AS event_kind FROM topaz_bid_events_raw ), topaz_bids_sales_events AS ( SELECT *, ( CASE WHEN event_resource IN ('FillCollectionBidEvent', 'SellEvent') THEN 'bid_won' WHEN event_resource = 'BuyEvent' THEN 'sale' END ) AS event_kind FROM ( SELECT *, LAG(event_index, 1, 0) OVER ( PARTITION BY tx_hash ORDER BY event_index ) AS prev_event_index FROM ( SELECT *, LEN(event_type) AS event_type_len FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM topaz_bids_sales_events_raw ) ) WHERE event_type_len > 70 AND event_resource NOT IN ('TokenSwapEvent', 'SendEvent') ) WHERE event_address = '0x2c7bccf7b31baf770fdbcc768d9e9cb3d87805e255355df5db32ac9a669010a2' AND event_resource IN ( 'BuyEvent', 'FillCollectionBidEvent', 'SellEvent' ) ), main_part_topaz AS ( SELECT block_timestamp, block_number, version, tx_hash, prev_event_index, event_index, event_kind AS event_type, COALESCE(event_data:buyer, event_data:bid_buyer) AS buyer_address, COALESCE( event_data:seller, event_data:owner, event_data:bid_seller ) AS seller_address, event_data:token_id.token_data_id.creator || '::' || event_data:token_id.token_data_id.collection || '::' || event_data:token_id.token_data_id.name || '::' || event_data:token_id.property_version AS nft_address, (CASE WHEN LEN(nft_address) > 70 THEN 'v1' ELSE 'v2' END) AS token_version, event_address AS platform_address, event_data:token_id.token_data_id.collection AS project_name, event_data:token_id.token_data_id.name AS tokenid, 'Topaz' AS platform_name, 'Topaz' AS platform_exchange_version, event_data:price :: NUMBER AS price FROM topaz_bids_sales_events ), deposit_events_topaz AS ( SELECT tx_hash, event_index, event_data:amount :: NUMBER AS fee, account_address FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM main_part_topaz ) AND event_resource = 'DepositEvent' AND event_module = 'coin' ), topaz_main_with_creator_fees_raw AS ( SELECT * FROM ( SELECT main.*, creator.fee, creator.event_index AS creator_ev_index, creator.account_address AS creator_royalties_address FROM main_part_topaz main JOIN deposit_events_topaz creator ON main.tx_hash = creator.tx_hash AND main.event_index > creator.event_index AND main.prev_event_index <= creator.event_index WHERE creator.account_address != '0x2c7bccf7b31baf770fdbcc768d9e9cb3d87805e255355df5db32ac9a669010a2' ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY creator_ev_index ) = 1 ), topaz_main_with_creator_fees AS ( SELECT * EXCLUDE (fee, creator_ev_index, creator_royalties_address) FROM ( SELECT *, ( CASE WHEN creator_royalties_address = '0x2c7bccf7b31baf770fdbcc768d9e9cb3d87805e255355df5db32ac9a669010a2' THEN 0 ELSE fee END ) AS creator_fee_raw FROM topaz_main_with_creator_fees_raw ) ), topaz_main_platform_fees AS ( SELECT * EXCLUDE prev_event_index FROM ( SELECT main.*, platform.fee AS platform_fee_raw, creator_fee_raw + platform_fee_raw AS total_fees FROM topaz_main_with_creator_fees main JOIN deposit_events_topaz platform ON main.tx_hash = platform.tx_hash AND main.event_index > platform.event_index AND main.prev_event_index <= platform.event_index AND platform.account_address = '0x2c7bccf7b31baf770fdbcc768d9e9cb3d87805e255355df5db32ac9a669010a2' ) ), ----- Wapal wapal_buy_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_resource = 'ListingFilledEvent' AND event_address = '0x584b50b999c78ade62f8359c91b5165ff390338d45f8e55969a04e65d76258c9' ), wapal_bid_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_resource = 'TokenOfferFilledEvent' AND event_address = '0x584b50b999c78ade62f8359c91b5165ff390338d45f8e55969a04e65d76258c9' UNION SELECT * FROM aptos.core.fact_events WHERE event_resource = 'CollectionOfferFilledEvent' AND event_address = '0x584b50b999c78ade62f8359c91b5165ff390338d45f8e55969a04e65d76258c9' ), wapal_bids_sales_events AS ( SELECT *, 'sale' AS event_kind FROM wapal_buy_events_raw UNION SELECT *, 'bid_won' AS event_kind FROM wapal_bid_events_raw ), wapal_main_platform_fees AS ( SELECT block_timestamp, block_number, version, tx_hash, event_index, event_kind AS event_type, event_data:purchaser AS buyer_address, event_data:seller AS seller_address, COALESCE( event_data:token_metadata.creator_address || '::' || event_data:token_metadata.collection_name || '::' || event_data:token_metadata.token_name || '::' || event_data:token_metadata.creator_address || '::' || event_data:token_metadata.collection_name || '::' || event_data:token_metadata.property_version.vec [0], event_data:token_metadata.token.vec [0].inner ) AS nft_address, (CASE WHEN LEN(nft_address) > 70 THEN 'v1' ELSE 'v2' END) AS token_version, event_address AS platform_address, event_data:token_metadata.collection_name AS project_name, event_data:token_metadata.token_name AS tokenid, 'Wapal' AS platform_name, 'Marketplace' AS platform_exchange_version, event_data:price :: NUMBER AS price_raw, event_data:commission :: NUMBER AS platform_fee_raw, event_data:royalties :: NUMBER AS creator_fee_raw, platform_fee_raw + creator_fee_raw AS total_fees FROM wapal_bids_sales_events ), ----- Bluemove bluemove_bids_sales_events1 AS ( SELECT *, 'sale' AS event_kind FROM aptos.core.fact_events WHERE event_address IN ( '0xd520d8669b0a3de23119898dcdff3e0a27910db247663646ad18cf16e44c6f5' ) AND event_resource = 'ListingFilledEvent' UNION SELECT *, 'bid_won' AS event_kind FROM aptos.core.fact_events WHERE event_address IN ( '0xd520d8669b0a3de23119898dcdff3e0a27910db247663646ad18cf16e44c6f5' ) AND event_resource IN ( 'TokenOfferFilledEvent', 'CollectionOfferFilledEvent' ) ), bluemove_main_platform_fees1 AS ( SELECT block_timestamp, block_number, version, tx_hash, event_index, event_kind AS event_type, event_data:purchaser AS buyer_address, event_data:seller :: varchar AS seller_address, COALESCE( event_data:id.token_data_id.creator || '::' || event_data:id.token_data_id.collection || '::' || event_data:id.token_data_id.name || '::' || event_data:id.property_version, event_data:token_metadata.token.vec [0].inner, event_data:token_id.token_data_id.creator || '::' || event_data:token_id.token_data_id.collection || '::' || event_data:token_id.token_data_id.name || '::' || event_data:token_id.property_version ) AS nft_address, (CASE WHEN LEN(nft_address) > 70 THEN 'v1' ELSE 'v2' END) AS token_version, event_address AS platform_address, event_data:token_metadata.collection_name AS project_name, event_data:token_metadata.token_name AS tokenid, 'BlueMove' AS platform_name, 'bluemove_marketplace_token_v2' AS platform_exchange_version, event_data:price :: NUMBER AS price, event_data:commission :: NUMBER AS platform_fee_raw, event_data:royalties :: NUMBER AS creator_fee_raw, platform_fee_raw + creator_fee_raw AS total_fees_raw FROM bluemove_bids_sales_events1 ), bluemove_bids_sales_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_address = '0xd1fd99c1944b84d1670a2536417e997864ad12303d19eac725891691b04d614e' AND event_resource = 'BuyEvent' UNION SELECT * FROM aptos.core.fact_events WHERE event_address = '0xd1fd99c1944b84d1670a2536417e997864ad12303d19eac725891691b04d614e' AND event_resource IN ('AcceptOfferEvent', 'AcceptOfferCollectionEvent') ), bluemove_bids_sales_events2 AS ( SELECT *, ( CASE WHEN event_resource IN ('AcceptOfferEvent', 'AcceptOfferCollectionEvent') THEN 'bid_won' WHEN event_resource = 'BuyEvent' THEN 'sale' END ) AS event_kind FROM ( SELECT *, LAG(event_index, 1, 0) OVER ( PARTITION BY tx_hash ORDER BY event_index ) AS prev_event_index FROM ( SELECT *, LEN(event_type) AS event_type_len FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM bluemove_bids_sales_events_raw ) ) WHERE event_type_len > 70 ) WHERE event_address = '0xd1fd99c1944b84d1670a2536417e997864ad12303d19eac725891691b04d614e' AND event_resource IN ( 'BuyEvent', 'AcceptOfferEvent', 'AcceptOfferCollectionEvent' ) ), deposit_event_bluemove AS ( SELECT tx_hash, event_index, event_data:amount :: NUMBER AS amount, account_address FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM bluemove_bids_sales_events2 ) AND event_resource = 'DepositEvent' AND event_module = 'coin' ), bluemove_add_seller_info AS ( SELECT * FROM ( SELECT main.*, seller.account_address AS seller_address, seller.amount FROM bluemove_bids_sales_events2 main JOIN deposit_event_bluemove seller ON main.tx_hash = seller.tx_hash AND main.event_index > seller.event_index AND main.prev_event_index <= seller.event_index ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY amount DESC ) = 1 ), bluemove_add_creator_fee AS ( SELECT *, ( CASE WHEN amount = creator_fee_raw1 THEN 0 ELSE creator_fee_raw1 END ) AS creator_fee_raw FROM ( SELECT * FROM ( SELECT main.*, creator.account_address AS creator_address, creator.amount AS creator_fee_raw1 FROM bluemove_add_seller_info main JOIN deposit_event_bluemove creator ON main.tx_hash = creator.tx_hash AND main.event_index > creator.event_index AND main.prev_event_index <= creator.event_index WHERE creator.account_address != '0x6fa430a236dcae28b60bfd93d5a96f2225e0357bd1542b2b8da4a430bdceed64' ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY creator_fee_raw1 ) = 1 ) ), bluemove_add_platform_fee_raw AS ( SELECT * FROM ( SELECT main.*, platform.account_address AS platform_address, platform.amount AS platform_fee_raw FROM bluemove_add_creator_fee main JOIN deposit_event_bluemove platform ON main.tx_hash = platform.tx_hash AND main.event_index > platform.event_index AND main.prev_event_index <= platform.event_index WHERE platform.account_address = '0x6fa430a236dcae28b60bfd93d5a96f2225e0357bd1542b2b8da4a430bdceed64' ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY platform_fee_raw DESC ) = 1 ), bluemove_add_platform_fee AS ( SELECT * FROM bluemove_add_platform_fee_raw UNION SELECT *, '0x6fa430a236dcae28b60bfd93d5a96f2225e0357bd1542b2b8da4a430bdceed64' AS platform_address, 0 AS platform_fee_raw FROM bluemove_add_creator_fee WHERE tx_hash NOT IN ( SELECT tx_hash FROM bluemove_add_platform_fee_raw ) ), bluemove_main_platform_fees2 AS ( SELECT block_timestamp, block_number, version, tx_hash, event_index, event_kind AS event_type, COALESCE( event_data:buyer_address, event_data:offer_collection_item.offerer, event_data:offerer ) AS buyer_address, seller_address, COALESCE( event_data:id.token_data_id.creator || '::' || event_data:id.token_data_id.collection || '::' || event_data:id.token_data_id.name || '::' || event_data:id.property_version, event_data:token_metadata.token.vec [0].inner, event_data:token_id.token_data_id.creator || '::' || event_data:token_id.token_data_id.collection || '::' || event_data:token_id.token_data_id.name || '::' || event_data:token_id.property_version ) AS nft_address, (CASE WHEN LEN(nft_address) > 70 THEN 'v1' ELSE 'v2' END) AS token_version, event_address AS platform_address, COALESCE( event_data:id.token_data_id.collection, event_data:token_id.token_data_id.collection ) AS project_name, COALESCE( event_data:id.token_data_id.name, event_data:token_id.token_data_id.name ) AS tokenid, 'BlueMove' AS platform_name, 'Marketplace' AS platform_exchange_version, amount + platform_fee_raw + creator_fee_raw AS price_raw, platform_fee_raw, creator_fee_raw, platform_fee_raw + creator_fee_raw AS total_fees_raw FROM bluemove_add_platform_fee ), bluemove_main_platform_fees AS ( SELECT * FROM bluemove_main_platform_fees1 UNION SELECT * FROM bluemove_main_platform_fees2 ), ----- Seashrine seashrine_sale_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_address = '0xd5431191333a6185105c172e65f9fcd945ae92159ab648e1a9ea88c71e275548' AND event_resource = 'BuyListingEvent' ), deposit_events_seashrine AS ( SELECT tx_hash, event_index, event_data:amount :: NUMBER AS amount, account_address FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM seashrine_sale_events_raw ) AND event_resource = 'DepositEvent' AND event_module = 'coin' ), seashrine_sales_events AS ( SELECT *, 'sale' AS event_kind FROM ( SELECT *, LAG(event_index, 1, 0) OVER ( PARTITION BY tx_hash ORDER BY event_index ) AS prev_event_index FROM ( SELECT *, LEN(event_type) AS event_type_len FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM seashrine_sale_events_raw ) ) WHERE event_type_len > 70 ) WHERE event_address = '0xd5431191333a6185105c172e65f9fcd945ae92159ab648e1a9ea88c71e275548' AND event_resource = 'BuyListingEvent' ), seashrine_add_seller_info AS ( SELECT * FROM ( SELECT main.*, seller.account_address AS seller_address, seller.event_index AS seller_ev_index, seller.amount FROM seashrine_sales_events main JOIN deposit_events_seashrine seller ON main.tx_hash = seller.tx_hash AND main.event_index > seller.event_index AND main.prev_event_index <= seller.event_index ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY seller_ev_index ) = 1 ), seashrine_add_creator_fee AS ( SELECT * FROM ( SELECT main.*, creator.event_index AS creator_ev_index, creator.amount AS creator_fee_raw FROM seashrine_add_seller_info main JOIN deposit_events_seashrine creator ON main.tx_hash = creator.tx_hash AND main.event_index > creator.event_index AND main.prev_event_index <= creator.event_index ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY creator_ev_index ) = 2 ), seashrine_add_platform_fee AS ( SELECT * FROM ( SELECT main.*, platform.event_index AS platform_ev_index, platform.amount AS platform_fee_raw FROM seashrine_add_creator_fee main JOIN deposit_events_seashrine platform ON main.tx_hash = platform.tx_hash AND main.event_index > platform.event_index AND main.prev_event_index <= platform.event_index ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY platform_ev_index ) = 3 ), seashrine_main_platform_fees AS ( SELECT block_timestamp, block_number, version, tx_hash, event_index, event_kind AS event_type, event_data:lister AS buyer_address, seller_address, event_data:token_id.token_data_id.creator || '::' || event_data:token_id.token_data_id.collection || '::' || event_data:token_id.token_data_id.name || '::' || event_data:token_id.property_version AS nft_address, (CASE WHEN LEN(nft_address) > 70 THEN 'v1' ELSE 'v2' END) AS token_version, event_address AS platform_address, event_data:token_id.token_data_id.collection AS project_name, event_data:token_id.token_data_id.name AS tokenid, 'Seashrine' AS platform_name, 'seashrine_market' AS platform_exchange_version, amount + platform_fee_raw + creator_fee_raw AS price_raw, platform_fee_raw, creator_fee_raw, platform_fee_raw + creator_fee_raw AS total_fees_raw FROM seashrine_add_platform_fee ), ----- Souffl3 souffl3_sale_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_address = '0xf6994988bd40261af9431cd6dd3fcf765569719e66322c7a05cc78a89cd366d4' AND event_resource = 'BuyTokenEvent<0x1' ), deposit_events_souffl3 AS ( SELECT tx_hash, event_index, event_data:amount :: NUMBER AS amount, account_address FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM souffl3_sale_events_raw ) AND event_resource = 'DepositEvent' AND event_module = 'coin' ), souffl3_sale_events AS ( SELECT *, event_data:coin_per_token :: NUMBER AS price_raw, 'sale' AS event_kind FROM ( SELECT *, LAG(event_index, 1, 0) OVER ( PARTITION BY tx_hash ORDER BY event_index ) AS prev_event_index FROM ( SELECT *, LEN(event_type) AS event_type_len FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM souffl3_sale_events_raw ) ) WHERE event_type_len > 70 AND event_resource != 'TokenSwapEvent' ) WHERE event_address = '0xf6994988bd40261af9431cd6dd3fcf765569719e66322c7a05cc78a89cd366d4' AND event_resource = 'BuyTokenEvent<0x1' ), souffl3_add_seller_info AS ( SELECT * FROM ( SELECT main.*, seller.account_address AS seller_address, seller.event_index AS seller_ev_index, seller.amount FROM souffl3_sale_events main JOIN deposit_events_souffl3 seller ON main.tx_hash = seller.tx_hash AND main.event_index > seller.event_index AND main.prev_event_index <= seller.event_index WHERE seller.account_address != '0x37ccac35f1d5a11773d14e47786f112941eb92aaea3295a3487e1b6dc3810b2a' ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY seller_ev_index ) = 2 ), souffl3_add_creator_fee AS ( SELECT * FROM ( SELECT main.*, creator.event_index AS creator_ev_index, creator.amount AS creator_fee_raw FROM souffl3_add_seller_info main JOIN deposit_events_souffl3 creator ON main.tx_hash = creator.tx_hash AND main.event_index > creator.event_index AND main.prev_event_index <= creator.event_index ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY creator_ev_index ) = 1 ), souffl3_main_platform_fees AS ( SELECT block_timestamp, block_number, version, tx_hash, event_index, event_kind AS event_type, event_data:buyer AS buyer_address, seller_address, event_data:token_id.token_data_id.creator || '::' || event_data:token_id.token_data_id.collection || '::' || event_data:token_id.token_data_id.name || '::' || event_data:token_id.property_version AS nft_address, (CASE WHEN LEN(nft_address) > 70 THEN 'v1' ELSE 'v2' END) AS token_version, event_address AS platform_address, event_data:token_id.token_data_id.collection AS project_name, event_data:token_id.token_data_id.name AS tokenid, 'Souffl3' AS platform_name, 'souffl3' AS platform_exchange_version, price_raw, price_raw -(creator_fee_raw + amount) AS platform_fee_raw, creator_fee_raw, platform_fee_raw + creator_fee_raw AS total_fees_raw FROM souffl3_add_creator_fee ), ----- Okx okx_sale_events_raw AS ( SELECT * FROM aptos.core.fact_events WHERE event_address = '0x1e6009ce9d288f3d5031c06ca0b19a334214ead798a0cb38808485bd6d997a43' AND event_resource = 'OrderExecutedEvent<0x1' ), deposit_events_okx AS ( SELECT tx_hash, event_index, event_data:amount :: NUMBER AS amount, account_address FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM okx_sale_events_raw ) AND event_resource = 'DepositEvent' AND event_module = 'coin' ), okx_sale_events AS ( SELECT *, event_data:coin_per_token :: NUMBER AS price, 'sale' AS event_kind FROM ( SELECT *, LAG(event_index, 1, 0) OVER ( PARTITION BY tx_hash ORDER BY event_index ) AS prev_event_index FROM ( SELECT *, LEN(event_type) AS event_type_len FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM okx_sale_events_raw ) ) WHERE event_type_len > 70 ) WHERE event_address = '0x1e6009ce9d288f3d5031c06ca0b19a334214ead798a0cb38808485bd6d997a43' AND event_resource = 'OrderExecutedEvent<0x1' ), token_deposit_events_okx AS ( SELECT tx_hash, event_index, event_data:id.property_version AS property_version, event_data:id.token_data_id.creator AS creator_address, event_data:id.token_data_id.collection AS project_name, event_data:id.token_data_id.name AS tokenid FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM okx_sale_events_raw ) AND event_resource = 'DepositEvent' AND event_module = 'token' ), okx_add_token_info AS ( SELECT * FROM ( SELECT main.*, token.property_version, token.creator_address, token.event_index AS token_ev_index, token.project_name, token.tokenid FROM okx_sale_events main JOIN token_deposit_events_okx token ON main.tx_hash = token.tx_hash AND main.event_index > token.event_index AND main.prev_event_index <= token.event_index ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY token_ev_index ) = 1 ), okx_add_seller_info AS ( SELECT * FROM ( SELECT main.*, seller.account_address AS seller_address, seller.event_index AS seller_ev_index, seller.amount FROM okx_add_token_info main JOIN deposit_events_okx seller ON main.tx_hash = seller.tx_hash AND main.event_index > seller.event_index AND main.prev_event_index <= seller.event_index ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY amount DESC ) = 1 ), okx_add_creator_fee AS ( SELECT * FROM ( SELECT main.*, creator.event_index AS creator_ev_index, creator.amount AS creator_fee_raw FROM okx_add_seller_info main JOIN deposit_events_okx creator ON main.tx_hash = creator.tx_hash AND main.event_index > creator.event_index AND main.prev_event_index <= creator.event_index WHERE creator.account_address != '0x40034a19a59ec81325b4d6b727cba5327733e7fff511e74d7350ce2a1ba8ac44' ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY creator_fee_raw ) = 1 ), okx_add_platform_fee AS ( SELECT * FROM ( SELECT main.*, platform.event_index AS platform_ev_index, platform.amount AS platform_fee_raw FROM okx_add_creator_fee main JOIN deposit_events_okx platform ON main.tx_hash = platform.tx_hash AND main.event_index > platform.event_index AND main.prev_event_index <= platform.event_index WHERE platform.account_address = '0x40034a19a59ec81325b4d6b727cba5327733e7fff511e74d7350ce2a1ba8ac44' ) QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash, event_index ORDER BY platform_fee_raw DESC ) = 1 ), okx_main_platform_fees AS ( SELECT block_timestamp, block_number, version, tx_hash, event_index, event_kind AS event_type, event_data:buyer AS buyer_address, seller_address, creator_address || '::' || project_name || '::' || tokenid || '::' || property_version AS nft_address, (CASE WHEN LEN(nft_address) > 70 THEN 'v1' ELSE 'v2' END) AS token_version, event_address AS platform_address, project_name, tokenid, 'Okx' AS platform_name, 'okx-nft-marketplace' AS platform_exchange_version, event_data:executed_price AS price_raw, platform_fee_raw, creator_fee_raw, platform_fee_raw + creator_fee_raw AS total_fees_raw FROM okx_add_platform_fee ), ----- General all_nft_platform_sales AS ( SELECT * FROM okx_main_platform_fees UNION SELECT * FROM seashrine_main_platform_fees UNION SELECT * FROM souffl3_main_platform_fees UNION SELECT * FROM bluemove_main_platform_fees UNION SELECT * FROM wapal_main_platform_fees UNION SELECT * FROM topaz_main_platform_fees UNION SELECT * FROM mercato_main_platform_fees ), aggregator_nft_sales AS ( SELECT tx_hash, ( CASE WHEN payload_function ILIKE '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26%' THEN 'Mercato' WHEN payload_function ILIKE '0xb339d393479e88d35ebf440f230c3d47ffa87f81012eb29ba8a4a3b2c689eda9%' THEN 'Mercato' WHEN payload_function ILIKE '0xf6994988bd40261af9431cd6dd3fcf765569719e66322c7a05cc78a89cd366d4%' THEN 'Souffl3' END ) AS aggregator_name FROM aptos.core.fact_events WHERE payload_function IN ( '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26::markets_v2::buy_tokens_v2', '0xf6994988bd40261af9431cd6dd3fcf765569719e66322c7a05cc78a89cd366d4::Aggregator::batch_buy_script_V1', '0xf6994988bd40261af9431cd6dd3fcf765569719e66322c7a05cc78a89cd366d4::Aggregator::batch_buy_script_V2', '0xf6994988bd40261af9431cd6dd3fcf765569719e66322c7a05cc78a89cd366d4::Aggregator::batch_buy_script_V3', '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26::biddings::unlist_and_accept_token_bid_v2', '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26::markets::buy_tokens', '0xb339d393479e88d35ebf440f230c3d47ffa87f81012eb29ba8a4a3b2c689eda9::markets_v2::buy_tokens_v2', '0xb339d393479e88d35ebf440f230c3d47ffa87f81012eb29ba8a4a3b2c689eda9::markets_v2::buy_tokens_v3' ) ), all_nft_platform_sales_with_agg AS ( SELECT main.*, agg.aggregator_name FROM all_nft_platform_sales main LEFT JOIN aggregator_nft_sales agg ON main.tx_hash = agg.tx_hash AND main.platform_name != agg.aggregator_name GROUP BY ALL ), associated_transfers AS ( SELECT tx_hash, account_address, token_address FROM aptos.core.fact_transfers WHERE tx_hash IN ( SELECT tx_hash FROM all_nft_platform_sales_with_agg ) AND account_address IN ( SELECT seller_address FROM all_nft_platform_sales_with_agg ) AND transfer_event = 'DepositEvent' ), all_nft_platform_sales_with_token AS ( SELECT main.*, transfer.token_address AS currency_address FROM all_nft_platform_sales_with_agg main JOIN associated_transfers transfer ON main.tx_hash = transfer.tx_hash AND main.seller_address = transfer.account_address GROUP BY ALL ), tx_table_info_raw AS ( SELECT tx_hash, block_timestamp, sender AS origin_from_address, split(payload_function, '::') [0] AS origin_to_address, payload_function AS origin_payload_function, gas_unit_price * gas_used AS tx_fee_raw, '0x1::aptos_coin::AptosCoin' AS token_address FROM aptos.core.fact_transactions WHERE tx_hash IN ( SELECT tx_hash FROM all_nft_platform_sales ) ), imputed_price_info AS ( SELECT * FROM aptos.price.ez_hourly_token_prices QUALIFY ROW_NUMBER() OVER ( PARTITION BY token_address ORDER BY hour ) = 1 ), tx_table_info AS ( SELECT tx.tx_hash, tx.tx_fee_raw * POWER(10, - price.decimals) :: FLOAT AS tx_fee, tx_fee * price.price :: FLOAT AS tx_fee_usd, tx.origin_from_address, tx.origin_to_address, tx.origin_payload_function FROM tx_table_info_raw tx JOIN aptos.price.ez_hourly_token_prices price ON LOWER(tx.token_address) = LOWER(price.token_address) AND tx.block_timestamp >= price.hour QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash ORDER BY price.hour DESC ) = 1 UNION SELECT tx.tx_hash, tx.tx_fee_raw * POWER(10, - price.decimals) AS tx_fee, tx_fee * price.price AS tx_fee_usd, tx.origin_from_address, tx.origin_to_address, tx.origin_payload_function FROM tx_table_info_raw tx JOIN imputed_price_info price ON LOWER(tx.token_address) = LOWER(price.token_address) AND tx.block_timestamp < price.hour QUALIFY ROW_NUMBER() OVER ( PARTITION BY tx_hash ORDER BY price.hour DESC ) = 1 ), all_nft_platform_sales_with_price_info_raw AS ( SELECT main.*, price.symbol AS currency_symbol, main.price_raw * POWER(10, - price.decimals) :: FLOAT AS price, main.platform_fee_raw * POWER(10, - price.decimals) :: FLOAT AS platform_fee, main.creator_fee_raw * POWER(10, - price.decimals) :: FLOAT AS creator_fee, main.total_fees_raw * POWER(10, - price.decimals) :: FLOAT AS total_fees, main.price_raw * POWER(10, - price.decimals) :: FLOAT * price.price :: FLOAT AS price_usd, platform_fee :: FLOAT * price.price :: FLOAT AS platform_fee_usd, creator_fee :: FLOAT * price.price :: FLOAT AS creator_fee_usd, total_fees :: FLOAT * price.price :: FLOAT AS total_fees_usd, price.hour FROM all_nft_platform_sales_with_token main JOIN aptos.price.ez_hourly_token_prices price ON LOWER(main.currency_address) = LOWER(price.token_address) AND main.block_timestamp >= price.hour QUALIFY ROW_NUMBER() OVER ( PARTITION BY main.tx_hash, main.event_index ORDER BY price.hour ) = 1 ), imputed_price_related_data AS ( SELECT main.*, price.symbol AS currency_symbol, main.price_raw * POWER(10, - price.decimals) :: FLOAT AS price, main.platform_fee_raw * POWER(10, - price.decimals) :: FLOAT AS platform_fee, main.creator_fee_raw * POWER(10, - price.decimals) :: FLOAT AS creator_fee, main.total_fees_raw * POWER(10, - price.decimals) :: FLOAT AS total_fees, (main.price_raw * POWER(10, - price.decimals)) * price.price AS price_usd, platform_fee :: FLOAT * price.price :: FLOAT AS platform_fee_usd, creator_fee :: FLOAT * price.price :: FLOAT AS creator_fee_usd, total_fees :: FLOAT * price.price :: FLOAT AS total_fees_usd, price.hour FROM all_nft_platform_sales_with_token main JOIN imputed_price_info price ON LOWER(main.currency_address) = LOWER(price.token_address) WHERE tx_hash NOT IN ( SELECT tx_hash FROM all_nft_platform_sales_with_price_info_raw ) ), all_nft_platform_sales_with_price_info AS ( SELECT * FROM all_nft_platform_sales_with_price_info_raw UNION SELECT * FROM imputed_price_related_data ), all_nft_platform_sales_with_tx_info AS ( SELECT main.*, tx.tx_fee, tx.tx_fee_usd, tx.origin_from_address, tx.origin_to_address, tx.origin_payload_function FROM all_nft_platform_sales_with_price_info main JOIN tx_table_info tx ON main.tx_hash = tx.tx_hash GROUP BY ALL ), nft_sales_related_events AS ( SELECT tx_hash, event_index, fact_events_id, inserted_timestamp, modified_timestamp FROM aptos.core.fact_events WHERE tx_hash IN ( SELECT tx_hash FROM all_nft_platform_sales ) ), all_nft_platform_sales_with_other_info_raw AS ( SELECT main.*, ev.fact_events_id AS ez_nft_sales_id, ev.inserted_timestamp, ev.modified_timestamp FROM all_nft_platform_sales_with_tx_info main JOIN nft_sales_related_events ev ON main.tx_hash = ev.tx_hash AND main.event_index = ev.event_index GROUP BY ALL ), ez_nft_sales AS ( SELECT * EXCLUDE ( price_raw, platform_fee_raw, creator_fee_raw, total_fees_raw, hour ) FROM all_nft_platform_sales_with_other_info_raw ) SELECT * FROM ez_nft_sales LIMIT 1000

Proposed NFT Schema