hessDaily Add stOSMO / OSMO
    Updated 2022-12-08
    -- 11/11/22 Pool 833 - stOSMO / OSMO
    with price as ( select RECORDED_AT::date as date, avg(price) as avg_price
    from osmosis.core.dim_prices
    where RECORDED_AT >= '2022-10-20'
    and symbol ilike '%osmo%'
    group by 1)
    ,
    pool as ( select date(block_timestamp) as date, currency , case when date < '2022-11-11' then 'Before program' else 'After Program'
    end as type, tx_id, LIQUIDITY_PROVIDER_ADDRESS ,amount/pow(10,decimal) as amounts
    from osmosis.core.fact_liquidity_provider_actions
    where action = 'pool_joined' and tx_status = 'SUCCEEDED' and pool_id[0] = '833' and date >= '2022-10-20')

    select a.date, type , count(DISTINCT(tx_id)) as total_tx, count(DISTINCT(LIQUIDITY_PROVIDER_ADDRESS)) as total_address,
    sum(amounts*avg_price) as amount_usd, avg(amounts*avg_price) as avg_price
    from pool a left outer join price b on a.date = b.date
    group by 1,2

    Run a query to Download Data