RamaharwBTC On- and Off- Ramps
    Updated 2022-06-04
    with addBTC as (select
    DATE(block_timestamp) as dayz,
    liquidity_provider_address as addusers,
    sum(amount) / 1e8 as addamt
    from osmosis.core.fact_liquidity_provider_actions
    where currency = 'ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F'
    AND tx_status = 'SUCCEEDED' AND action = 'pool_joined'
    group by 1, 2),

    removeBTC as (select
    DATE(block_timestamp) as dt,
    liquidity_provider_address as removeusers,
    sum(amount) / 1e8 as removeamt
    from osmosis.core.fact_liquidity_provider_actions
    where currency = 'ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F'
    AND tx_status = 'SUCCEEDED' AND action = 'pool_exited'
    group by 1, 2),

    wBTCpos as (select
    dayz,
    addusers,
    (addamt - coalesce(removeamt, 0)) as Netamt,
    sum(Netamt) over (partition by addusers order by dayz asc rows between unbounded preceding and current row) as balance
    from addBTC a
    left join removeBTC ON dayz=dt AND addusers = removeusers
    order by dayz ASC )

    select *
    from wBTCpos
    where balance < 40

    -- ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F wBTC
    -- ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5 wETH
    Run a query to Download Data