nitsStandardized TVL
Updated 2022-04-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
›
⌄
with last_use as
(SELECT pool_name as pn , max(block_timestamp) as last_use from thorchain.swaps
GROUP by 1) ,
asset_prices_fa as
( SELECT pool_name,from_asset as a,from_asset_price as a_price
from
(SELECT *, from_amount_usd/from_amount as from_asset_price
, to_amount_usd/to_amount as to_asset_price
from thorchain.swaps
inner join last_use
on pn = pool_name and block_timestamp = last_use )),
asset_prices_ta as
( SELECT pool_name,to_asset as a, to_asset_price as a_price
from
(SELECT *, from_amount_usd/from_amount as from_asset_price
, to_amount_usd/to_amount as to_asset_price
from thorchain.swaps
inner join last_use
on pn = pool_name and block_timestamp = last_use )),
asset_prices as
(SELECT pool_name as pn ,a , max(a_price) as final_price from
(SELECT * from asset_prices_ta
UNION ALL
SELECT * from asset_prices_fa )
where a !='THOR.RUNE'
GROUP by 1,2 ),
stake as (SELECT date(block_timestamp) as day, pool_name, sum(rune_e8)/pow(10,8) as total_rune,sum(asset_e8)/pow(10,8) as total_assets
from thorchain.stake_events
GROUP by 1,2 ),
unstake as (SELECT date(block_timestamp) as day, pool_name, (-1)*sum(emit_rune_e8)/pow(10,8) as total_rune,(-1)*sum(emit_asset_e8)/pow(10,8) as total_assets from thorchain.unstake_events
GROUP by 1,2 ),
all_data as (SELECT day, pool_name, sum(total_rune) as rune_tvl, sum(total_assets) as assets_tvl, rune_tvl*8 as rune_tvl_usd
from
(SELECT * from stake
UNION ALL
SELECT * from unstake )
Run a query to Download Data