DAY | NON_RUNE_TVL | AVG_SLIP_BP | |
|---|---|---|---|
1 | 2025-05-20 00:00:00.000 | 82986300.182882 | 5.629430441 |
2 | 2025-05-19 00:00:00.000 | 82842200.0360183 | 5.883638099 |
3 | 2025-05-18 00:00:00.000 | 85621254.5799751 | 5.73883111 |
4 | 2025-05-17 00:00:00.000 | 79566912.6445767 | 5.682828422 |
5 | 2025-05-16 00:00:00.000 | 82129125.1860983 | 5.84517436 |
6 | 2025-05-15 00:00:00.000 | 84876257.2809589 | 5.730797648 |
7 | 2025-05-14 00:00:00.000 | 86722212.1414444 | 5.719212274 |
8 | 2025-05-13 00:00:00.000 | 89914510.4485092 | 5.784827793 |
9 | 2025-05-12 00:00:00.000 | 85755656.0618677 | 5.967272514 |
10 | 2025-05-11 00:00:00.000 | 81818604.3135662 | 5.843795283 |
11 | 2025-05-10 00:00:00.000 | 82108273.0548609 | 5.79547839 |
12 | 2025-05-09 00:00:00.000 | 74431244.7677048 | 6.133476143 |
13 | 2025-05-08 00:00:00.000 | 70825108.0606368 | 5.762991829 |
14 | 2025-05-07 00:00:00.000 | 64704751.246433 | 5.572304629 |
15 | 2025-05-06 00:00:00.000 | 65574975.4427098 | 5.754291776 |
16 | 2025-05-05 00:00:00.000 | 64260467.4508599 | 5.679655664 |
17 | 2025-05-04 00:00:00.000 | 64371014.0661385 | 6.883820565 |
18 | 2025-05-03 00:00:00.000 | 65036562.4151092 | 6.843731733 |
19 | 2025-05-02 00:00:00.000 | 66405701.8795639 | 7.26062752 |
20 | 2025-05-01 00:00:00.000 | 68421454.5999297 | 7.15368226 |
pietrektNon RUNE TVL in LP
Updated 5 hours ago
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
›
⌄
-- WITH tvls AS (select to_date(block_timestamp) as day,
-- CASE
-- WHEN pool_name like '%BNB%' then 'BNB'
-- WHEN pool_name like '%ETH%' then 'ETH'
-- WHEN pool_name like '%AVAX%' then 'AVAX'
-- WHEN pool_name like '%BTC%' then 'BTC'
-- WHEN pool_name like '%DOGE%' then 'DOGE'
-- WHEN pool_name like '%BCH%' then 'BCH'
-- WHEN pool_name like '%LTC%' then 'LTC'
-- WHEN pool_name like '%GAIA%' then 'GAIA'
-- WHEN pool_name like '%TERRA%' then 'TERRA'
-- END AS label, asset_amount_usd
-- from thorchain.defi.fact_pool_block_balances
-- QUALIFY (block_timestamp = MIN(block_timestamp) OVER(PARTITION BY pool_name, time_slice(block_timestamp, 1, 'DAY', 'START'))))
-- SELECT day, label, sum(asset_amount_usd) as non_rune_tvl_usd,
-- SUM(non_rune_tvl_usd) OVER(PARTITION BY day) as total_non_rune_tvl_usd
-- FROM tvls GROUP BY label, day ORDER BY day DESC
with non_rune_tvl AS (SELECT day, total_value_pooled_usd / 2 as non_rune_tvl from thorchain.defi.fact_daily_tvl WHERE day is not null),
to_rune_swaps AS (SELECT block_timestamp, tx_id, from_asset, to_asset, (liq_fee_in_rune_e8 /to_e8) * pow(10,4) as slip, from_e8 / pow(10, 8) as from_amount, to_e8 / pow(10,8) as to_amount from thorchain.defi.fact_swaps_events where block_timestamp is not null and to_asset = 'THOR.RUNE'),
from_rune_swaps AS (SELECT block_timestamp, tx_id, from_asset, to_asset, (liq_fee_in_rune_e8 /from_e8) * pow(10,4) as slip, from_e8 / pow(10, 8) as from_amount, to_e8 / pow(10,8) as to_amount from thorchain.defi.fact_swaps_events where block_timestamp is not null and from_asset = 'THOR.RUNE'),
-- UNIONED
unioned AS (
SELECT block_timestamp, tx_id, from_asset, to_asset, from_amount, to_amount, slip FROM to_rune_swaps
UNION
SELECT block_timestamp, tx_id, from_asset, to_asset, from_amount, to_amount, slip FROM from_rune_swaps
),
union_grouped AS (SELECT to_date(block_timestamp) as day, avg(slip) as avg_slip_bp from unioned group by day),
joined AS (SELECT a.day, a.non_rune_tvl, COALESCE(avg_slip_bp, 0) as avg_slip_bp from non_rune_tvl as a left join union_grouped as b on a.day = b.day)
select * from joined where day > '2021-09-17' order by day DESC
Last run: about 5 hours agoAuto-refreshes every 24 hours
...
1336
74KB
6s