Querying Historical Aave Borrow rate with Primer
TLDR
Primer URL Syntax
For reference, we will use the following top-level query parameters as shown below. The rest of the top-level query parameters are available here
Name | Description |
---|---|
match | Filters the records to pass only the documents that match the specified condition(s). |
group | Groups input elements by the specified _id expression and for each distinct grouping, outputs an element. Grouping by date operators is also possible. |
Getting Started
Side note
Blockchain ID | Chain Name | LendingPool | Contract Address |
---|---|---|---|
1 | Ethereum Mainnet |
Aave V2 | 0x7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9 |
137 | Matic Mainnet |
Aave V2 | 0x8dff5e27ea6b7ac08ebfdf9eb090f32ee9a30fcf |
Get borrow rates for Aave v2 Markets
We now use the Covalent API GET log events by topic hash endpoint with the following parameters. Note that the Covalent API currently limits the block range between the starting-block
and the ending-block
to 1 million blocks. Hence, this data will need to be fetched in batches of 1 million blocks.
Parameter | Description | Value |
---|---|---|
chainId |
Ethereum Mainnet |
1 |
topic |
Reserve Data Updated |
0x804c9b842b2748a22bb64b345453a3de7ca54a6ca45ce00d415894979e22897a |
starting-block |
current Ethereum mainnet block height, as of 2021-06-01T17:31:52Z |
12500000 |
ending-block |
latest block height value |
latest |
sender-address |
Aave v2 Lending Pool |
0x7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9 |
From Etherscan, the current Ethereum mainnet block height, as of “2021-06-01T17:31:52Z” is 12551266. For the ending-block field, we can use the value latest to ensure we get data up to the latest block height.
After feeding the parameters, this is what the endpoint looks like https://api.covalenthq.com/v1/1/events/topics/0x804c9b842b2748a22bb64b345453a3de7ca54a6ca45ce00d415894979e22897a/?starting-block=12500000&ending-block=latest&sender-address=0x7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9&key=ckey_docs.
Our response from the Covalent API looks something like this (only first couple records shown):
{
"data": {
"updated_at": "2021-06-01T22:32:08.245291190Z",
"items": [
{
"block_signed_at": "2021-05-24T23:17:48Z",
"block_height": 12500001,
"tx_offset": 55,
"log_offset": 133,
"tx_hash": "0xfcc8ac1834c9d4072769e3000cf0b31e717a63c669826af4135504c83f8b8277",
"_raw_log_topics_bytes": null,
"raw_log_topics": [
"0x804c9b842b2748a22bb64b345453a3de7ca54a6ca45ce00d415894979e22897a",
"0x000000000000000000000000514910771af9ca656af840dff83e8264ecf986ca"
],
"sender_contract_decimals": null,
"sender_name": null,
"sender_contract_ticker_symbol": null,
"sender_address": "0x7d2768de32b0b80b7a3454c06bdac94a69ddc7a9",
"sender_address_label": null,
"sender_logo_url": null,
"raw_log_data": "0x00000000000000000000000000000000000000000000013844fd4b40c1a8d37b00000000000000000000000000000000000000000019de33cc7328f9059c58f500000000000000000000000000000000000000000000bc9e60c033968220a4ab0000000000000000000000000000000000000000033b51940b994f792ebbc8f20000000000000000000000000000000000000000033c50a65d561d37d0015b59",
"decoded": {
"name": "ReserveDataUpdated",
"signature": "ReserveDataUpdated(indexed address reserve, uint256 liquidityRate, uint256 stableBorrowRate, uint256 variableBorrowRate, uint256 liquidityIndex, uint256 variableBorrowIndex)",
"params": [
{
"name": "reserve",
"type": "address",
"indexed": true,
"decoded": true,
"value": "0x514910771af9ca656af840dff83e8264ecf986ca"
},
{
"name": "liquidityRate",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "5760355363302566056827"
},
{
"name": "stableBorrowRate",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "31272466365674405857351925"
},
{
"name": "variableBorrowRate",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "890726455972084100146347"
},
{
"name": "liquidityIndex",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "1000166895460320265505589490"
},
{
"name": "variableBorrowIndex",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "1001371436844653983133948761"
}
]
}
},
{
"block_signed_at": "2021-05-24T23:17:48Z",
"block_height": 12500001,
"tx_offset": 64,
"log_offset": 165,
"tx_hash": "0x2fc4a7f8dbe03cfb961af4620739f041378d6fcf11c398f175bb95424c860680",
"_raw_log_topics_bytes": null,
"raw_log_topics": [
"0x804c9b842b2748a22bb64b345453a3de7ca54a6ca45ce00d415894979e22897a",
"0x000000000000000000000000dac17f958d2ee523a2206206994597c13d831ec7"
],
"sender_contract_decimals": null,
"sender_name": null,
"sender_contract_ticker_symbol": null,
"sender_address": "0x7d2768de32b0b80b7a3454c06bdac94a69ddc7a9",
"sender_address_label": null,
"sender_logo_url": null,
"raw_log_data": "0x000000000000000000000000000000000000000000190f9fe7097b6e9907283300000000000000000000000000000000000000000061e3a1357d6a1af16509850000000000000000000000000000000000000000001e579cb16aba901aca130a000000000000000000000000000000000000000003626e953454cc9db9e449450000000000000000000000000000000000000000037bf8cc52c4cad647ddf06e",
"decoded": {
"name": "ReserveDataUpdated",
"signature": "ReserveDataUpdated(indexed address reserve, uint256 liquidityRate, uint256 stableBorrowRate, uint256 variableBorrowRate, uint256 liquidityIndex, uint256 variableBorrowIndex)",
"params": [
{
"name": "reserve",
"type": "address",
"indexed": true,
"decoded": true,
"value": "0xdac17f958d2ee523a2206206994597c13d831ec7"
},
{
"name": "liquidityRate",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "30296930667889706360318003"
},
{
"name": "stableBorrowRate",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "118340755474379826698455429"
},
{
"name": "variableBorrowRate",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "36681510948759653396910858"
},
{
"name": "liquidityIndex",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "1047451972435115274808871237"
},
{
"name": "variableBorrowIndex",
"type": "uint256",
"indexed": false,
"decoded": true,
"value": "1078327821264292131647647854"
}
]
}
},
We will add Primer to the endpoint to filter out the response to get the historical borrow rates. Before we do that, there are some quick references to know.
Name | Description |
---|---|
$sum |
Sums all values with a specified value. |
$avg |
Averages all values with a specified value. |
Name | Description |
---|---|
$year |
Returns the year portion of a date. |
$month |
Returns the month of a year as a number between 1 and 12. |
$dayOfMonth |
Returns the day of the month as a number between 1 and 31. |
With all that in mind, let’s add Primer to the endpoint
---
header: Primer Query Parameters
---
// https://api.covalenthq.com/v1/1/events/topics/0x804c9b842b2748a22bb64b345453a3de7ca54a6ca45ce00d415894979e22897a/?starting-block=12500000&ending-block=latest&sender-address=0x7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9&primer=[{%22$match%22:{%22decoded.params.0.value%22:%220x6b175474e89094c44da98b954eedeac495271d0f%22}},{%22$group%22:{%22_id%22:{%22year%22:{%22$year%22:%22block_signed_at%22},%22month%22:{%22$month%22:%22block_signed_at%22},%22day%22:{%22$dayOfMonth%22:%22block_signed_at%22}},%22count%22:{%22$sum%22:1},%22variable_borrow_rate%22:{%22$avg%22:%22decoded.params.3.value%22}}}]&key=ckey_docs
[
{
"$match": {
"decoded.params.0.value": "0x6b175474e89094c44da98b954eedeac495271d0f"
}
},
{
"$group": {
"_id": {
"year": {
"$year": "block_signed_at"
},
"month": {
"$month": "block_signed_at"
},
"day": {
"$dayOfMonth": "block_signed_at"
}
},
"count": {
"$sum": 1
},
"variable_borrow_rate": {
"$avg": "decoded.params.3.value"
}
}
}
]
Understanding how Primer filters historical borrow rates for Aave Markets
Please reference this endpoint to understand what Primer is trying to filter for. https://api.covalenthq.com/v1/1/events/topics/0x804c9b842b2748a22bb64b345453a3de7ca54a6ca45ce00d415894979e22897a/?starting-block=12500000&ending-block=latest&sender-address=0x7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9&key=ckey_docs
Reference above, we want to filter out a reserve, in this case we have chosen 0x6b175474e89094c44da98b954eedeac495271d0f
which is a DAI stable coin. A reserve is the address of the underlying asset of the reserve.
We can first use Primer’s top-level query $match
to get this specified reserve and use dot notation to access the reserve field.
We can do this by using decoded.params.0.value
which accesses the value of the first param of the first decoded log event which is the reserve field.
{
"$match": {
"decoded.params.0.value": "0x6b175474e89094c44da98b954eedeac495271d0f"
}
}
After, we can count how many times that data was updated at that particular reserve using Primer’s Aggregation $sum
. We can group it by year, month, and day using Primer’s top-level query parameter $group
. We will then compute the average from all values of the variable borrow rate corresponding to that particular reserve using Primer’s Aggregation $avg
.
{
"$group": {
"_id": {
"year": {
"$year": "block_signed_at"
},
"month": {
"$month": "block_signed_at"
},
"day": {
"$dayOfMonth": "block_signed_at"
}
},
"count": {
"$sum": 1
},
"variable_borrow_rate": {
"$avg": "decoded.params.3.value"
}
}
}
Result from API Endpoint
There you have it! We have gotten the historical borrow rates for Aave Markets. This is the result after using Primer to filter out the API response.
// https://api.covalenthq.com/v1/1/events/topics/0x804c9b842b2748a22bb64b345453a3de7ca54a6ca45ce00d415894979e22897a/?starting-block=12500000&ending-block=latest&sender-address=0x7d2768dE32b0b80b7a3454c06BdAc94A69DDc7A9&primer=[{%22$match%22:{%22decoded.params.0.value%22:%220x6b175474e89094c44da98b954eedeac495271d0f%22}},{%22$group%22:{%22_id%22:{%22year%22:{%22$year%22:%22block_signed_at%22},%22month%22:{%22$month%22:%22block_signed_at%22},%22day%22:{%22$dayOfMonth%22:%22block_signed_at%22}},%22count%22:{%22$sum%22:1},%22variable_borrow_rate%22:{%22$avg%22:%22decoded.params.3.value%22}}}]&key=ckey_docs
{
"data": {
"updated_at": "2021-06-03T20:35:45.432451418Z",
"items": [
{
"id": {
"year": 2021,
"month": 5,
"day": 24
},
"variable_borrow_rate": 3.886201304093548e+25,
"count": 26.0
},
{
"id": {
"year": 2021,
"month": 5,
"day": 25
},
"variable_borrow_rate": 3.879000666476045e+25,
"count": 349.0
},
{
"id": {
"year": 2021,
"month": 5,
"day": 26
},
"variable_borrow_rate": 3.889214780074118e+25,
"count": 297.0
},
{
"id": {
"year": 2021,
"month": 5,
"day": 27
},
"variable_borrow_rate": 4.640587159027803e+25,
"count": 327.0
},
{
"id": {
"year": 2021,
"month": 5,
"day": 28
},
"variable_borrow_rate": 4.06120627967394e+25,
"count": 314.0
},
{
"id": {
"year": 2021,
"month": 5,
"day": 29
},
"variable_borrow_rate": 4.006701953684034e+25,
"count": 270.0
},
{
"id": {
"year": 2021,
"month": 5,
"day": 30
},
"variable_borrow_rate": 4.1062287935383295e+25,
"count": 282.0
},
{
"id": {
"year": 2021,
"month": 5,
"day": 31
},
"variable_borrow_rate": 4.600270904928996e+25,
"count": 283.0
},
{
"id": {
"year": 2021,
"month": 6,
"day": 1
},
"variable_borrow_rate": 4.3261550223796084e+25,
"count": 277.0
},
{
"id": {
"year": 2021,
"month": 6,
"day": 2
},
"variable_borrow_rate": 4.451015189994744e+25,
"count": 280.0
},
{
"id": {
"year": 2021,
"month": 6,
"day": 3
},
"variable_borrow_rate": 3.958298095888893e+25,
"count": 245.0
}
],
"pagination": null
},
"error": false,
"error_message": null,
"error_code": null
}