Some thoughts on duplicated OpenSea rows in nft.trades

[onesuper]

Hi, @masquot

According to my investigation, multiple swaps within one transaction may bring duplicated rows in nft.trades.

Take this tx(0xbbed44a3f6ba1bdfb...) as an example:

It conducts 52 erc721 exchanges as a proxy for OpeaSean.

Using tx_hash as join key, the left join SQL may produce a Cartesian product, thus generating duplicated rows. Theoretically, there will be 2704(52x52) rows about this tx in nft.trades.

I notice nft.trades table has some unique index constraints, but there are still 1494 rows being inserted according to my Dune query: https://dune.xyz/queries/495980.

An overview of this problem

I aggregate the OpenSea trades of 2022-01-01.

863 transactions(with multiple erc721 tansfers inside) produce 29741 records (the expected number is 4329).

The vast majority of cases are caused by GenieSwap 0x0a267cf51ef038fc00e71801f5a524aec06e4f07 and only few other contracts.

I also wrote a more detailed survey to analyze the problem and be able to reproduce the problem on my dataset.

Let me know what you think!

[themez]

I believe this issue was fixed in nft.trades_v2_beta table: https://dune.com/queries/870181
However, there're other issues in v2 table, maybe that's the reason the SQL which generates this table is not published yet.

[soispoke]

Closing the issue as we're now maintaining nft.trades on the V2 engine