Arioliquidity pool on osmosis - pool seasy/osmo
    Updated 2022-10-27
    with Price as (
    select
    RECORDED_AT::date as date,
    address,
    avg(PRICE) as AVG_Price
    from osmosis.core.dim_prices a join osmosis.core.dim_labels b on project_name = symbol
    group by 1,2
    ),
    balances as (
    select
    a.date,
    a.ADDRESS,
    BALANCE/pow(10,decimal) * AVG_Price as Balance_USD,
    CASE
    when Balance_USD < 50 then 'Tier 1: 0-50$'
    when Balance_USD >= 50 and Balance_USD < 500 then 'Tier 2: 50-500$'
    when Balance_USD >= 500 and Balance_USD < 5000 then 'Tier 3: 500-5K$'
    when Balance_USD >= 5000 and Balance_USD < 50000 then 'Tier 4: 5K-50K$'
    else 'Tier 5: Whale'
    end as status
    from osmosis.core.fact_daily_balances a join Price b on a.CURRENCY = b.address and a.date = b.date
    where a.DATE >= current_date - 1
    and BALANCE_TYPE = 'liquid'
    and decimal is not NULL
    and CURRENCY is not null
    ),
    LP_Volume as (
    select
    BLOCK_TIMESTAMP,
    tx_id,
    ACTION,
    POOL_ID,
    LIQUIDITY_PROVIDER_ADDRESS,
    CASE
    when ACTION = 'pool_joined' then AMOUNT/pow(10,decimal) * AVG_Price
    when action = 'pool_exited' then -1 * AMOUNT/pow(10,decimal) * AVG_Price
    Run a query to Download Data