Eman-RazTotal Stats: spec swap data
    Updated 2025-04-28
    with overview as (select block_timestamp, tx_hash, origin_from_address as swapper, symbol_in || '➡' || symbol_out as token_pair, case
    when token_in = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389') then sum(amount_in)
    when token_out = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389') then sum(amount_out)
    end as volume, sum(coalesce(amount_in_usd,amount_out_usd)) as volume_usd, platform as dex, case
    when token_in = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389') then 'Sell'
    when token_out = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389') then 'Buy'
    end as swap_direction, 'Ethereum' as "Chain"
    from ethereum.defi.ez_dex_swaps
    where (token_in = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389')
    or token_out = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389'))
    and block_timestamp::date between '{{Start_Date}}' and '{{End_Date}}'
    group by block_timestamp, token_in, token_out, tx_hash, origin_from_address, symbol_in, symbol_out, platform

    union all

    select block_timestamp, tx_hash, origin_from_address as swapper, symbol_in || '➡' || symbol_out as token_pair, case
    when token_in = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431') then sum(amount_in)
    when token_out = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431') then sum(amount_out)
    end as volume, sum(coalesce(amount_in_usd,amount_out_usd)) as volume_usd, platform as dex, case
    when token_in = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431') then 'Sell'
    when token_out = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431') then 'Buy'
    end as swap_direction, 'Base' as "Chain"
    from base.defi.ez_dex_swaps
    where (token_in = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431')
    or token_out = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431'))
    and block_timestamp::date between '{{Start_Date}}' and '{{End_Date}}'
    group by block_timestamp, token_in, token_out, tx_hash, origin_from_address, symbol_in, symbol_out, platform)

    select count(distinct tx_hash) as "Swap Count", sum(volume) as "Swap Volume ($SPEC)",
    sum(volume_usd) as "Swap Volume ($USD)", count(distinct swapper) as "Swapper Count"
    from overview

    Last run: about 1 month ago
    Swap Count
    Swap Volume ($SPEC)
    Swap Volume ($USD)
    Swapper Count
    1
    56749360006078.8759295576788086.753172
    1
    45B
    61s