connorhDaily Revenue vs Comp Emissions
Updated 2021-12-25
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
›
⌄
-- revenue for compound is reflected in the change in
-- reserve value for each market over the course of some time interval
-- in this case we use 1 day as the interval
-- first we pull the daily stats we need to calculate both reserve changes
-- and daily COMP emissions
with baseline as (
select
date_trunc('day',block_hour) as date,
ctoken_address,
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,
ctoken_address,
reserves_ctoken,
reserves_ctoken - lag(reserves_ctoken, 1) ignore nulls over (partition by ctoken_address order by date) as reserve_diff_ctoken,
reserve_diff_ctoken * underlying_token_price as reserve_diff_usd
from baseline
)
-- put it together: sum the diffs as revenue; sum the comp emissions (cost)
select
b.date,
sum(rd.reserve_diff_usd) as revenue_usd,
sum(b.comp_emissions_comp) as emissions_comp,
Run a query to Download Data