alirsRun-05-top
Updated 2022-09-07
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
›
⌄
with base as(select iff(len(POOL_NAME)>10,(SUBSTRING(POOL_NAME, 0, charindex('-', POOL_NAME, 0)-1)),POOL_NAME)as Pool_Name,
sum(SWAP_VOLUME_RUNE_USD) as Swap_Volume_RUNE2,
row_number() over (order by Swap_Volume_RUNE2 desc) as rank
from flipside_prod_db.thorchain.daily_pool_stats
where SWAP_VOLUME_RUNE_USD>0 and SWAP_VOLUME_RUNE>0
group by 1
order by 2 desc
)
SELECT psday,pool from flipside_prod_db.thorchain.daily_pool_stats ps,base
where rank <= 10
-- day,
-- POOL_NAME,
-- iff(len(POOL_NAME)>10,(SUBSTRING(POOL_NAME, 0, charindex('-', POOL_NAME, 0)-1)),POOL_NAME)as Pool_Name,
-- sum(SWAP_VOLUME_RUNE_USD) as Swap_Volume_USD,
-- sum(SWAP_VOLUME_RUNE) as Swap_Volume_RUNE,
-- sum(SWAP_COUNT) as Number_of_Swap,
-- sum(RUNE_LIQUIDITY) as RUNE_LIQUIDITY,
-- sum(TO_RUNE_SWAP_COUNT) as Number_of_Swap_from_asset__to_RUNE,
-- sum(TO_ASSET_SWAP_COUNT) as Number_of_Swap_from_RUNE__to_Asset,
-- sum(TO_ASSET_SWAP_VOLUME) as Volume_of_Swap_from_RUNE_to_Asset,
-- sum(TO_RUNE_SWAP_VOLUME) as Volume_of_Swap_From_Asset_to_RUNE,
-- sum(TOTAL_SWAP_FEES_RUNE) as Total_swap_fees_in_RUNE,
-- sum(TOTAL_SWAP_FEES_USD) as Total_swap_fees_in_USD,
-- sum(UNIQUE_SWAPPER_COUNT) as Number_of_unique_Swapper,
-- avg(RUNE_PRICE_USD) as RUNE_PRICE_USD
-- from flipside_prod_db.thorchain.daily_pool_stats
-- where SWAP_VOLUME_RUNE_USD>0 and SWAP_VOLUME_RUNE>0 and pool_name in('BNB.BUSD','BTC.BTC','ETH.ETH','BNB.BNB','ETH.USDC')
-- GROUP by pool_name,day
-- order by Swap_Volume_RUNE DESC
-- limit 5
Run a query to Download Data