ThatGuyOsmo - Daily Active User 8. ibc from
    Updated 2022-10-19
    WITH
    active_users AS
    (
    SELECT
    DISTINCT active_users
    FROM (
    SELECT
    date_trunc('week', block_timestamp) AS date,
    tx_from AS active_users,
    COUNT(DISTINCT date_trunc('day', block_timestamp)) AS unique_week_days
    FROM osmosis.core.fact_transactions
    WHERE tx_status ilike 'SUCCEEDED'
    GROUP BY 1, 2
    )
    WHERE unique_week_days >= 4 -- four active days out of 7 days of a week (majority)
    )
    ,
    other_users as (
    SELECT
    DISTINCT tx_from as other_users
    FROM osmosis.core.fact_transactions
    WHERE tx_status ilike 'SUCCEEDED'
    AND tx_from NOT IN (SELECT active_users FROM active_users)
    )

    SELECT
    CASE WHEN STARTSWITH(LEFT(SENDER, POSITION('1' IN SENDER) - 1), '0x') THEN NULL ELSE INITCAP(LEFT(SENDER, POSITION('1' IN SENDER) - 1), ' ') END as "Sender Network",
    count(tx_id) as "Tx Count"
    FROM osmosis.core.fact_transfers
    WHERE RECEIVER IN (SELECT active_users FROM active_users)
    AND tx_status ilike 'SUCCEEDED'
    AND "Sender Network" IS NOT NULL
    GROUP BY 1
    HAVING "Tx Count" > 10000
    ORDER BY "Tx Count" DESC

    Run a query to Download Data