There are two types of sales transactions in mercato. One is a bid transaction and the other is a Buy (non-bid) transaction

1. In Buy (non-bid) transactions, the payload_function must be the following:

• 1. 0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26::markets::buy_tokens
• 2. 0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26::markets::buy_tokens_v2
• 3. 0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26::markets_v2::buy_tokens_v2

Then it should be noted that in Mercato, at some times, the event does not have the required values that we want to extract or is incomplete. Therefore, we have to choose another method that is more complete. Fortunately, in the payload section of each transaction, we can find arguments that have all the necessary information that we want to extract. such as marketplace name, price, creator_address, seller_address, collection_name.

To get this information, it is enough to extract information from arguments, but in some cases, the information we need may start from the second item of each array. The first value we need is the name of the marketplace. So we have to check that if the first value is the same as the marketplace name, then we start from the same index, otherwise we have to start from the second index of each array.

So the reason for the condition iff(payload:arguments[0][0] in ('Souffl3', 'Wapal', 'BlueMove', 'Topaz', 'Mercato', 'TradePort', 'SeaShrine', 'Okx', 'Mobius' ', 'mobius'), payload:arguments[0], payload:arguments[1]) in lateral flatten is the same thing. Be careful that sometimes the name of Mobius Marketplace is written in the form of mobius, so we consider both. Please note that the transactions made by Mercato itself exist under the TradePort name.

In the index of each array of the payload object, the required information and each purchased nft are specified and there are several arrays in order, each array is a representation of one of the required features that we need to extract. Because they are ordered, we can put the order condition. Therefore, in order to be able to extract the information of the same nft for each of the nft records, we have checked the condition as follows:

where mp.index = sl.index

and am.index = sl.index

and crt.index = sl.index

and cl.index = sl.index

and nf.index = sl.index

2. Bidding transactions are the second type of transactions that have a different payload_function. If the payload_function of each transaction is like '0xe11c12ec495f3989c35e1c6a0af414451223305b579291fc8f3d9d0575a23c26::biddings%' so it is a bid transaction. Now there are two types of events that are always present in these transactions: 'AcceptCollectionBidEvent', 'AcceptTokenBidEvent'

Now the problem is that these transactions do not have collection information. That means we cannot extract the collection name and nft_id. The information of these values can be found in the changes section. In order to find these values, the conditions a.event_data:token:inner and c.address must be equal, and also the condition c.change_data:collection is not null, so that the condition of the relationship between the fact_changes and fact_transactions tables is established.

Please note that some transactions may have both creator_address and collection_name values null. The number of these transactions is very limited, but after reviewing these transactions, we realized that these items should be deleted. There must be one of these two values, otherwise it must be deleted.

Important note:

I have been investigating the Mercato NFT Aggregator, and during my analysis, I encountered an issue related to calculating the platform fee. I observed that for NFTs sold on Mercato but originating from different marketplaces such as Wapal, Topaz, or Bluemove, the fees were directed to the respective platform fee addresses of those marketplaces. As a result, the Mercato Platform fee column in many transactions remained empty.

To address this problem, I am considering three possible solutions:

• 1. Consider Marketplace Platform Fees: In the first approach, I could factor in the platform fees of the individual marketplaces (Wapal, Topaz, Bluemove).
• 2. Create a New Column for Aggregator Transactions: The second solution involves creating a new column specifically for Aggregator transactions. This column would indicate that a particular transaction is of the aggregator type, allowing me to calculate the platform fee based on the origin marketplaces (Wapal, Topaz, etc.).
• 3. Focus Solely on Mercato Fee: The third option involves only taking into account the Mercato fee. For transactions that did not pay a fee to Mercato, the platform fee for those transactions would be considered Null.

I choose second one because it seems more logical and table will be in a correct way of decoding without any data loss

Platform Fee

To calculate the platform fee, we need to calculate the fee based on the type of marketplace because the aggregator transactions, the fee is transferred to the address of the same marketplace. One of the reasons that made us choose the second method, which has an aggregator column, was the same issue. Because here it will be clear where exactly this fee has been transferred.

To calculate the fee, we first created a table of fee addresses and then used it in the main section. Note that we have used case when for this purpose because if we wanted to write more concisely, the problem of nested queries would have arisen and therefore we had to explicitly write the name of the marketplace for each when. Note that the fee per nft is calculated based on the amount paid. That is, there may be a transaction where several nfts are purchased from different marketplaces, but due to the different price of each nft, the payment fee is also different. We wanted to write this fee through the transfer table without any formula, but there was no way to create a relation. But fortunately, because the fee paid is relatively based on the price paid for each nft, we just need to calculate the formula (price/total_price) * platform_fee. Based on each record and type of marketplace, we can correctly and accurately calculate the amount of Platform_fee for each record.