MLDZMNswp3
Updated 2023-04-03
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 tb5 as (
SELECT
trunc(timestamp,'day') as day,
TOKEN_CONTRACT,
avg(price_usd) as price_token
from near.core.fact_prices
group by 1,2
),
t1 as (select
*,
AMOUNT_IN*a.price_token as volume_usd_in,
AMOUNT_OUT*b.price_token as volume_usd_out
from near.core.ez_dex_swaps s
left join tb5 a on s.TOKEN_IN_CONTRACT=a.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=a.day
left join tb5 b on s.TOKEN_OUT_CONTRACT=b.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=b.day
where BLOCK_TIMESTAMP>='2023-01-01'
and volume_usd_out >=20000
and BLOCK_TIMESTAMP>='2023-01-01'
and volume_usd_out<1e6
)
select
BLOCK_TIMESTAMP::date as date,
count(distinct trader) as no_whales,
count(distinct tx_hash) as no_swaps,
sum(AMOUNT_OUT*b.price_token) as total_swap_volume,
avg(AMOUNT_OUT*b.price_token) as avg_swap_volume,
sum(total_swap_volume) over (order by date) as total_volume
from near.core.ez_dex_swaps s
left join tb5 a on s.TOKEN_IN_CONTRACT=a.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=a.day
left join tb5 b on s.TOKEN_OUT_CONTRACT=b.TOKEN_CONTRACT and s.BLOCK_TIMESTAMP::date=b.day
where BLOCK_TIMESTAMP>='2023-01-01'
and tx_hash in (select tx_hash from t1)
Run a query to Download Data