flipside

    Daily active user LP activity
    peteerDaily active user LP activity
    Updated 2022-10-16
    with Daily_Active_User as (select count(*), tx_from from (select count(distinct block_timestamp::date), date_trunc('week', block_timestamp) , tx_from
    from osmosis.core.fact_transactions
    group by 2,3
    having count(distinct block_timestamp::date) > 4)
    group by 2
    having count(*) > 50 )
    , Daily_Active_User_transactions as (select count(*) as tcount , block_timestamp::date as tdate from osmosis.core.fact_transactions
    where tx_from in (select tx_from from Daily_Active_User)
    group by tdate)
    , Daily_Active_User_transactions_top10 as (select count(*) as tcount , tx_from from osmosis.core.fact_transactions
    where tx_from in (select tx_from from Daily_Active_User)
    group by tx_from
    order by tcount desc
    limit 10)
    , Daily_Active_User_lp as (select count(*) as lpcount , block_timestamp::date as lpdate from osmosis.core.fact_liquidity_provider_actions
    where LIQUIDITY_PROVIDER_ADDRESS in (select tx_from from Daily_Active_User)
    group by lpdate)
    , Daily_Active_User_lp_top10 as (select count(*) as lpcount , LIQUIDITY_PROVIDER_ADDRESS from osmosis.core.fact_liquidity_provider_actions
    where LIQUIDITY_PROVIDER_ADDRESS in (select tx_from from Daily_Active_User)
    group by LIQUIDITY_PROVIDER_ADDRESS
    order by lpcount desc
    limit 10)
    , Daily_Active_User_swaps as (select count(*) as scount , block_timestamp::date as sdate from osmosis.core.fact_swaps
    where TRADER in (select tx_from from Daily_Active_User)
    group by sdate)
    , Daily_Active_User_swaps_top10 as (select count(*) as scount , TRADER from osmosis.core.fact_swaps
    where TRADER in (select tx_from from Daily_Active_User)
    group by TRADER
    order by scount desc
    limit 10)
    , Daily_Active_User_transfer_to as (select count(*) as tocount , block_timestamp::date as todate from osmosis.core.fact_transfers
    where RECEIVER in (select tx_from from Daily_Active_User)
    group by todate)
    , Daily_Active_User_transfer_to_top10 as (select count(*) as tocount , RECEIVER from osmosis.core.fact_transfers
    where RECEIVER in (select tx_from from Daily_Active_User)
    group by RECEIVER
    Run a query to Download Data