shenronCompound playground
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
›
⌄
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.date
, ROUND(sum(rd.reserve_diff_usd)*1.0/sum(b.comp_emissions_usd),4) AS revenue_to_emissions_usd
from master b join reservediff rd on b.date = rd.date
and b.contract_name = rd.contract_name
GROUP BY 1
;
Run a query to Download Data