5. Join on-chain and off-chain data

Write a query that joins your data with blockchain data.

This page is part of the Getting Started guide. For the best experience, make sure to follow the guide step-by-step

This is where Space and Time shines—joining your use case-specific data to our indexed blockchain data in a single request.

With Space and Time, joining on-chain and off-chain data is as simple as running a query. In this example, we'll show you how to join data from the DAPP_USER_WALLETS table we created in Part three with some blockchain data using our REST APIs.

Specifically, we're going to ask a common question that's traditionally hard to answer: How many of our users (by address) held our ERC20 token before a given block?

How to join on-chain and off-chain data

Before we get started, please open the API Reference page open for the Data Query Language (DQL) API.

This endpoint requires two parameters:

  1. resourceId: <SCHEMA_NAME>.<TABLE_NAME>- In this case, we'll use:
ETHEREUM.FUNGIBLETOKEN_WALLET
  1. sqlText: This will be the SQL command (the query) you want to run. Let's look at all of users (by address) that held SWISE tokens before block 15480960:
SELECT DISTINCT WALLET_ADDRESS FROM ETHEREUM.FUNGIBLETOKEN_WALLET AS eth_erc_20 
JOIN SE_PLAYGROUND.DAPP_USER_WALLETS_1 AS dapp_users 
ON eth_erc_20.WALLET_ADDRESS = dapp_users.USER_WALLET_ADDRESS
WHERE eth_erc_20.TOKEN_ADDRESS = '0x48C3399719B582dD63eB5AADf12A40B4C3f52FA2' --SWISE Token
AND eth_erc_20.BLOCK_NUMBER > '15480960' LIMIT 100;

And when we bring it all together to run with cURL it looks like this:

curl -i --request POST \                                                        [16:12:48]
     --url "$SxT_DQL_API" \
     --header 'accept: application/json' \
     --header 'authorization: Bearer '"$AT"'' \
     --header 'content-type: application/json' \
     --data @- <<EOF
{
  "resourceId": "ETHEREUM.FUNGIBLETOKEN_WALLET",
  "sqlText": "SELECT DISTINCT WALLET_ADDRESS FROM ETHEREUM.FUNGIBLETOKEN_WALLET AS eth_erc_20
   JOIN SE_PLAYGROUND.DAPP_USER_WALLETS_1 AS dapp_users
   ON eth_erc_20.WALLET_ADDRESS = dapp_users.USER_WALLET_ADDRESS
   WHERE eth_erc_20.TOKEN_ADDRESS = '0x48C3399719B582dD63eB5AADf12A40B4C3f52FA2' 
   AND eth_erc_20.BLOCK_NUMBER > '15480960' LIMIT 100;"
}
EOF

And the output:

HTTP/2 200 
date: Tue, 04 Apr 2023 22:14:17 GMT
content-type: application/json
content-length: 193
vary: Origin
x-krakend: Version 2.1.1
x-krakend-completed: false
strict-transport-security: max-age=15724800; includeSubDomains

[
 {"WALLET_ADDRESS":"0x992f534fcc87864875224d142d6Bf054B1882160"},
 {"WALLET_ADDRESS":"0x1231DEB6f5749EF6cE6943a275A1D3E7486F4EaE"},
 {"WALLET_ADDRESS":"0xB825779da5f2D625540e04bf53015CD6fe38F969"}
]

Here we can see three addresses that exist inDAPP_USER_WALLETS_1.USER_WALLET_ADDRESS that held SWISE tokens before block 15480960!

👍

You joined your data with our indexed blockchain data!


And now you're ready to start using Space and Time for your use case! Soon, we'll be adding instructions for publishing your query results to a smart contract. For now, explore our APIs and start building on Space and Time.