Transpose SQL unlocks many amazing new queries on blockchain data that were previously impossible. Writing queries requires a basic knowledge of SQL — this guide is the best place to get started. SQL queries can look daunting when seeing them for the first time, so let’s dive in and understand their building blocks.
We’ll start with a simple example that returns the USD price of the 100 most recent NFT sales for the BAYC contract across all the most popular exchanges.
Your first decision when building a SQL query is understanding which columns to return with the `SELECT` keyword. This usually goes at the start of your query. If you aren’t sure, `*` serves as a wild card to return everything on the queried table.
These examples depend on selecting the correct table, see the next section for that.
Our example: We’ll be selecting the contract address, token ID and usd price of each sale:
`SELECT contract_address, token_id, usd_price`
You can easily see which columns exist in the Table browser in the playground:
You need to tell SQL which table you want to retrieve data from. Things get especially powerful when you start combining data across different tables, but we’ll get to that in another post.
Note: All our tables are namespaced with the name of the chain they pull from. For now we only support Ethereum mainnet but in the near future you’ll be able to do queries that join across tables from different chains.
For our example, we want to retrieve data from the nft_sales table:
`FROM ethereum.nft_sales`
Altogether we now have:
`SELECT block_number, token_id, usd_price FROM ethereum.nft_sales`
We need to filter the results to only retrieve sales for the BAYC contract address. We can do this with the `WHERE` clause. In this example, we’ll filter for sales where the contract address matches the BAYC address, but you can filter across any metric contained in any of the columns.
The following line will do the trick (don’t forget the single quotes around contract_address!):
` WHERE contract_address = '0xBC4CA0EdA7647A8aB7C2061c2E118A18a936f13D' `
Just like this, the query won’t run. This query would return every single BAYC sale that’s ever happened on Ethereum, and we can only return a limited amount of data per query. Let’s look into ordering and limiting to get more targeted results.
Ordering results is essential for defining what we want from a query. Ordering is achieved with the `ORDER BY` clause. Even queries without an explicit `ORDER BY` have an implicit order determined by the primary key of the table — however, specifying it explicitly makes things much clearer.
Usage: `ORDER BY [column_name] [ASC/DESC]`
Since we want to retrieve the results for the 100 most recent queries, we’ll want to order by the sale timestamp in descending order. We’ll also break our query out onto separate lines to make things more readable:
`ORDER BY timestamp DESC`
Adding this to our query we now have:
The last step is limiting the number of results we want so that our database can efficiently handle the query.
Limiting results is easy — we just add a LIMIT clause with the number of items to return. If this is the last part of our SQL statement, we need to add a semicolon at the end to tell our database to execute the whole query.
Let’s add the limit to return 100 sales:
We’ve now built a simple yet powerful query that combines choosing return columns, selecting a table, ordering our results and limiting our response size so we get a response quickly. Try this out for yourself in the playground by clicking HERE.
Try experimenting with the query by changing up some of these input values. As a starting point, open up the schema for the nft sales table (example below) and try selecting a few different columns like `exchange_name` or `seller_address`. See how easy it is to get exactly the data you need!
Try changing up the ordering to retrieve the 100 first NFT sales we’ve indexed on Ethereum. What exchanges did they happen on?
Hopefully this guide serves as a good starting point for figuring out how to tackle more complex queries. Unfortunately, sometimes some of the most useful data is hidden behind very complex queries. This is why we’ve created The Atlas — sharing and discovering the most powerful queries is even more important than being able to write them all yourself. We’re hoping to encourage the community to contribute queries they find helpful so that Transpose can be used to easily solve any complex use-case, even without in-depth SQL knowledge.