binhachon9. [Hard] RUNE price vs Swapper activity - Main query
    Updated 2021-10-27

    with swap_table as(
    select block_timestamp, 'sell volume' as symbol,
    case when position('-', from_asset, 1) = 0 then substr(from_asset, position('.',from_asset,1) + 1) else
    substr(from_asset, position('.',from_asset,1) + 1, position('-', from_asset, 1) - position('.',from_asset,1) - 1) end as asset,
    from_amount as amount_usd from thorchain.swaps
    union all
    select block_timestamp, 'buy volume' as symbol,
    case when position('-', to_asset, 1) = 0 then substr(to_asset, position('.',to_asset,1) + 1) else
    substr(to_asset, position('.',to_asset,1) + 1, position('-', to_asset, 1) - position('.',to_asset,1) - 1) end as asset,
    to_amount as amount_usd from thorchain.swaps
    union all
    select block_timestamp, 'buy volume' as symbol,
    case when position('-', asset, 1) = 0 then substr(asset, position('.',asset,1) + 1) else
    substr(asset, position('.',asset,1) + 1, position('-', asset, 1) - position('.',asset,1) - 1) end as asset,
    asset_e8/1e8 as amount_usd from thorchain.outbound_events
    where tx_id is null
    and substr(MEMO, 5) = in_tx
    and in_tx not in (select distinct tx_id as tx_id from thorchain.liquidity_actions where tx_id is not null union all select distinct tx_id as tx_id from thorchain.swaps where tx_id is not null)
    )
    select date_trunc('day', block_timestamp) as blocktime, 'price' as symbol, avg(rune_usd) as rune_usd from thorchain.prices
    where block_timestamp > getdate() - interval'45 days'
    group by blocktime, symbol
    union all
    select date_trunc('day', block_timestamp) as blocktime, symbol, sum(amount_usd) as rune_usd from swap_table
    where block_timestamp > getdate() - interval'45 days'
    and asset = 'RUNE'
    group by blocktime, symbol






    -- lp_action as(
    -- select block_timestamp, lp_action, 'RUNE' as asset, rune_amount_usd as amount_usd from thorchain.liquidity_actions
    Run a query to Download Data