binhachon13. [Easy] Combinations - Total - Volume - Top 10
Updated 2021-10-31
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
›
⌄
-----------------------------Find if they're type 1 or 2---------------------------
with swap_type as (
select tx_id, count(tx_id) as num_tx_id from thorchain.swaps
group by tx_id
),
-----------------------------Give out score to each of the asset-------------------
order_list_1 as (
select asset, row_number() over (order by asset) as rownumer from (
select distinct from_asset as asset from thorchain.swaps
union
select distinct to_asset as asset from thorchain.swaps
)
),
order_list_2 as (
select asset, row_number() over (order by asset) as rownumer from (
select distinct from_asset as asset from thorchain.swaps
union
select distinct to_asset as asset from thorchain.swaps
)
),
------------------------------Select type 1 swap and put score on them-------------------------------------------------------------------
swap_action_1 as(
select from_asset, order_list_1.rownumer as from_asset_row_number, to_asset, order_list_2.rownumer as to_asset_row_number, from_amount_usd as amount_usd
from thorchain.swaps left join order_list_1 on from_asset = order_list_1.asset
left join order_list_2 on to_asset = order_list_2.asset
where tx_id in (select tx_id from swap_type where num_tx_id = 1)
),
----------------------------Sort them according to my rule------------------------------------------------------------------------------
swap_action_with_order_1 as(
select case when from_asset_row_number >= to_asset_row_number then to_asset else from_asset end as asset_1,
case when from_asset_row_number >= to_asset_row_number then from_asset else to_asset end as asset_2,
amount_usd
from swap_action_1
),
----------------------------Remove unnecessary string and only keep the "blockchain" + "asset name" from the string--------------------
final_table_1 as(
Run a query to Download Data