alirsRun-05-top
    Updated 2022-09-07
    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