wollok-ethStablecoin Transfers 2023/24
    Updated 2024-08-29
    -- Stablecoin Transfers 2023/24
    WITH stables AS (
    SELECT
    block_timestamp,
    tx_hash,
    from_address,
    to_address,
    contract_address, -- usdc, usdt, weth
    symbol,
    amount
    FROM ethereum.core.ez_token_transfers
    WHERE
    block_timestamp::date >= '2023-01-01'
    AND contract_address IN (
    '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' -- USDC
    ,'0xdac17f958d2ee523a2206206994597c13d831ec7' -- USDT
    ,lower('0x6B175474E89094C44Da98b954EedeAC495271d0F') -- DAI
    --,lower('0x0000000000085d4780B73119b644AE5ecd22b376') -- TUSD
    )
    )

    SELECT
    date_trunc('month', block_timestamp) AS month,
    --contract_address,
    symbol,
    COUNT(*) AS transfer_count,
    SUM(amount) AS amount,
    TO_CHAR(ROUND(SUM(amount), 2), 'FM999,999,999,990.00') AS amount_formatted,
    ROUND(SUM(amount) / 1e6, 2) AS amount_in_millions,
    ROUND(SUM(amount) / 1e9, 2) AS amount_in_billions,
    ROUND(SUM(amount) / transfer_count, 2) AS avg_amount_per_txn
    FROM stables
    GROUP BY month, symbol
    ORDER BY month ASC, symbol ASC;
    QueryRunArchived: QueryRun has been archived