binhachon9. [Hard] RUNE price vs Swapper activity - Main query
Updated 2021-10-27
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
36
›
⌄
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