nsa2000Distribution of SCRT Liquidity Providers By Their Total LP Volume
    Updated 2022-10-13
    with SCRTPricet as (
    select recorded_at::date as day,
    avg (price) as SCRTPrice
    from osmosis.core.dim_prices
    where symbol = 'SCRT'
    and recorded_at >= CURRENT_DATE - 7
    group by 1),

    maintable as (
    select liquidity_provider_address,
    action,
    count (distinct tx_id) as TX_Count,
    count (distinct liquidity_provider_address) as LPers_Count,
    sum ((amount/1e6)*SCRTPrice) as Total_USD_Volume,
    avg ((amount/1e6)*SCRTPrice) as Average_USD_Volume
    from osmosis.core.fact_liquidity_provider_actions t1 join SCRTpricet t2 on t1.block_timestamp::date = t2.day
    where currency = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A'
    and tx_status = 'SUCCEEDED'
    and action in ('pool_joined','pool_exited')
    and block_timestamp >= CURRENT_DATE - 7
    group by 1,2)

    select action,
    case when Total_USD_Volume < 10 Then 'Less Than $10'
    when Total_USD_Volume >= 10 and Total_USD_Volume < 100 then '$10 - $100'
    when Total_USD_Volume >= 100 and Total_USD_Volume < 1000 then '$100 - $1000'
    else 'More Than $1000' end as type,
    count (distinct liquidity_provider_address)
    from maintable
    group by 1,2
    order by 3 desc
    Run a query to Download Data