shenronRevenue to Emissions ratio by contract
Updated 2021-05-16
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with master as (
select
date_trunc('day',block_hour) as date
, contract_name
, avg(token_price) as underlying_token_price
, avg(reserves_token_amount) as reserves_ctoken
, sum(comp_speed) * 2 as comp_emissions_comp -- *2 because the contract reports the COMP emitted for EITHER the supply or borrow side of each market
, sum(comp_speed_usd) * 2 as comp_emissions_usd
from compound.market_stats
where block_hour > getdate() - interval '6 months'
group by 1,2
),
-- now all we need is the daily difference in the raw reserves for each market
-- then we can simply sum up the diffs in the final step
reservediff as (
select
date
, contract_name
, reserves_ctoken
, reserves_ctoken - lag(reserves_ctoken, 1) ignore nulls over (partition by contract_name order by date) as reserve_diff_ctoken
, reserve_diff_ctoken * underlying_token_price as reserve_diff_usd
from master
)
-- put it together: sum the diffs as revenue; sum the comp emissions (cost)
select
b.contract_name
--, sum(rd.reserve_diff_usd) as revenue_usd
--, sum(b.comp_emissions_usd) as emissions_usd
, ROUND(sum(rd.reserve_diff_usd)*1.0/NULLIF(sum(b.comp_emissions_usd),0),4) AS revenue_to_emissions_
from master b join reservediff rd on b.date = rd.date
and b.contract_name = rd.contract_name
WHERE b.contract_name not in ('cCOMP', 'cUNI')
GROUP BY 1
ORDER BY 2 DESC
Run a query to Download Data