CryptoIcicleThor-87.Standardized Swap Volume
Updated 2022-05-15
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
›
⌄
-- Payout 24.83 RUNE
-- Grand Prize 74.5 RUNE
-- Level Intermediate
-- Q87. A few weeks ago we did a question on standardized TVL. Now we’d like to see a similar analysis looking at swap volume
-- in a way that neutralized the impact of price changes. Feel free to look at this any way you’d like,
-- but one idea is to fix asset prices at their current value and apply that price historically to all the tokens that were swapped.
-- Show this metric by week or month (or both!)
-- Hint: use thorchain.swaps, thorchain.prices
with asset_price as (
select
pool_name,
avg(asset_usd) as price
from thorchain.prices
where block_timestamp::date = CURRENT_DATE - 1
group by pool_name
)
select
date_trunc({{time_interval}},block_timestamp) as date,
s.pool_name,
avg(to_amount * price) as non_rune_swap_volume_usd,
sum(non_rune_swap_volume_usd) over (partition by s.pool_name order by date asc rows between unbounded preceding and current row) as cum_non_rune_swap_volume_usd
from thorchain.swaps s join asset_price a on s.pool_name = a.pool_name and s.from_asset <> 'THOR.RUNE'
group by date, s.pool_nametw
Run a query to Download Data