apostleoffinance2023-06-07 11:26 PM
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 min_dates as(
select
origin_from_address as addresses,
min(block_timestamp)::date as min_date,
date_trunc('month', min_date)::date as date
from ethereum.core.ez_dex_swaps
where platform like '%uniswap%'
AND block_timestamp >= current_date - 360
--and block_timestamp::date >= '2022-05-01'
--AND token_in = '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce' --Shiba Inu )
AND token_in = '0xcf0c122c6b73ff809c693db761e7baebe62b6a2e' --Floki
group by addresses
),
metrics as(
SELECT
date_trunc('month', block_timestamp)::date as date,
count(DISTINCT origin_from_address) as users,
count(DISTINCT tx_hash) as transactions,
count(DISTINCT origin_from_address || origin_to_address) as active_users,
sum(amount_in_usd) as volume,
avg(amount_in_usd) as avg_price
from ethereum.core.ez_dex_swaps
where platform like '%uniswap%'
group by date
)
SELECT
count(DISTINCT addresses) as new_users,
date,
users, transactions, active_users, volume, avg_price,
sum(new_users)over(order by date) as total_new_users,
sum(users)over(order by date) as total_users
from min_dates
join metrics using(date)
where date >= current_date - 360
Run a query to Download Data