Web3 SQL Weekly #2: Finding contracts that have integrated with a contract
Showing how contract composability really works - by looking at integrations into Opensea Seaport.
Welcome back! I’ll be breaking down one (community submitted) query a week into byte-sized bits, making both the SQL and Blockchain concepts more digestible.
You can find all the weeks here, and submit/vote on future week queries here.
If you’re a complete beginner, you can reference this guide for any terms that are unfamiliar.
The video goes much more in the depth on explanations and examples, but you can also just read the written overview below:
What:
Today, I’ll cover how to get all the contracts that have integrated with a contract - specifically Opensea Seaport 1.1 in this case. You’ll learn more about:
how cross-contract calls in traces work
decode and match function signatures
get contract address metadata
filter for read versus write functions
Ultimately we’ll create this query and chart below:
Blockchain concepts:
Let’s look at an example by selecting an example integration from the quickstart dashboard. This table shows us that Gemswap is the top caller over the last 6 months, mostly due to the batchBuyWithETH function that invokes fulfillAvailableAdvancedOrders on the Seaport contract.
We get an example transaction by clicking the function links, and take the hash into blocksec. First we can see the ETH flows, and how a wallet called Gem which then called both Seaport and X2Y2 to swap for NFTs.
If you look at the exploded traces, you can see exactly where Gem calls Seaport after the batchBuyWithETH function is invoked:
In the video, I start out by filtering for this exact connection from the ethereum.traces table - so check it out if you’re still confused here on how the Gem is integrating into Seaport!
You’ll also need to know how different function types work. To put it simply, there are functions that modify state (like transferring ETH and tokens) and functions that just calculate some value (like getting the price of ETH from a chainlink oracle). The former are of the type ‘view’ or ‘pure’, and sit within the “stateMutability” parameter of the ABI for a given function. Here are some examples from the ethereum.signatures table where you can see the function signature “id”, the function name, and the ABI for that function with it’s “stateMutability”.
With that, you’re ready to start writing the query!
SQL Query Walkthrough:
We’re looking specifically at integrations into the Seaport contract, referencing this query here.
Let’s start by getting all the traces where the Seaport contract was called, and group by “integration_type” (column 1). This will give us the number of successful, distinct transactions from each unique contract integration. You’ll notice there’s a check for cardinality(trace_address) != 0, this is because the top level “empty” trace is always a wallet invoking the transaction. We don’t want to include wallets, so we filter out traces that are of 0 length (doing an “is not null” filter would work here too).
Next, we want to get labels for the integration contracts, since addresses are hard to interpret. We LEFT JOIN on ethereum.contracts by “address” and “from”, and do a COALESCE(c.name, “from”) to replace the address with the contract name if a match is found. The LEFT JOIN is used instead of an INNER JOIN because we don’t want to lose any addresses just because there isn’t a matched label found in our database. We also want to filter out gnosis safes and argent wallets, since those contract integrations just produce noise in our analysis. The % symbol used with LIKE means match anything around a string, so using `%gnosis%` means match anything that contains “gnosis” in it, no matter how many or what characters come before or after it.
Cool - so now we just want to filter out transactions where a read function was called. Modify functions are normally much more meaningful for us, unless you’re analyzing something like Chainlink oracle getPrice calls. For this, we take the ethereum.signatures table and LEFT JOIN ON the signature “id” column (4 byte identifier) to the ethereum.traces “input” column by the first 4 bytes (using the substring() function). We filter out traces where the matching input “stateMutability” is 'view’ or ‘pure’, but still keep traces where no matching function signature is found for a given input from the LEFT JOIN.
We also make this a time series using a date_trunc() on the “block_time” column to round down to the nearest week, and add a 2 to the GROUP BY. This gives us the number of transactions per week from each contract integration.
The final query that was references added parameters to make it easier to filter months, choose a chain and address, and choose to include read functions or not. The bar chart can easily be turned into percentage terms just be ticking the “Normalize to percentage” option under “Chart Options” after stacking.
You can find the simplified query we wrote in the video here.
Bonus question:
Can you think of a way to tie ETH transferred to each interaction? I’ll give you a hint, you’ll need to sum over the `call` value for each trace. So for our example transaction, you would get 0.0999 ETH as the transfer value from Gemswap.
This should be a pretty easy one to figure out if you watched the week #1 video, dm it to me if you get it!
Hope you learned something! Don’t forget you can submit/vote on future week queries here.
