Elprognerd8 - top 10 Exiting providers with the most number of provides
    Updated 2022-12-14
    with main as (select
    date_trunc('day', block_timestamp) as date,
    ACTION,
    tx_id,
    liquidity_provider_address,
    CURRENCY,
    amount,
    decimal
    from osmosis.core.fact_liquidity_provider_actions
    where pool_id = [773] and action in ('pool_exited')
    )

    select
    liquidity_provider_address as "Liquidity Provider",
    count(distinct tx_id) as "Number of liquidity provides",
    sum(amount*price/pow(10,decimal)) as "Total Volume",
    avg(amount*price/pow(10,decimal)) as "Average Volume"
    from main x join (
    select date_trunc('day',block_timestamp) as date,
    from_currency as tkn,
    avg((to_amount / pow(10 , to_decimal)) / (from_amount / pow(10 , from_decimal))) as price
    from osmosis.core.fact_swaps
    where to_currency = 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858' and to_amount != 0 AND from_amount != 0
    and (from_currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7' or from_currency = 'uosmo')
    group by 1,2
    ) y
    on x.date = y.date and x.currency = y.tkn
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 10
    Run a query to Download Data