How to easily analyse Compound data with Dune Analytics
Dune Analytics is a platform for doing smart contract analysis using SQL.
Compound is an interest rate protocol on Ethereum.
Dune and Compound have partnered to make it as easy as possible to query, visualise and share the Compound data. This quick start guide assumes you have basic familiarity with SQL and smart contracts.
In order to get started analyzing Compound on Dune Analytics, you'll need a free account. If at any time during this guide you feel confused, feel free to peek into the growing Dune Analytics documentation, Compound's FAQ and documentation, or ask questions directly in the Dune Telegram or Compound Discord channels.
Compound is the first “liquidity pool” — instead of having assets borrowed directly by another user, you supply liquidity to a market, and users borrow from that market. In each market, interest rates are determined algorithmically (based on supply and demand), and interest accrues every Ethereum block.There are no pre-defined durations or terms (such as “90 days”) — you can use the Compound protocol for as short as one block, or as long as you’d like; you’re free to withdraw or repay at any time.
For more info on how the protocol works see the Compound FAQ.
Key actions on Compound
- Borrow - a Compound borrower borrow tokens or ETH.
- RepayBorrow - a Compound borrower repays their borrowed tokens or ETH.
- Mint - a Compound supplier supplies their tokens or ETH to the Compound protocol to earn interest. The user receives a quantity of cTokens equal to the underlying tokens supplied, divided by the current Exchange Rate.
- Redeem - a Compound supplier converts a specified quantity of cTokens into the underlying asset, and their tokens are returned. The amount of underlying tokens received is equal to the quantity of cTokens redeemed, multiplied by the current Exchange Rate.
- Liquidate - When a liquidation occurs, a liquidator may repay some or all of an outstanding borrow on behalf of a borrower and in return receive a discounted amount of collateral held by the borrower.
- Accrue Interest - Whenever someone interacts with the protocol, interest is accrued. Accruing interest is what leads to an increase in the Exchange Rate.
- Transfer - cTokens are transferred to a new owner. The new owner can now redeem the the underlying asset for the cToken if they want to.
Most interesting events and data tables
From the actions described above we have created these corresponding views that you can easily query:
- Borrowing compound.view_borrow
- Repay borrow compound.view_repay_borrow
- Mint cToken compound.view_mint
- Redeem cToken compound.view_redeem
- Transfer cToken compound.view_transfer_ctoken
- Liquidate a borrow view_liquidate_borrow
- Accrue Interest view_accrue_interest
These views have been created in collaboration between the Dune Analytics engineers and the Compound team. More details on what the tables can be found in the "Underlying Data" section below.
With the tables above you can do a SQL query like:
WHERE block_time > now() - interval '8 weeks'
GROUP BY 1,2
ORDER BY 1;
Which gives you a chart like this:
Dune users and Compound community members have already created neat queries that you can explore, fork and get inspired by:
- Compound Business Intelligence dashboard (created by Calvin from Compound)
- Different breakdowns of interest accrued (created by @teo_leibowitz)
- Different breakdowns of liquidations (created by @teo_leibowitz)
- How much collateral will be liquidated at different ETH prices (created by @teo_leibowitz)
💡Ideas for what to build
Consequences if Dai unpegs
There's currently (Tuesday March 17th) a possibility that Dai will no longer be pegged to the dollar. What will then happen to Dai positions on Compound? Which loans will get liquidated at what prices?
Here's a great example/starting point by @teo_leibowitz:
How many users are liquidated daily? How many liquidators are there? How much profit do they get? How much value will be liquidated at different ETH prices?
There's a great blog post for inspiration made by @tomschmidt here. Full of queries you can utilise.
Gas and block analysis
Gas price paid by Compound users over time. What % of blocks have Compound transactions in them? What's the breakdown of Compound usage across the different events?
Whale Activity Dashboard
Distribution of supply and borrow share across accounts. How much of the volume comes from the top 10 addresses?
Breakdown of cToken acquisition
By # and volume, are people getting it from transfers or mints
How active are users to supply and borrow? For how long do users keep their deposits in the protocol? Do mostly supply/borrow and stay idle or go actively in and out?
If we remove crypto price volatility, what do volumes in and out look like?
Compound activity vs. Maker Stability Fee
What's the correlation between the Maker Stability Fee, savings rate and user actions on Compound.
Compound Activity vs. crypto price actions
When ETH and token prices move, what does user activity on Compound look like?
View of activity by labeled smart contracts
Look at composability, for example how much activity is coming from Curve vs. PoolTogether. How much of the activity comes from other smart contracts vs. externally owned accounts?
The underlying data
The created views have the following schemas.
These underlying source of these views are tracked here, and are built on top of Dune Analytics state-of-the art smart contract data.
If, for your use case, you find that the above abstractions aren't a good fit, you can use the underlying tables that are generated directly from the smart contract ABIs. These are available in the compound_v2 - schema, and the Dune Analytics documentation will help you dive deeper into the data. The naming of the those tables follow the convention below