hessTop Assets (ETH -> Thor)
Updated 2022-07-24
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 thor as ( select (block_timestamp) as t_date , from_address , tx_id , SPLIT(from_asset, '-')[0] AS split, SPLIT(split, '.')[1] AS from_asset1 ,
to_asset ,FROM_AMOUNT, FROM_AMOUNT_USD , TO_AMOUNT_USD,TO_AMOUNT,POOL_NAME as pool
from flipside_prod_db.thorchain.swaps
where blockchain = 'ETH'
and block_timestamp::date >= '2022-01-01'
UNION
select (block_timestamp) as t_date , from_address , tx_id , SPLIT(from_asset, '.')[0] AS split, SPLIT(from_asset, '.')[1] AS from_asset1 ,
to_asset ,FROM_AMOUNT, FROM_AMOUNT_USD , TO_AMOUNT_USD,TO_AMOUNT,POOL_NAME as pool
from flipside_prod_db.thorchain.swaps
where blockchain = 'ETH'
and block_timestamp::date >= '2022-01-01'
order by 1
)
,
tb1 as ( select from_address , from_asset1 , sum(TO_AMOUNT_USD) as thor_total
from thor
group by 1,2)
,
eth as (select (block_timestamp) as date ,tx_hash ,ORIGIN_FROM_ADDRESS, ORIGIN_to_ADDRESS , AMOUNT_IN , AMOUNT_OUT, POOL_NAME , AMOUNT_IN_USD , AMOUNT_OUT_USD, symbol_in , symbol_out
from ethereum.core.ez_dex_swaps
where ORIGIN_FROM_ADDRESS in ( select FROM_ADDRESS from tb1) and ORIGIN_FROM_ADDRESS = tx_to
and block_timestamp::date >= '2022-01-01' and symbol_out in ( select from_asset1 from tb1)
)
,
tb2 as ( select ORIGIN_FROM_ADDRESS , symbol_out ,count(DISTINCT(tx_hash)) as count_tx, sum(AMOUNT_OUT_USD) as eth_total
from eth
group by 1,2)
,
final as ( select from_address , ORIGIN_FROM_ADDRESS , from_asset1 , symbol_out ,count_tx, thor_total , eth_total , eth_total-thor_total as gain , thor_total+eth_total as total_volume
from tb1 a join tb2 b on a.from_address = b.ORIGIN_FROM_ADDRESS
and a.from_asset1 = b.symbol_out
)
,
final1 as (select from_address , symbol_out , count_tx,thor_total , eth_total , gain , total_volume , (gain/eth_total)*100 as percetage
from final
Run a query to Download Data