rajsOsmosis Transfers Receivers
    Updated 2022-10-17
    with reqd_tab as
    (
    SELECT
    week,
    count(distinct user) as no_of_active_users
    FROM
    (
    SELECT
    -- *
    date_trunc('week', block_timestamp) as week,
    receiver as user,
    count(distinct block_timestamp::date) as no_of_active_days
    from osmosis.core.fact_transfers
    -- where block_timestamp >= '2022-01-01'
    group by 1,2
    having count(distinct block_timestamp::date) >= 4
    -- limit 1
    )
    group by 1
    order by 1
    )

    SELECT
    *,
    avg(no_of_active_users) over (order by week rows between 7 preceding and 1 preceding) as "prior_7_days_no_of_active_users"
    -- min(no_of_active_users),
    -- max(no_of_active_users),
    -- avg(no_of_active_users),
    -- median(no_of_active_users)
    from reqd_tab
    Run a query to Download Data