jackguyUp The Mountain 7
    Updated 2023-09-04
    WITH tab1 as (
    SELECT
    to_address,
    sum(raw_amount / power(10, 8)) as in_vol
    FROM avalanche.core.fact_token_transfers
    WHERE contract_address = lower('0x152b9d0FdC40C096757F570A51E494bd4b943E50')
    GROUP BY 1
    ), tab2 as (
    SELECT
    from_address,
    sum(raw_amount / power(10, 8)) as out_vol
    FROM avalanche.core.fact_token_transfers
    WHERE contract_address = lower('0x152b9d0FdC40C096757F570A51E494bd4b943E50')
    GROUP BY 1
    )

    SELECT
    count(*) as users,
    avg(balance) as avg_BTC_balance,
    median(balance) as median_balanace

    from (
    SELECT
    to_address,
    sum(in_vol - CASE when out_vol is NULL THEN 0 ELSE out_vol END) as balance
    FROM tab1
    left outer join tab2
    on to_address = from_address
    GROUP BY 1
    ORDER BY 2 DESC
    -- LIMIT 20
    )
    WHERE not to_address LIKE '0x0000000000000000000000000000000000000000'
    AND balance > 0
    --GROUP BY 1
    Run a query to Download Data