kiacryptoLP metrics 2
    Updated 2022-10-13
    with join_to as (
    select
    date_trunc('day', block_timestamp) as date,
    count(distinct tx_id) as tx_count_in,
    count(distinct liquidity_provider_address) as unique_user_in,
    sum(amount/1e10) as tx_volume_in,

    sum(tx_count_in) over (order by date) as cum_tx_count_in,
    sum(tx_volume_in) over (order by date) as cum_tx_volume_in
    from osmosis.core.fact_liquidity_provider_actions left join osmosis.core.dim_labels on currency = address
    where
    block_timestamp::date >= '2022-09-26' and
    currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7' and
    action = 'pool_joined' and
    tx_status = 'SUCCEEDED' and
    project_name = 'DOT.axl' and
    pool_id = 773
    group by 1
    ),
    exit_from as (
    select
    date_trunc('day', block_timestamp) as date,
    -count(distinct tx_id) as tx_count_out,
    -count(distinct liquidity_provider_address) as unique_user_out,
    -sum(amount/1e10) as tx_volume_out,

    sum(tx_count_out) over (order by date) as cum_tx_count_out,
    sum(tx_volume_out) over (order by date) as cum_tx_volume_out
    from osmosis.core.fact_liquidity_provider_actions left join osmosis.core.dim_labels on currency = address
    where
    block_timestamp::date >= '2022-09-26' and
    currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7' and
    action = 'pool_exited' and
    tx_status = 'SUCCEEDED' and
    project_name = 'DOT.axl' and
    pool_id = 773
    Run a query to Download Data