hbd1994Distribution of Swaps Volume
    Updated 2023-05-01
    with main as (
    select
    date(a.BLOCK_TIMESTAMP) as DATE,
    case
    when a.AMOUNT0_ADJUSTED < '0' then 'Swap For USDC'
    when a.AMOUNT0_ADJUSTED >= '0' then 'Swap From USDC' end as SWAP_TYPE,
    a.TX_HASH,
    b.FROM_ADDRESS,
    a.AMOUNT0_ADJUSTED,
    case
    when a.AMOUNT0_ADJUSTED < '0' then (a.AMOUNT0_ADJUSTED * -1)
    when a.AMOUNT0_ADJUSTED >= '0' then (a.AMOUNT0_ADJUSTED) end as AMOUNT
    from
    ethereum.uniswapv3.ez_swaps a
    left join
    ethereum.core.fact_transactions b
    on
    b.TX_HASH = a.TX_HASH
    where
    a.POOL_NAME in ( 'USDC-WETH 100 1' )
    and a.BLOCK_TIMESTAMP::date >= CURRENT_DATE - {{Date_Interval}} )

    select
    case
    when AMOUNT BETWEEN '0' AND '10' THEN '0 - 10 USDC'
    when AMOUNT BETWEEN '10' AND '100' THEN '10 - 100 USDC'
    when AMOUNT BETWEEN '100' AND '1000' THEN '100 - 1000 USDC'
    when AMOUNT BETWEEN '1000' AND '10000' THEN '1000 - 10000 USDC'
    when AMOUNT >= '10000' THEN '>= 10000 USDC'
    END AS VOLUME_LEVEL,
    SWAP_TYPE,
    COUNT(*) AS SWAPS_COUNT,
    COUNT(DISTINCT FROM_ADDRESS) AS SWAPPERS_COUNT,
    SUM(AMOUNT) AS TOTAL_USDC_VOLUME
    from main
    GROUP BY 1 , 2
    Run a query to Download Data