binhachonAverage Price Shift for Synth Holders - Average percent from entry
Updated 2022-04-09
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
›
⌄
with minting_transaction as (
select
native_to_address,
to_asset,
sum(from_amount) / sum(to_amount) as average_price
from thorchain.swaps
where to_asset like '%/%'
group by native_to_address, to_asset
),
synth_price as (
select
block_id,
to_asset,
from_amount/to_amount as current_price
from thorchain.swaps
where to_asset like '%/%'
qualify row_number() over (partition by to_asset order by block_id desc) = 1
),
synth_holders_with_price as (
select
minting_transaction.*,
current_price,
current_price / average_price * 100 as percent_from_entry,
floor(percent_from_entry, -1) as rounded_percent_from_entry
from minting_transaction
inner join synth_price on (synth_price.to_asset = minting_transaction.to_asset) --substr(synth_price.to_asset, 5, 10) = substr(minting_transaction.to_asset, 5, 10)
)
select
avg(percent_from_entry) - 100
from synth_holders_with_price
Run a query to Download Data