Overview
- Example 1: Fetch all Uniswap Swap event logs between a time period
- Example 2: Fetch all DEX pools that contain a given token
- Example 3: The number of times a wallet has interacted with a set of contracts
- Example 4: The number of NFT transfers per month
Example 1: Fetch all Uniswap Swap event logs between a time period
Request
curl --location 'https://api.syve.ai/v1/sql' \
--header 'Content-Type: application/json' \
--data '{
"query": "SELECT * FROM eth_logs WHERE topic_0 = '\''0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'\'' AND address = '\''0xa43fe16908251ee70ef74718545e4fe6c5ccec9f'\'' AND timestamp BETWEEN 1690294975 AND 1690381375 ORDER BY timestamp DESC LIMIT 10"
}'
eth_logsis the reference to our Logs table.0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822is the Uniswap V2 Swap event signature.- The query uses the
timestampfield to return records between timestamp1690294975and1690381375. You can also use theblock_numberfield to specify a time period.
Try out in Postman: https://www.postman.com/dark-equinox-191074/workspace/syve-examples/request/11895150-047915c9-b3ae-4cc6-8269-ca92863941be?ctx=documentation
Response
[
{
"timestamp": "2023-07-26T14:21:23.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0x6c466e5dfc17fa0a3ff4000f7e666d5ed53ea6d4bbc41c0ec62eb3d5ee90bb24",
"block_number": 17777814,
"data": "0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000014a270ef4868b00000000000000000000000000000000000000000000712052a17650002fc488cdf0000000000000000000000000000000000000000000000000000000000000000",
"log_index": 418,
"record_index": 7111125600000418,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_2": "0x00000000000000000000000074de5d4fcbf63e00296fd95d33236b9794016631",
"topic_3": null,
"transaction_hash": "0xcbd24b8a2a2fd27cc73760bdc4b8a87cd0c698ac7a9c2470dc5c8603dfa54e14",
"transaction_index": 157
},
{
"timestamp": "2023-07-26T14:20:47.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0x28b4524833b8a66cd929f2a6d702eb83e56f3678c999876ae5a2ea326347e4b5",
"block_number": 17777811,
"data": "0x00000000000000000000000000000000000000000487a2aa00042c7a82e5e710000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000d235ef6909b4ca1",
"log_index": 124,
"record_index": 7111124400000124,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000003fc91a3afd70395cd496c647d5a6cc9d4b2b7fad",
"topic_2": "0x0000000000000000000000008e83de18b38ddc22166fb5454003a573a53be4ae",
"topic_3": null,
"transaction_hash": "0x7f02ac8b23faa089eddcab4dbe1eea69b9255d6ca1627a466b60b56b9131d240",
"transaction_index": 88
},
{
"timestamp": "2023-07-26T14:19:35.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0x8a3eb1b64c8b4dc4bb46c2b911ba09918a7c383d7267bb1308b0a36c6a1bbe9f",
"block_number": 17777806,
"data": "0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007d1ab2c75d7d034000000000000000000000000000000000000000002ade85fe224ca7be9c4cdfd0000000000000000000000000000000000000000000000000000000000000000",
"log_index": 173,
"record_index": 7111122400000173,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_2": "0x00000000000000000000000074de5d4fcbf63e00296fd95d33236b9794016631",
"topic_3": null,
"transaction_hash": "0xbd5585ec476713b3b7064b01fccade53d271f6d213827eb006dd5c9b489ab9f5",
"transaction_index": 47
},
{
"timestamp": "2023-07-26T14:16:23.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0xc6c317d586aa007a79fb2ed3ada90c3e4181cca609a6b27cb91ca64036eb758f",
"block_number": 17777790,
"data": "0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002103e7e540a7800000000000000000000000000000000000000000000b50fb7e96a4efe292cb46b0000000000000000000000000000000000000000000000000000000000000000",
"log_index": 304,
"record_index": 7111116000000304,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_2": "0x00000000000000000000000074de5d4fcbf63e00296fd95d33236b9794016631",
"topic_3": null,
"transaction_hash": "0x6af04d0449f145ae153112a79de0188aab48fe56ce06145df75aca8a5be1584c",
"transaction_index": 88
},
{
"timestamp": "2023-07-26T14:14:47.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0x5a3f5ca36caaa6cf87b16de7f954d9337a1a3c213dec1e05bde0a4f28a4cae90",
"block_number": 17777782,
"data": "0x0000000000000000000000000000000000000000003e09de2596099e2b0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000b3e7d26d625e21",
"log_index": 243,
"record_index": 7111112800000243,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_2": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_3": null,
"transaction_hash": "0xe64c356c16f5a282c6f4cbaa5fba6ff8cf553a721037e48513e6dc8af3767fc6",
"transaction_index": 93
},
{
"timestamp": "2023-07-26T14:13:59.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0x009b182e1f6815693639abcf1fbe176d97908cb00ab47aa4bc4438bf75381b11",
"block_number": 17777778,
"data": "0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000052f5711f619ff80000000000000000000000000000000000000000001c70880bc4dd954a13bc6240000000000000000000000000000000000000000000000000000000000000000",
"log_index": 166,
"record_index": 7111111200000166,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_2": "0x00000000000000000000000074de5d4fcbf63e00296fd95d33236b9794016631",
"topic_3": null,
"transaction_hash": "0x8f477249cbda699e4be2407d168399f1fe23f96c9470174e584de2f663dbd240",
"transaction_index": 39
},
{
"timestamp": "2023-07-26T14:12:23.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0x6a5f7e70940d2af5ccae8a84fff60931708e313ce3893cdb67eb23b93dcfbdee",
"block_number": 17777770,
"data": "0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003811a164c210ce00000000000000000000000000000000000000000013396184c77a70eecf454b0000000000000000000000000000000000000000000000000000000000000000",
"log_index": 255,
"record_index": 7111108000000255,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_2": "0x000000000000000000000000a7ca2c8673bcfa5a26d8ceec2887f2cc2b0db22a",
"topic_3": null,
"transaction_hash": "0x335ab09687a84e134ec863209ad5cb18831d868e9e41b654847914d493a63ace",
"transaction_index": 59
},
{
"timestamp": "2023-07-26T14:11:59.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0xde21b8fb80db28a0223e44b30b32400e9260d223c431d2886ca0102ac89404c7",
"block_number": 17777768,
"data": "0x000000000000000000000000000000000000000000211654585005212800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005fec8bbe5190f5",
"log_index": 156,
"record_index": 7111107200000156,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_2": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_3": null,
"transaction_hash": "0x7807e5200b13f65d3ccd7a3b77cadb9155d8aa736efcc986f59b064149c034cc",
"transaction_index": 72
},
{
"timestamp": "2023-07-26T14:08:11.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0xb1cdf4c1fa4c93fff589b1a572ef9fa394c445406fca9a1826b6d2c0b1b6c62e",
"block_number": 17777749,
"data": "0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013657ea37c626800000000000000000000000000000000000000000006a762d34c4a886022f2039f0000000000000000000000000000000000000000000000000000000000000000",
"log_index": 238,
"record_index": 7111099600000238,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000001111111254eeb25477b68fb85ed929f73a960582",
"topic_2": "0x00000000000000000000000074de5d4fcbf63e00296fd95d33236b9794016631",
"topic_3": null,
"transaction_hash": "0x6b64dffe60bc6fca9eff878815f4909a626d4fe194789ab6bdd91c169bf48c37",
"transaction_index": 88
},
{
"timestamp": "2023-07-26T14:06:11.000Z",
"address": "0xa43fe16908251ee70ef74718545e4fe6c5ccec9f",
"block_hash": "0x7b15ab43dd830c8251abdb2af6a2a63506372100ace65c0385c300e030710643",
"block_number": 17777739,
"data": "0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000058d15e176280000000000000000000000000000000000000000000001e7d45c9fe46a64f07f71de0000000000000000000000000000000000000000000000000000000000000000",
"log_index": 334,
"record_index": 7111095600000334,
"topic_0": "0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822",
"topic_1": "0x0000000000000000000000003fc91a3afd70395cd496c647d5a6cc9d4b2b7fad",
"topic_2": "0x000000000000000000000000bd62a612f1340111b861a038941eff0ccb4f12aa",
"topic_3": null,
"transaction_hash": "0xf67a41c19d159e164695c4d73c0f18fb7a9407a72d5299f49b6c19820c352261",
"transaction_index": 125
}
]
Example 2: Fetch all DEX pools that contain a given token
Query
curl --location 'https://api.syve.ai/v1/sql' \
--header 'Content-Type: application/json' \
--data '{
"query": "SELECT * FROM eth_pool_metadata WHERE token_0_address = '\''0x7391e573ddf984fb137a5de759b885c566ef28bc'\'' OR token_1_address = '\''0x7391e573ddf984fb137a5de759b885c566ef28bc'\'' LIMIT 10"
}'
Try out in Postman: https://www.postman.com/dark-equinox-191074/workspace/syve-examples/request/11895150-61c42f87-35c3-44c9-84e7-7b6723863d93?ctx=documentation
Response
[
{
"block_number_created": 17645097,
"pool_address": "0x8d0c16d5da9ca8a75ffd7979f2f4fe5f550671c6",
"protocol": "uniswap_v2",
"timestamp_created": "2023-07-07T23:09:23.000Z",
"token_0_address": "0x7391e573ddf984fb137a5de759b885c566ef28bc",
"token_1_address": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2"
}
]
Example 3: The number of times a wallet has interacted with a set of contracts
Query
curl --location 'https://api.syve.ai/v1/sql' \
--header 'Content-Type: application/json' \
--data '{
"query": "SELECT COUNT(*) as count, to_address FROM eth_transactions WHERE to_address IN ('\''0x2648b1863d0e3c7753a49571ba5ffb91814f702e'\'', '\''0xc65482703ebc29106d82c3ee7209a47a2b482712'\'', '\''0xd8e82cb955b3b0a976790616438e3b35463abeaf'\'') AND timestamp BETWEEN 1658840302 AND 1690376302 GROUP BY to_address"
}'
This query can be used to calculate the number of active users of a protocol over a given time period. A protocol will usually consist of multiple contract addresses.
The below request looks at how often wallets have interacted with the following three smart contracts: 0x2648b1863d0e3c7753a49571ba5ffb91814f702e, 0xc65482703ebc29106d82c3ee7209a47a2b482712, and 0xd8e82cb955b3b0a976790616438e3b35463abeaf.
Try out in Postman: https://www.postman.com/dark-equinox-191074/workspace/syve-examples/request/11895150-d6e4105b-b0fb-4405-a0bc-88014fa686d6?ctx=documentation
Response
[
{
"count": 1,
"to_address": "0x2648b1863d0e3c7753a49571ba5ffb91814f702e"
},
{
"count": 2,
"to_address": "0xc65482703ebc29106d82c3ee7209a47a2b482712"
},
{
"count": 4,
"to_address": "0xd8e82cb955b3b0a976790616438e3b35463abeaf"
}
]
Example 4: The number of NFT transfers per month
Query
The SQL API can be used to aggregate data across a set of time intervals using the HISTOGRAM clause.
curl --location 'https://api.syve.ai/v1/sql' \
--header 'Content-Type: application/json' \
--data '{
"query": "SELECT HISTOGRAM(timestamp, INTERVAL 1 MONTH) AS month, COUNT(*) as transfer_count FROM eth_erc721 WHERE timestamp >= NOW() - INTERVAL 12 MONTH GROUP BY month ORDER BY month"
}'
SELECT HISTOGRAM(timestamp, INTERVAL 1 MONTH) AS month: Creates monthly "buckets" from thetimestampfield.COUNT(*) as transfer_count: Counts the records in each bucket, labeled astransfer_count.FROM eth_erc721: References our ERC721 Transfers table.WHERE timestamp >= NOW() - INTERVAL 12 MONTH: Filters to include records from the last 12 months only.GROUP BY month: Groups records by each month's bucket, andORDER BY monthsorts them in ascending order.
Try out in Postman: https://www.postman.com/dark-equinox-191074/workspace/syve-examples/request/11895150-c052a0f2-0bf1-4c0a-acd7-de77b4c46fc5?ctx=documentation
Response
[
{
"month": "2022-07-01T00:00:00.000Z",
"transfer_count": 146647
},
{
"month": "2022-08-01T00:00:00.000Z",
"transfer_count": 12650341
},
{
"month": "2022-09-01T00:00:00.000Z",
"transfer_count": 13952161
},
{
"month": "2022-10-01T00:00:00.000Z",
"transfer_count": 10082386
},
{
"month": "2022-11-01T00:00:00.000Z",
"transfer_count": 6682714
},
{
"month": "2022-12-01T00:00:00.000Z",
"transfer_count": 8370821
},
{
"month": "2023-01-01T00:00:00.000Z",
"transfer_count": 8378929
},
{
"month": "2023-02-01T00:00:00.000Z",
"transfer_count": 7208480
},
{
"month": "2023-03-01T00:00:00.000Z",
"transfer_count": 7819819
},
{
"month": "2023-04-01T00:00:00.000Z",
"transfer_count": 5774601
},
{
"month": "2023-05-01T00:00:00.000Z",
"transfer_count": 2644761
},
{
"month": "2023-06-01T00:00:00.000Z",
"transfer_count": 5244941
},
{
"month": "2023-07-01T00:00:00.000Z",
"transfer_count": 2510986
}
]