hbd1994LP Actions | Query Number 3
Updated 2023-05-02
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
›
⌄
with main as (
select
date(a.BLOCK_TIMESTAMP) as DATE,
a.ACTION,
a.TX_HASH,
a.LIQUIDITY_PROVIDER,
a.AMOUNT0_ADJUSTED,
case
when a.ACTION = 'DECREASE_LIQUIDITY' then (a.AMOUNT0_ADJUSTED * -1)
when a.ACTION = 'INCREASE_LIQUIDITY' then (a.AMOUNT0_ADJUSTED) end as AMOUNT
from
ethereum.uniswapv3.ez_lp_actions a
where
a.POOL_NAME in ( 'USDC-WETH 100 1' )
and a.BLOCK_TIMESTAMP::date >= CURRENT_DATE - {{Date_Interval}} )
select
DATE,
ACTION,
zeroifnull(COUNT(*)) AS LPS_COUNT,
zeroifnull(SUM(LPS_COUNT) OVER (PARTITION BY ACTION ORDER BY DATE)) AS CUMULATIVE_LPS_COUNT,
zeroifnull(COUNT(DISTINCT LIQUIDITY_PROVIDER)) AS LPERs,
zeroifnull(SUM(AMOUNT0_ADJUSTED)) AS USDC_VOLUME,
zeroifnull(SUM(USDC_VOLUME) OVER (PARTITION BY ACTION ORDER BY DATE)) AS CUMULATIVE_USDC_VOLUME
from main
group by 1 , 2
order by 1 , 2
Run a query to Download Data