Learning SQL and Ethereum (Part 3)

By June 23, 2021Ethereum
Click here to view original web page at towardsdatascience.com
Photo by Loic Leray on Unsplash

I’m going to create more bite-sized query breakdowns while explaining basic concepts in Ethereum, this is a continuation of the beginner and intermediate guides I wrote.

This article is written in partnership with my friend 0xKowloon, please go and check out his deep dive on the solidity architecture of BarnBridge to get a better understanding of the ins-and-outs of the protocol.

We’ve looked at Aave before, where you have to deposit some amount of collateral in one asset (like ETH) to borrow 75% of the value deposited in another asset (like USDC). You can earn fixed or variable interest on this just like in the traditional financial system, except in DeFi this rate is usually determined by the “utilization rate” of the asset you deposited or borrowed.

Let’s look at USDC for example on Compound (a similar protocol to Aave):

https://compound.finance/markets/USDC

The important chart is on the left, where the black line indicates the current utilization of USDC (total USDC borrowed/total USDC deposited), and the green and purple lines represent variable and fixed interest rate paid respectively. You’ll notice there’s a point at which the slope for interest rates jumps up, which’s typically set at around 80% — this is to try and avoid an asset pool from being 100% utilized (borrowed from).

Now, what if you were depositer willing to forfeit any extra return you earned in exchange for always being made whole on some fixed rate? For example, let’s say you and another investor both deposit variable rate USDC. You want to make 2% returns each year. The other investor has agreed to pay you from their deposit (principle) to make up the difference if the variable rate drops below 2% for some amount of time (difference*your principle*time_under_2%). In exchange, if the variable rate goes above 2% then you pay them the extra yield instead (difference*your principle*time_above_2%). This is essentially how BarnBridge and their SmartYield product works, where there are hundreds of investors who choose between the safe (2% guaranteed) or risky (2% + upside/downside) tranches.

However, if BarnBridge launched with their own asset pools for deposit and borrowing then they would have to grow the liquidity of the markets from scratch. Bootstrapping liquidity can be difficult and leads to really volatile utilization rates, which leads to volatile interest rates. This is where the concept of money legos is important — essentially BarnBridge can build a product where the assets you borrow from or deposit into their pools get directly deposited into Compound or Aave. This way BarnBridge can handle the double tranche logic in their smart contracts while leveraging the stability of markets in Compound or Aave. If you want to understand this money lego structure more, I recommend reading my explainer of Web3 products.

Now we can get into the data analysis. This is what we want to put together:

https://duneanalytics.com/ilemi/Barnbridge-PnL

To build-up to this, we need to know the main transactions (function calls and events emitted) users take on smart contracts. Remember, the SQL tables are based on the data of transactions from each smart contract. Users can either enter or leave the risky tranche (junior tokens/bonds) or enter and leave the safer tranche (senior bonds), so we have four main actions. There’s a bit of a caveat on the “leaving risky tranche” process, but we’ll tackle that later.

Let’s start with putting together the “total value locked” (meaning total assets deposited) into the protocol since inception. This means we need to add in total deposits and subtract total withdrawals from junior and senior tranches.

Depositing USDC into the junior tranche doesn’t work like depositing into Aave, because the junior tranche returns are dependent on how much they owe (or have gained) from the senior tranche. This means if you join the tranche after launch, it won’t be a 1:1 deposit of the underlying asset (USDC) and the token you get in return (bb_USDC). Likewise, when you sell, the price is not 1:1.

We calculate the ratio with:

SELECT  date_trunc('day', evt_block_time) as day,
contract_address,
"underlyingIn"-fee,
"tokensOut",
("underlyingIn"-fee)/"tokensOut" as ratios
FROM barnbridge."smartYield_evt_BuyTokens"

UNION ALL

SELECT date_trunc('day', evt_block_time) as day,
contract_address,
"underlyingOut",
"tokensIn",
"underlyingOut"/"tokensIn" as ratios
FROM barnbridge."smartYield_evt_SellTokens"
where "tokensIn" != 0

We’re querying the tables for the BuyTokens and SellTokens events on junior tranches in Barnbridge. Because there are many types of tokens in the tranche (such as USDC, Dai, USDT) we need to keep the contract_address column to filter by later on. tokensOut represents the tokens minted to the user, and underlyingIn is the asset deposited into the protocol. The ratio of underlying/tokens gives us the exchange rate between the two. We will use this ratio calculation in total value locked with total barnbridge tokens in pool * ratio to get back to the value of the total underlying asset.

day will only return the dates that transactions happened on, so there may be gaps on days where no one deposited or withdrew from BarnBridge. This would lead to a very choppy or distorted time-series graph, so we will fill in all the missing dates using a generate_series table.

SELECT generate_series('2021-03-14'::timestamp, date_trunc('day', NOW()), '1 day') AS day 

Next, we will calculate the next day (after the last day in the data) to serve as an endpoint for the series:

SELECT  ratios,
contract_address,
day,
lead(day, 1, now()) OVER (ORDER BY day) AS next_day
FROM ratioavg

This looks a little complicated, but it’s essentially creating a constant value column next_day as the last day in the time series.

We can put it all together with a JOIN:

SELECT  ratios, 
d.day,
a.contract_address,
symbol
FROM days d
left join ratio_with_lead_day a on a.day <= d.day AND d.day < a.next_day
left join dune_user_generated.smartyield_token_mapping u on u.contract_address= a.contract_address

This completes the time series with rows on days with no transactions (which will just show up as 0 in the time series chart) and end on next_day . We also join a table that maps contract_address to the token symbols of the underlying assets for readability and filtering later on.

All of this returns the following table

Full query here https://duneanalytics.com/queries/67210

Now that that is out of the way, we can look at the total deposits and withdrawals of junior tranche tokens. This starts with two queries:

SELECT  buyer,
evt_tx_hash,
contract_address,
"tokensOut" as deposit,
date_trunc('day', evt_block_time) as day

FROM barnbridge."smartYield_evt_BuyTokens"

And

SELECT  seller as buyer,
evt_tx_hash,
contract_address,
-"tokensIn" as deposit,
date_trunc('day', evt_block_time) as day

FROM barnbridge."smartYield_evt_SellTokens"

I don’t think these two need much explanation as the columns are self-explanatory, and we do the same date gap-filling here but by using a contract_address partition instead.

SELECT
buyer,
contract_address,
deposit,
day,
lead(day, 1, now()) OVER (partition by evt_tx_hash, contract_address ORDER BY day) AS next_day
FROM redeemjbonds

We put it all together with this somewhat messy looking query:

SELECT
sum(((d.deposit + coalesce(r.deposit,0))/10^u.u_decimals)*ratios) as deposits,
sum(coalesce(r.deposit,0)/10^u.u_decimals*ratios) as withdrawlsLEFT JOIN redeemfinal r on d.day = r.day and d.buyer = r.buyer and r.contract_address = d.contract_address LEFT JOIN dune_user_generated.smartyield_token_mapping u on u.contract_address= q.contract_addressWHERE((d.deposit + coalesce(r.deposit,0))*ratios)/10^u.u_decimals > 0
AND u."symbol"='cUSDC'

We’re joining the total deposits (depositfinal) of with the total withdrawals (redeemfinal) for each user, and also the asset (contract_address). We keep the dates aligned in this join as well. Next, we join the ratioq from our first query to the date as well (remember the ratio changes every day as interest and senior tranche payments/rewards accrue). Lastly, we join the mapping on contract_address to asset symbols and filter for just compound USDC deposits (cUSDC). As for what we choose to select, we take the total deposited subtracted by the total withdrawn (COALESCE is used here to cover the cases where a user has not withdrawn anything and the value is null, which we want to replace with 0 instead — think of it like a if null then value (0) function). Then we divide the sum by the decimals of the asset (since you don’t have decimals in solidity, we have to do the conversion in the frontend). Lastly, we multiple this deposit by the ratios because this deposit is represented in BarnBridge tokens and we need it to show USDC value instead. If this was not using a stablecoin asset like USDC, we would then multiple it by the price for each date using another join (to add the price column while joining on symbol and date).

This gives us the following table:

https://duneanalytics.com/queries/67226
https://duneanalytics.com/queries/67051/134549

Now the only thing left for us to calculate is the profit and loss per user and date. We want to check how much interest was earned or forfeited for users who have entered and completely exited the pools (i.e. deposit-withdrawals=0). Here comes the extra caveat I mentioned earlier, as when someone exits the junior tranche they need to compensate the senior tranche for any lost interest. Typically in a securitization style structure, tranches are locked in from issuance and no one can exit or enter as they please (at least not in a way that mutates the primary issuance). In BarnBridge, this compensation upon early withdrawal takes two forms: either in a forfeiture or in a junior bond that has takes into account the weight average maturity of interest owed.

This is calculated with:

SELECT date_trunc('day',buy."evt_block_time") as date, buy."buyer", buy."tokensIn", redeem."underlyingOut", buy."maturesAt", true as did_redeem
FROM barnbridge."smartYield_evt_BuyJuniorBond" buy
INNER JOIN barnbridge."smartYield_evt_RedeemJuniorBond" redeem ON buy."juniorBondId" = redeem."juniorBondId" AND buy."buyer" = redeem."owner"
INNER JOIN dune_user_generated."smartyield_token_mapping" map ON buy."contract_address"=map."contract_address"
WHERE map."u_symbol"='USDC'

Here I’m taking only the buyers who have both bought a bond (BuyJuniorBond ) and also redeemed it (RedeemJuniorBond). The join is on the buyer’s address as well as the junior bond ID (in case they have multiple with differing maturities and amounts). I created a column of true values as did_redeemto be used later on for sanity checks.

Then we take the UNION of these redemptions with the junior tranche deposit and withdrawal query (the jtokens_t table is a modified version of our original query):

SELECT "date","buyer","underlyingOut","underlyingIn","tokensOut","tokensIn", false as "did_redeem"
FROM jtokens_t
UNION
SELECT "date","buyer","underlyingOut", 0 as "underlyingIn", 0 as "tokensOut","tokensIn", "did_redeem"
FROM SELECT "date","buyer","underlyingOut","underlyingIn","tokensOut","tokensIn", false as "did_redeem"
FROM jtokens_t
UNION
SELECT "date","buyer","underlyingOut", 0 as "underlyingIn", 0 as "tokensOut","tokensIn", "did_redeem"
FROM junior_bond_redemptions

Notice that junior_bond_redemptions doesn’t have underlyingIn or tokensOut columns, so I created them with constant values of 0 so that the UNION function works as intended.

Lastly, we put together the SELECT query:

SELECT union_by_date."buyer", sum("underlyingOut" - "underlyingIn")/1e6 as net_profit_loss
FROM union_by_date
INNER JOIN
(SELECT "buyer"
FROM buyer_pivot
WHERE "out" - "in" = 0) as exit_buyers
ON union_by_date."buyer" = exit_buyers."buyer"
GROUP BY 1
ORDER BY 2 DESC

Here, I’m taking the PnL with sum(“underlyingOut” — “underlyingIn”)/1e6 for the subset of buyers who have completely exited the pool. I do this with an inner join on the subquery exit_buyers , and then divide by six decimals places for USDC. Lastly is a GROUP BY since I’m using an aggregate function and an ORDER BY to make the chart prettier.

That gives us this:

https://duneanalytics.com/queries/66812

It’s clearly a little skewed (someone must have been a whale), which is why I included the table view in the dashboard as well. For the date version of PnL, I made a slight adjustment to the final SELECT query but it is otherwise unchanged. On aggregate it looks fairly even, with losses probably forced by general crypto market stresses.

https://duneanalytics.com/queries/65891

If you’re curious about the dashboard itself, it’s just a drag and drop of the queries so there’s nothing complex to explain there. Dune has made that part really easy, no fiddling around in Python Plotly Dash or Bokeh to get the visualizations right or worrying about deployment.

That’s the end of this short explainer, hope you learned something about Ethereum and/or SQL!

All Today's Crypto News In One Place