keshanLP Size Distribution
    Updated 2022-05-02
    with adding as (select pool_name, sum(rune_amount) as added_rune_amount, sum(rune_amount_usd) as added_rune_amount_usd,
    sum(asset_amount) as added_asset_amount, sum(asset_amount_usd) as added_asset_amount_usd
    from thorchain.liquidity_actions where lp_action = 'add_liquidity' group by pool_name),
    removing as (select pool_name, sum(rune_amount) as removed_rune_amount, sum(rune_amount_usd) as removed_rune_amount_usd,
    sum(asset_amount) as removed_asset_amount, sum(asset_amount_usd) as removed_asset_amount_usd
    from thorchain.liquidity_actions where lp_action = 'remove_liquidity' group by pool_name),
    ltv as (select a.pool_name, added_rune_amount, added_rune_amount_usd, added_asset_amount, added_asset_amount_usd, removed_rune_amount, removed_rune_amount_usd, removed_asset_amount, removed_asset_amount_usd,
    (added_rune_amount - removed_rune_amount) as net_rune, (added_rune_amount_usd - removed_rune_amount_usd) as net_rune_usd, (added_asset_amount - removed_asset_amount) as net_assets, (added_asset_amount_usd - removed_asset_amount_usd) as net_assets_usd,
    (net_rune + net_assets) net_ltv, (net_rune_usd + net_assets_usd) net_ltv_usd
    from adding a full outer join removing using(pool_name))

    select *,
    (case when net_ltv_usd <= 0 then '$0'
    when net_ltv_usd > 0 and net_ltv_usd <= 1e6 then '$0 - 1M'
    when net_ltv_usd > 1e6 and net_ltv_usd <= 5 * 1e6 then '$1M - 5M'
    when net_ltv_usd > 5 * 1e6 and net_ltv_usd <= 20 * 1e6 then '$5M - 20M'
    when net_ltv_usd > 20 * 1e6 and net_ltv_usd <= 50 * 1e6 then '$20M - 50M'
    when net_ltv_usd > 50 * 1e6 and net_ltv_usd <= 100 * 1e6 then '$50M - 100M'
    when net_ltv_usd > 100 * 1e6 then '$100M+'
    end) as dist
    from ltv
    Run a query to Download Data