Quickstart

Introduction

Let’s start by setting the scene; you’re a developer working on a cutting-edge Web3 data enabled product. You need low-latency, high-bandwidth, interpretable, realtime information from the blockchain to make your product stand out. Fortunately, you’ve chosen Transpose, and this makes integrating any arbitrary Web3 data into your production application a breeze.

In this example, we’ll be focusing on pulling daily floor prices for the CryptoPunks NFT collection. You might be interested in integrating this data for any number of reasons; maybe you want this information for analytics, for executing realtime trades, or for helping to understand the liquidity of the NFT collection.

Before we get started, it’s important to note that NFT pricing data is only one of the limitless applications of Transpose data. Transpose indexes over 99.9% of trading volume on the Ethereum blockchain. We’ll work with our SQL Analytics API, and with one of our Verified Endpoints, in this quickstart guide.

API Key

Before proceeding, we need to authenticate ourselves with Transpose. To access any Transpose API, you'll need an API key. All API requests should include your API key in an X-API-KEY header. If you haven't already, you'll need to sign up and create a team (for free!). Once you have a team, you can find a list of your API keys in your team dashboard. When you have your API key, replace <YOUR-API-KEY> in the interactive examples below with your key.

Remember not to share your API key! Your API key is a secret, and should not be stored or exposed in a public manner.

1. Get all CryptoPunks NFT sales

Given that our final goal is to understand daily floor prices (the lowest price that a CryptoPunk was sold for on a given day), to get started it makes sense to pull the complete set of sales data from CryptoPunks throughout their entire history.

This is a very standard use-case, and we can leverage Transpose’s Verified Endpoints to do this. Specifically, we’ll use api.transpose.io/nft/sales-by-contract-address. We’ll need to pass the blockchain we want (Ethereum), the CryptoPunks contract address (0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB), the desired ordering (ascending, specified as asc), and a limit on the number of results we’d like (in this case, we’ll just do 10).

PythonNodecURL

import requests url = "https://api.transpose.io/nft/sales-by-contract-address" headers = { 'Content-Type': 'application/json', 'X-API-KEY': 'BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG', } params = { "chain_id": "ethereum", "contract_address": "0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB", "order": "asc", "limit": "10", } response = requests.get(url, headers=headers, params=params) print(response.text) print('Credits charged:', response.headers.get('X-Credits-Charged', None))

const https = require('https'); const querystring = require('querystring'); const params = { chain_id: "ethereum", contract_address: "0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB", order: "asc", limit: "10", } var options = { hostname: 'api.transpose.io', path: '/nft/sales-by-contract-address?' + querystring.stringify(params), method: 'GET', headers: { 'X-API-KEY': 'BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG' } }; var req = https.request(options, (res) => { console.log('Credits charged:', res.headers['x-credits-charged']); res.on('data', (d) => { process.stdout.write(d); }); }); req.on('error', (e) => { console.error(e); }); req.end();

curl -X GET \
    'https://api.transpose.io/nft/sales-by-contract-address?chain_id=ethereum&contract_address=0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB&order=asc&limit=10' \
    -H 'X-API-KEY: BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG' \

Voila! How easy was that?

2. Group CryptoPunks sales by date

So, we’ve got every CryptoPunk sales event in all of history. Now, the next step is to group this data by the date that it occurred on.

Transforms and aggregations on huge datasets? Sounds like a job for SQL! With the Transpose SQL Analytics API, we do all the heavy lifting for you - you give us a query, we return you the results in milliseconds. Let’s put that to the test, and group these results by date.

The following SQL query selects all NFT sales, filters them to only include CryptoPunks, groups them by date, and returns the count of the number of sales events that occurred on that particular day - an important step towards our ultimate goal of calculating the daily floor price.

SQL Query

SELECT
/* extract date */
timestamp::date AS date
FROM ethereum.nft_sales AS sales 
/* specify CryptoPunks contract address */
WHERE sales.contract_address = '0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB'
/* group sales by date */
GROUP BY date
/* skip days with no sales */
HAVING COUNT(*) > 0; 

PythonNodecURL

import requests url = "https://api.transpose.io/sql" sql_query = """SELECT timestamp::date AS date FROM ethereum.nft_sales AS sales WHERE sales.contract_address = '0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB' GROUP BY date HAVING COUNT(*) > 0;""" headers = { 'Content-Type': 'application/json', 'X-API-KEY': 'BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG', } response = requests.post(url, headers=headers, json={ 'sql': sql_query, }, ) print(response.text) print('Credits charged:', response.headers.get('X-Credits-Charged', None))

const https = require('https'); var postData = JSON.stringify({ sql: "SELECT timestamp::date AS date FROM ethereum.nft_sales AS sales WHERE sales.contract_address = '0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB' GROUP BY date HAVING COUNT(*) > 0;" }); var options = { hostname: 'api.transpose.io', path: '/sql', method: 'POST', headers: { 'Content-Type': 'application/json', 'Content-Length': postData.length, 'X-API-KEY': 'BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG' } }; var req = https.request(options, (res) => { console.log('Credits charged:', res.headers['x-credits-charged']); res.on('data', (d) => { process.stdout.write(d); }); }); req.on('error', (e) => { console.error(e); }); req.write(postData); req.end();

curl --request POST \
     --data '{"sql": "SELECT
timestamp::date AS date
FROM ethereum.nft_sales AS sales 
WHERE sales.contract_address = '0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB'
GROUP BY date
HAVING COUNT(*) > 0;"}' \
     --url 'https://api.transpose.io/sql' \
     --header 'Content-Type: application/json' \
     --header 'x-api-key: BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG' \

3. Add logic to get the daily floor price

Now that we have a working query that can group CryptoPunks sales by the date on which they occurred, we can take all of the sales that occurred each day, and calculate the floor price.

Given that floor prices can be subject to a lot of dataset noise, here we use a ‘smart floor price’ - computed by taking the value at the bottom 20th percentile of the daily data.

Take a moment to examine this revised query, and note the (small) differences between this query and our last query. Then let it rip!

SQL Query

SELECT
/* extract date */
timestamp::date AS date,
/* calculate smart floor price as bottom 5 percentile of USD sale prices */
percentile_disc(0.2) WITHIN GROUP (ORDER BY usd_price) AS floor_price
FROM ethereum.nft_sales AS sales 
/* specify CryptoPunks contract address */
WHERE sales.contract_address = '0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB'
/* group sales by date */
GROUP BY date
/* skip days with no sales */
HAVING COUNT(*) > 0; 

PythonNodecURL

import requests url = "https://api.transpose.io/sql" sql_query = """SELECT timestamp::date AS date, percentile_disc(0.2) WITHIN GROUP (ORDER BY usd_price) AS floor_price FROM ethereum.nft_sales AS sales WHERE sales.contract_address = '0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB' GROUP BY date HAVING COUNT(*) > 0;""" headers = { 'Content-Type': 'application/json', 'X-API-KEY': 'BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG', } response = requests.post(url, headers=headers, json={ 'sql': sql_query, }, ) print(response.text) print('Credits charged:', response.headers.get('X-Credits-Charged', None))

const https = require('https'); var postData = JSON.stringify({ sql: "SELECT timestamp::date AS date, percentile_disc(0.2) WITHIN GROUP (ORDER BY usd_price) AS floor_price FROM ethereum.nft_sales AS sales WHERE sales.contract_address = '0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB' GROUP BY date HAVING COUNT(*) > 0;" }); var options = { hostname: 'api.transpose.io', path: '/sql', method: 'POST', headers: { 'Content-Type': 'application/json', 'Content-Length': postData.length, 'X-API-KEY': 'BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG' } }; var req = https.request(options, (res) => { console.log('Credits charged:', res.headers['x-credits-charged']); res.on('data', (d) => { process.stdout.write(d); }); }); req.on('error', (e) => { console.error(e); }); req.write(postData); req.end();

curl --request POST \
     --data '{"sql": "SELECT
timestamp::date AS date,
percentile_disc(0.2) WITHIN GROUP (ORDER BY usd_price) AS floor_price
FROM ethereum.nft_sales AS sales 
WHERE sales.contract_address = '0xb47e3cd837dDF8e4c57F05d70Ab865de6e193BBB'
GROUP BY date
HAVING COUNT(*) > 0;"}' \
     --url 'https://api.transpose.io/sql' \
     --header 'Content-Type: application/json' \
     --header 'x-api-key: BtRVYj7dgnYUcr1gSSfWhmrTShIb8RBG' \

Now, it's your turn!

Try experimenting with the requests above yourself. For example, you could change the contract addresses to target different NFT Collections (i.e. why not give Bored Ape Yacht Club a go? The contract address for Bored Ape Yacht Club is 0xBC4CA0EdA7647A8aB7C2061c2E118A18a936f13D ).

When you’re ready to experiment further, we recommend:

Exploring the Atlas

Explore and contribute to queries created by the Transpose community.

Exploring the Playground

Write and execute SQL queries in our browser-based development tool.

Join our Discord

Discord is the primary home of the Transpose developer community. Join us to ask questions, share your work, and get help.