hbd1994Daily Review of "USDC-WETH 100 1" - 1
Updated 2023-05-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
›
⌄
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
DATE,
SWAP_TYPE,
COUNT(*) AS SWAPS_COUNT,
SUM(SWAPS_COUNT) OVER (PARTITION BY SWAP_TYPE ORDER BY DATE) AS CUMULATIVE_SWAPS_COUNT,
COUNT(DISTINCT FROM_ADDRESS) AS SWAPPER,
SUM(AMOUNT) AS USDC_VOLUME,
SUM(USDC_VOLUME) OVER (PARTITION BY SWAP_TYPE ORDER BY DATE) AS CUMULATIVE_USDC_VOLUME
from main
GROUP BY 1 , 2
ORDER BY 1 , 2
Run a query to Download Data