mohammadhCosmoverse Free Play 1.5 ) sell
    Updated 2022-10-12
    with A as (
    select
    avg(PRICE) as price,
    date_trunc('day',RECORDED_AT) as block_day
    from osmosis.core.dim_prices
    where SYMBOL='SCRT'
    and (block_day >= '2022-10-01' and block_day < current_date -1)
    group by block_day),
    B as (select
    date_trunc('day',block_timestamp) as block_day,
    count(distinct TX_ID) as tx_count,
    count(distinct TRADER) as unique_user,
    sum(FROM_AMOUNT/1e6) as volume,
    PROJECT_NAME
    from osmosis.core.fact_swaps a
    join osmosis.core.dim_labels b
    on a.TO_CURRENCY = b.ADDRESS
    where (block_timestamp >= '2022-10-01' and block_timestamp < current_date -1)
    and FROM_CURRENCY ='ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A'
    and TX_STATUS ='SUCCEEDED'
    group by block_day,PROJECT_NAME)

    select
    sum(unique_user) as sum_user,
    sum(volume) as sum_volume,
    PROJECT_NAME,
    sum(tx_count) as sum_tx,
    sum(volume*price) as sum_usd_volume
    from A join B on A.block_day=B.block_day
    group by PROJECT_NAME