MoDeFiWC R32 - USDC 1
    Updated 2022-11-23
    ----------------------------------------Ethereum----------------------------------------1
    with ethereum_usdc_transfers as
    (select BLOCK_TIMESTAMP, 'Ethereun' as chain, FROM_ADDRESS as sender, TO_ADDRESS as receiver, AMOUNT, tx_hash, b.ADDRESS_NAME as sender_name, c.ADDRESS_NAME as receiver_name
    from ethereum.core.ez_token_transfers
    left join ethereum.core.dim_labels b
    on FROM_ADDRESS=b.address
    left join ethereum.core.dim_labels c
    on TO_ADDRESS=c.address
    where CONTRACT_ADDRESS='0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' --USDC
    and BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}),
    ethereum_data as (
    select date_trunc({{Time_Interval}},BLOCK_TIMESTAMP) as date, chain, count(*) as transfers, sum(AMOUNT) as volume, avg(amount) as avg_transfer_vol,
    count(distinct sender) as senders, count(distinct receiver) as receivers,
    sum(transfers) over(partition by chain order by date) as total_transfers,
    sum(volume) over(partition by chain order by date) as total_volume
    from ethereum_usdc_transfers
    group by 1,2),
    ----------------------------------------Optimism----------------------------------------2
    optimism_usdc_transfers as
    (select BLOCK_TIMESTAMP, 'Optimism' as chain, FROM_ADDRESS as sender, TO_ADDRESS as receiver, RAW_AMOUNT/pow(10,6) as AMOUNT, tx_hash, b.ADDRESS_NAME as sender_name, c.ADDRESS_NAME as receiver_name
    from optimism.core.fact_token_transfers
    left join optimism.core.dim_labels b
    on FROM_ADDRESS=b.address
    left join optimism.core.dim_labels c
    on TO_ADDRESS=c.address
    where CONTRACT_ADDRESS='0x7f5c764cbc14f9669b88837ca1490cca17c31607' --USDC
    and BLOCK_TIMESTAMP::date>=CURRENT_DATE-{{Days_Before}}),
    optimism_data as (
    select date_trunc({{Time_Interval}},BLOCK_TIMESTAMP) as date, chain, count(*) as transfers, sum(AMOUNT) as volume, avg(amount) as avg_transfer_vol,
    count(distinct sender) as senders, count(distinct receiver) as receivers,
    sum(transfers) over(partition by chain order by date) as total_transfers,
    sum(volume) over(partition by chain order by date) as total_volume
    from optimism_usdc_transfers
    Run a query to Download Data