binhachon14. [Hard] Pool Stats - raw chart
    Updated 2021-11-07
    with pool_stats as (
    select day,
    case when position('-', pool_name, 1) = 0 then pool_name else
    substr(pool_name, 1, position('-', pool_name, 1) - 1) end as pool_name,
    coalesce(rune_liquidity*2*rune_price_usd,0) as liquidity,
    coalesce(swap_volume_rune_usd,0) as swap_volume_rune_usd,
    coalesce(swap_count,0) as swap_count,
    coalesce(total_swap_fees_usd,0) as total_swap_fees_usd,
    coalesce(total_swap_fees_usd/swap_volume_rune_usd * 1000,0) as fee_per_1000_swapped
    from thorchain.daily_pool_stats
    ),
    pool_stats_with_rank as(
    select pool_name, max_liquidity, row_number() over (order by max_liquidity desc) as max_liquidity_row_number,
    swap_volume_rune_usd, row_number() over (order by swap_volume_rune_usd desc) as swap_volume_rune_usd_row_number,
    swap_count, row_number() over (order by swap_count desc) as swap_count_row_number,
    total_swap_fees_usd, row_number() over (order by total_swap_fees_usd desc) as total_swap_fees_usd_row_number
    from(
    select pool_name, max(liquidity) as max_liquidity, sum(swap_volume_rune_usd) as swap_volume_rune_usd, sum(swap_count) as swap_count, sum(total_swap_fees_usd) as total_swap_fees_usd from pool_stats
    group by pool_name
    )
    )
    --select * from pool_stats_with_rank --where (max_liquidity_row_number < 6 or swap_volume_rune_usd_row_number < 6 or swap_count_row_number < 6 or total_swap_fees_usd_row_number < 6)
    select * from pool_stats

    Run a query to Download Data