mz0111Near 2
Updated 2023-09-11
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 tab1 as (SELECT
TIMESTAMP:: DATE AS Date,
TOKEN_CONTRACT,
avg(PRICE_USD) as avg_price
from near.price.fact_prices
group by 1 , 2
)
SELECT
'Swap In' as Name,
date_trunc('WEEK', BLOCK_TIMESTAMP) as Week,
count(distinct TX_HASH) as Swaps,
count(distinct TRADER) as Swapper,
Swaps/Swapper as Swap_per_Swapper,
sum(AMOUNT_IN * avg_price) Volume,
Volume/Swaps as Volume_per_Swap
from near.defi.ez_dex_swaps join tab1
on date = date_trunc('day', BLOCK_TIMESTAMP)
and TOKEN_CONTRACT = TOKEN_IN_CONTRACT
and token_in_contract not in('dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near'
,'2260fac5e5542a773aa44fbcfedf7c193bc2c599.factory.bridge.near'
,'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near')
where BLOCK_TIMESTAMP :: date >= current_date - {{Period}}
group by 1 , 2
union all
SELECT
'Swap Out' as Name,
date_trunc('WEEK', BLOCK_TIMESTAMP) as Week,
count(distinct TX_HASH) as Swaps,
count(distinct TRADER) as Swapper,
Swaps/Swapper as Swap_per_Swapper,
Run a query to Download Data