binhachonStargate User Growth - Remaining
    Updated 2022-04-24
    with transactions as (
    select
    from_address,
    contract_address,
    sum(raw_amount) as total_amount
    from ethereum_core.fact_token_transfers
    where contract_address in ('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xdac17f958d2ee523a2206206994597c13d831ec7') --USDC/USDT
    and to_address in ('0xdf0770df86a8034b3efef0a1bb3c889b8332ff56', '0x38ea452219524bb87e18de1c24d3bb59510bd783') --Stargate USDC/USDT
    group by 1, 2
    union all
    select
    to_address,
    contract_address,
    -sum(raw_amount) as total_amount
    from ethereum_core.fact_token_transfers
    where contract_address in ('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xdac17f958d2ee523a2206206994597c13d831ec7') --USDC/USDT
    and from_address in ('0xdf0770df86a8034b3efef0a1bb3c889b8332ff56', '0x38ea452219524bb87e18de1c24d3bb59510bd783') --Stargate USDC/USDT
    group by 1, 2
    ),
    user_stats as (
    select
    from_address,
    contract_address,
    sum(total_amount) as total_amount
    from transactions
    group by 1, 2
    )
    select
    'Total users' as symbol,
    count(*) as number_of_users
    from user_stats
    union all
    select
    'Current users' as symbol,
    count_if(total_amount > 0) as number_of_users
    from user_stats
    Run a query to Download Data