On-chain Analysis 101
SELECT * FROM ethereum.core.ez_token_transfers LIMIT 100;;
Time for your first query!
Whats a "query"?
- That's just a fancy way of saying the code you will write to make awesome tables and graphics in Flipside.
- And now that you know your data source: ethereum.core.ez_token_transfers, you can start coding!
- Below, you will see colortful code for a simple query.
- Below that is the result of the query in a table format.
CODE BREAKDOWN
- SELECT starts the query and precedes a list of columns you want returned from your data table.
- * is a special character that indicates to select ALL of the columns in the source data table (blocknumber, block_timestamp, tx_hash, etc etc)
- FROM tells your query where the data exists on flipside to pull data from
- LIMIT sets a limit on the amount of data we get back. We dont want EVERY Ethereum transer, so we just choose 100.
You decide you only want to see transfers of the LDO token on Ethereum network.
Filtering data with the WHERE clause allows you to do this.
- The _WHERE clause goes after the FROM clause and allows us to filter out data we don't want.
- The WHERE must be used with an operator ( = , >, <, >=, <=)
CODE BREAKDOWN
- Here we will use WHERE with the = operator.
- We will force our query to only give us data WHERE the contract_address (aka the token address) is equal to the contract_address of the token we are interested in (LDO)
- To do this, we have to copy and paste the contract address for LDO from Etherscan into our query. (0x5A98F....B32)
- NOTE: I also use the lower() function to make sure my address is all in lower case letters. If i didn't do this, the query would not be able to find any data as all of the addresses are in lower case.
But what if you don't want ALL of that data?
Let's say you don't care about TX_hash or the BLOCK_NUMBER, you're only interested in who sent the transfer, the date, the amount, and the token sent.
- Now, we will specify the exact columns of data we want from our source table.
CODE BREAKDOWN
- Instead of using the * (ALL) operator, now we will list the column names that we want after the SELECT clause.
- Each column name is separated by a comma , except for the last entry.
- Block_timestamp (date of the transfer), FROM_ADDRESS(address of the sender), contract_address (the token transferred), RAW_AMOUNT(amount of tokens sent)
SELECT BLOCK_TIMESTAMP, FROM_ADDRESS, SYMBOL, contract_address, AMOUNT FROM ethereum.core.ez_token_transfers LIMIT 100;
You're happy your query only shows you LDO transfers BUT you also decide you're only interested in transfers that occured after 15 August 2023...
Now you will still use WHERE but you will also use the Logical Operator: AND
- There are two main logical operators AND and OR
- For this example, we will just use AND
CODE BREAKDOWN
- Here, we add to our code with AND and > operator to say we only want dates on or after 15 August 2023.
Now, you decide it's not enough to just see a list of transfers on random dates and times. You want to know the total amount of tokens transferred by EACH WALLET.
To do this, you must use the SUM function and the GROUP BY clause
- SUM() is just one of many aggregation functions you can use: Count(), AVG(), MIN(), MAX(), MEDIAN
- SUM allows us to add the total amount of numbers in any column
- GROUP BY goes after the WHERE clause and we use it to aggregate columns that we cant include in the Sum() function (aka things we are not adding up like wallet addresses, we are just grouping them together)
CODE BREAKDOWN
- Here, we add the Sum() function around "AMOUNT" to get the total amount amount of LDO traded
- However, since we want to know the total amount traded by each individual wallet we must also include GROUP BY
- GROUP BY precedes the names of our columns not included in the sum function and is used to group similar data entries. IE it will group every similar address into a single row.
- NOTE: the order your list columns in the GROUP BY clause is the order of precedence that you want them grouped. In this case, we are grouping by address first, then symbol, then contract_address.
- Confused? Don't worry look at the data table result below. Notice how none of the wallet addresses repeat? This is because we grouped wallets.
- Notice how we have a new column labeled SUM(AMOUNT)? This is because we took the sum of all the tokens transferred for each wallet.
Now the table is almost perfect...but you realize it could be better if you could see just the top 100 wallets by total LDO transferred.
To do this, you simply add the ORDER BY clause
ORDER BY lets you organize your data results using any of your columns
- ORDER BY is used with a column name and either ASC or DESC to specify ascending or descending order
- It is placed between WHERE and LIMIT
CODE BREAKDOWN
- Here we add ORDER BY + the column we want to organize our results by (Sum(Amount)) + DESC
- The resulting table below this organizes each entry by the most tokens transferred to the least.
You're an aspiring On-chain Wizard.
You want to investigate token transfers on Ethereum.
But you're not sure where to start?
First, you must find the right data source
- There is so much data on Flipside, for a variety of networks.
- However, since you want Ethereum transfers, follow the steps in the picture below to find the table ez_token_transfers








This is just the beginning of your SQL journey.
If you made it this far, congratulations!
You are well on your way to becoming a data wizard.
Make sure to also save this infographic below as it will help you as a nice quick reference in the future. We did not cover all the concepts but perhaps I will do more in a later dashboard.

SELECT BLOCK_TIMESTAMP, FROM_ADDRESS, SYMBOL, contract_address, AMOUNT FROM ethereum.core.ez_token_transfers WHERE contract_address=lower('0x5A98FcBEA516Cf06857215779Fd812CA3beF1B32') LIMIT 100;
SELECT BLOCK_TIMESTAMP, FROM_ADDRESS, SYMBOL, contract_address, AMOUNT FROM ethereum.core.ez_token_transfers WHERE contract_address=lower('0x5A98FcBEA516Cf06857215779Fd812CA3beF1B32') AND block_timestamp>= '2023-8-15' LIMIT 100;
SELECT BLOCK_TIMESTAMP, FROM_ADDRESS, SYMBOL, contract_address, AMOUNT FROM ethereum.core.ez_token_transfers WHERE contract_address=lower('0x5A98FcBEA516Cf06857215779Fd812CA3beF1B32') AND block_timestamp>= '2023-8-15' GROUP BY FROM_ADDRESS, SYMBOL, contract_address LIMIT 100;
SELECT BLOCK_TIMESTAMP, FROM_ADDRESS, SYMBOL, contract_address, AMOUNT FROM ethereum.core.ez_token_transfers WHERE contract_address=lower('0x5A98FcBEA516Cf06857215779Fd812CA3beF1B32') AND block_timestamp>= '2023-8-15' GROUP BY FROM_ADDRESS, SYMBOL, contract_address ORDER BY Sum(Amount) DESC LIMIT 100;

You're so proud of your findings that you want to share them with the rest of the community.
So you decide to put your graph and table into a dashboard.
Click the + next to "My Work" and then click Dashboard
- Follow the steps below to add tables and charts to your dashboard. You can also add images as well.

