Dune. День 2: Как узнать общий объем торгов и кол-во свопов для пары на Uniswap v2?

Материал подготовлен каналом @shitcoinresearch

Задача: Необходимо узнать кол-во свопов, которое было совершено в паре USDC/WETH на Uniswap v2 в разбивке по дням. Делаем запрос:

--0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc - Адрес пары USDC/WETH

select
      date_trunc ('day', evt_block_time) as day,
      count (*)
from uniswap_v2_ethereum.Pair_evt_Swap
where contract_address = 0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc
group by 1

Стоить отметить, что здесь адрес пары был внесен вручную. Его можно посмотреть либо на Etherscan, либо в UI самого Uniswap v2. Но также, можно сделать запрос в Dune по поиску контракта пары, содержащей USDC и WETH:

--0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48  - адрес контракта USDC
--0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  - адрес контракта WETH

select p.pair as Contract_address_USDC_WETH
from uniswap_v2_ethereum.Factory_evt_PairCreated p
where token0 in (0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48, 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2)
and token1 in (0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48, 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2)

При этом, я решил, улучшить запрос, сделав в нем подзапрос:

--0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48 USDC
--0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2 WETH

select date_trunc ('day', evt_block_time) as day,
       count (*)
from uniswap_v2_ethereum.Pair_evt_Swap
where contract_address = (select p.pair as Contract_address_USDC_WETH
                          from uniswap_v2_ethereum.Factory_evt_PairCreated p
                          where token0 in (0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48, 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2)
                          and token1 in (0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48, 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2))
group by 1
order by day desc

Получаем следующий результат:

Визуализируем полученные данные:

Теперь необходимо узнать кол-во свопов за неделю.
Создадим запрос через оператор WITH:

--0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48 USDC
--0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2 WETH

with 
    frequency as (
select date_trunc ('week', evt_block_time) as time,
       count (*)
from uniswap_v2_ethereum.Pair_evt_Swap
where contract_address = (select p.pair as Contract_address_USDC_WETH
                          from uniswap_v2_ethereum.Factory_evt_PairCreated p
                          where token0 in (0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48, 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2)
                          and token1 in (0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48, 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2))
group by 1
order by time desc)

select * from frequency

Визуализируем полученный результат:

Теперь определим объем торгов пары, содержащей USDC и WETH, выраженный в долларах. Для этого делаем следующий запрос:

--0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48 :USDC
--0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2 :WETH
--0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc :pair (USDC,WETH)

with
    frequency as (
        select 
            date_trunc ('week', evt_block_time) as time,
            count (*)
        from uniswap_v2_ethereum.Pair_evt_Swap
        where contract_address = 0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc
        group by 1
    ),
    
    volume as (
        with
            base as (
                   select 
            date_trunc ('minute', s.evt_block_time) as time,
            case when cast(amount0Out as double) = 0 then p.token1 else p.token0 end as token_bought_address,
            case when cast(amount0Out as double) = 0 then cast(amount1Out as double) else cast(amount0Out as double) end as token_bought_amount_raw
            from uniswap_v2_ethereum.Pair_evt_Swap s
            left join uniswap_v2_ethereum.Factory_evt_PairCreated p 
                   on s.contract_address = p.pair
            where s.contract_address = 0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc
            )

        select date_trunc ('week', b.time) as time,
               sum (token_bought_amount_raw / pow(10, coalesce (t.decimals,18)) * price) as total_usd_amount
        from base b
        left join tokens.erc20 t
               on t.contract_address = b.token_bought_address
        left join prices.usd p
               on p.minute = b.time
              and p.blockchain = 'ethereum'
              and p.contract_address = b.token_bought_address
         group by 1
    )
    
select * from volume

Визуализируем полученный запрос:

Запрос выше, можно упростить, если воспользоваться БД "dex_traders":

--0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48 USDC
--0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2 WETH
--0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc :pair (USDC,WETH)

with
    frequency as (
        select 
            date_trunc ('week', evt_block_time) as time,
            count (*) as num_swaps
        from uniswap_v2_ethereum.Pair_evt_Swap
        where contract_address = 0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc
        group by 1
    ),

dex_traders as (
        select 
            date_trunc ('week', block_date) as time,
            sum (amount_usd) as total_usd_amount_dx
        from dex.trades
        where blockchain = 'ethereum'
          and project_contract_address = 0xb4e16d0168e52d35cacd2c6185b44281ec28c9dc
        group by 1
)

select 
    dt.time,
    f.num_swaps,
    dt.total_usd_amount_dx
from dex_traders dt
left join frequency f
       on f.time = dt.time

Визуализируем результаты данного запроса, предварительно настроив отображение осей и подписей:

Домашнее задание: определить пару, содержащую WETH, с наибольшим объёмом торгов в долларах США. Делаем следующий запрос:

--0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2 WETH

select 
     d.token_pair, 
     d.project_contract_address as contract_address,
     sum (d.amount_usd) as total_usd_amount
from dex.trades d
where (token_sold_address = 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2
   or token_bought_address = 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2)
  and blockchain = 'ethereum'
  and project = 'uniswap'
  and version = '2'
  group by 1,2
order by total_usd_amount desc
limit 5

В результате, получаем следующие данные:

Визуализирую полученные данные в виде круговой диаграммы для наглядности:

Таким образом, пара, содержащая WETH, с наибольшим долларов объёмом торгов на Uniswap v2, является USDC-WETH. Объем торгов которой за все время составил $43,6 млрд.

Материал подготовлен каналом @shitcoinresearch