Web3 SQL Weekly #4: Getting New and Old Users (Wallets) for a Protocol

Finding trends between new and old users can tell you if a protocol (or domain) is starting to make a comeback.

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 calculate new and returning (old) users over a period of time, for a given contract. This is a great way to get a sense of overall wallet trends given a protocol.

This query is part of the contract quickstart dashboard which I covered in an earlier article.

Blockchain concepts:

There are two key decisions I make in counting wallets:

• I include both indirect and direct interactions

• I use function calls, not event logs.

The diagram below shows how I define “indirect” versus “direct”. Direct means I signed a transaction calling Seaport directly (trading in the Opensea UI). Indirect means I signed a transaction calling one contract, which then called Seaport. Trading on the new Uniswap UniversalRouter app page would be an example of this.

This indirect/direct structure is also useful for tracking users across protocol segments. You could apply this to the whole protocol, or just a portion of it - depending on your needs. For example, you could plug in the Uniswap Router to get all swap users, or you could plug in just the USDC-WETH pair to get all swaps users of that pair.

Transactions (wallet calling a function) and internal transactions (contracts calling a function) both show up in the ethereum.traces table, so we’ll use that as a starting point to calculate all transactions involving Seaport (i.e. when Traces “to” is the Seaport address).

The second point on using only functions as the starting point is simply because many contracts get a lot of function call interactions, but don’t emit any events. All Uniswap Router contracts don’t emit events, even though they are the starting point of millions of transactions. So, no ethereum.logs table usage in this week’s query - however, if you want to run this transaction over a very long timeline, then you might need to use logs instead of traces simply because it’s faster to query (logs is the smaller table).

SQL Query Walkthrough:

Here’s the query we’re going to create today.

Let’s start with a filter on traces, and a join on transactions to get the top-level wallet that signed the transaction.

Like last week, I use {{ }} to parameterize things so the query is more flexible. We filter on block_time as well just to make the query performant, and we only want traces where the execution was successful (didn’t revert). This query gives me a trace for each time the Seaport contract was called, as well as transaction data on what wallet signed the transaction (the “from” column of the ethereum.transactions table).

Next, we’ll take a distinct on week and user - this gives us every single week that a user interacted with the Seaport contract.

From here, we have to use our trusty row_number() window function again. We partition by users and order by week, so the first week a user comes the “times_used” column will be 1, then the next time it’ll be 2, etc. Repeat this for every unique user.

We’re almost done - now comes the fun logic. We use a CASE WHEN to create categories for users. If the “times_used” column is 1, then that’s the first week they’ve used the contract so they’re a ‘new’ user. If the “times_used” column is greater than 1, then they’re a returning user so they’re ‘old’.

There you have it! A pretty short query this week, hopefully you’re getting more familiar with the window functions now and how the traces table works. We plot a bar chart and group by “user_type” to get the below output:

First week of data is always going to be 100% new users

You can see that There was a noticeable dip in old users around the whole FTX blowup, but now they’re healthily returning. New users are still stagnant, which is an unfortunate sign.

The query again is here, note that in that original query I used “new_old” instead of “times_used” but it’s the same exact thing.

Bonus question:

Can you make this query multi-chain? Protocols now will often deploy to the same address on multiple chains, so that makes your query easier to extend :) Try to analyze Opensea Seaport 1.1 on both Ethereum and Polygon.

Hope you learned something! Don’t forget you can submit/vote on future week queries here.