Afonso_Diaz2023-10-18 06:59 PM
Updated 2023-10-18
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
35
36
›
⌄
WITH POOLS AS (
SELECT *
FROM (
SELECT
POOL_ADDRESS,
TOKEN0_BALANCE_USD + TOKEN1_BALANCE_USD AS BALANCE_USD
FROM ethereum.uniswapv3.ez_pool_stats
QUALIFY ROW_NUMBER() OVER (PARTITION BY POOL_ADDRESS ORDER BY BLOCK_TIMESTAMP DESC) = 1
)
ORDER BY BALANCE_USD LIMIT {{ number_of_pools }}
),
T2 AS (
SELECT
DATE_TRUNC('MONTH', MIN_TIME) AS MONTH,
COUNT(DISTINCT LIQUIDITY_PROVIDER) AS NEW_PROVIDERS
FROM (
SELECT
LIQUIDITY_PROVIDER,
MIN(BLOCK_TIMESTAMP) AS MIN_TIME
FROM ethereum.uniswapv3.ez_lp_actions
WHERE POOL_ADDRESS IN (SELECT DISTINCT POOL_ADDRESS FROM POOLS)
GROUP BY 1
)
GROUP BY 1
),
T AS (
SELECT
DATE_TRUNC('MONTH', BLOCK_TIMESTAMP) AS MONTH,
SUM(IFF(action = 'DECREASE_LIQUIDITY', NVL(AMOUNT0_USD, 0) + NVL(AMOUNT1_USD, 0), 0)) AS OUTPUTLIQUIDITY,
COUNT(DISTINCT TX_HASH) AS ACTIONS,
COUNT(DISTINCT LIQUIDITY_PROVIDER) AS LIQUIDITY_PROVIDERS,
SUM(ACTIONS) OVER (ORDER BY MONTH) AS CUMULATIVE_ACTIONS
FROM
ethereum.uniswapv3.ez_lp_actions
Run a query to Download Data