binhachonAverage Price Shift for Synth Holders - Average percent from entry
    Updated 2022-04-09
    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