Moe2p773
    Updated 2022-12-13
    with prices as (
    select
    RECORDED_AT::date as days , SYMBOL,
    avg(price) as prices
    from
    osmosis.core.dim_prices
    where provider = 'coinmarketcap'
    group by 1 ,2
    )

    ,base as (
    select
    l.*, PROJECT_NAME as LABEL
    from
    osmosis.core.fact_liquidity_provider_actions l , osmosis.core.dim_labels b
    where CURRENCY = b.ADDRESS
    )

    ,fin as (
    select
    b.*,prices,AMOUNT/pow(10,decimal) as AMOUNTs ,
    AMOUNTs*prices as AMOUNT_usd
    from
    base b,prices p
    where
    BLOCK_TIMESTAMP::date = days and LABEL = SYMBOL
    and AMOUNT is not null
    )

    select
    date_trunc(week,BLOCK_TIMESTAMP)::date as week,
    count(case when action = 'pool_joined' then LIQUIDITY_PROVIDER_ADDRESS end ) as depositors,
    Run a query to Download Data