drone-mostafaeth dist
Updated 2023-05-13
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
base as (
SELECT 'Ethereum' as Chain,
ORIGIN_FROM_ADDRESS, min (BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP
FROM ethereum.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%'
GROUP by 1,2
UNION
SELECT 'Polygon' as Chain,
ORIGIN_FROM_ADDRESS, min (BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP
FROM polygon.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%'
GROUP by 1,2
UNION
SELECT 'Optimism' as Chain,
ORIGIN_FROM_ADDRESS, min (BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP
FROM optimism.core.ez_dex_swaps
WHERE PLATFORM LIKE '%uniswap%'
GROUP by 1,2),
final as (
SELECT case when AMOUNT_IN_USD > 0 then AMOUNT_IN_USD else AMOUNT_OUT_USD end as Volume,BLOCK_TIMESTAMP,ORIGIN_FROM_ADDRESS FROM ethereum.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%' UNION
SELECT case when AMOUNT_IN_USD > 0 then AMOUNT_IN_USD else AMOUNT_OUT_USD end as Volume,BLOCK_TIMESTAMP,ORIGIN_FROM_ADDRESS FROM polygon.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%' UNION
SELECT case when AMOUNT_IN_USD > 0 then AMOUNT_IN_USD else AMOUNT_OUT_USD end as Volume,BLOCK_TIMESTAMP,ORIGIN_FROM_ADDRESS FROM optimism.core.ez_dex_swaps WHERE PLATFORM LIKE '%uniswap%'
)
SELECT date_trunc ('month',t1.BLOCK_TIMESTAMP) as date, count (DISTINCT t1.ORIGIN_FROM_ADDRESS) as New_Users, sum (volume) as Volumes, sum (New_Users) over (order by date) as Cum_Users, sum (Volumes) over (order by date) as Cum_Volume,Median (volume) as AVG_Volumes,
CASE
when volume < 10 then 'Less than 10 USD'
when volume BETWEEN 10 AND 50 then 'Between 10 and 50 USD'
when volume BETWEEN 50 AND 100 then 'Between 50 and 100 USD'
when volume BETWEEN 100 AND 500 then 'Between 100 and 500 USD'