rajsTotal Swaps On Near
Updated 2022-11-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 prices as
(
SELECT
date_trunc('{{Granularity}}', timestamp) as date,
symbol,
avg(price_usd) as price
from near.core.fact_prices
-- where symbol = 'wNEAR'
group by 1,2
)
,
swaps AS
(
SELECT
-- date_trunc('day', block_timestamp) as date,
-- token_in as swapped_from,
sum(amount_in) as swapped_from_amount,
sum(case when token_in = 'wNEAR' and block_timestamp >= CURRENT_DATE - interval '{{Period}} days' then amount_in end) as near_swapped_from_amount,
-- token_out as swapped_to,
sum(amount_out) as swapped_to_amount,
count(distinct trader) as no_of_swappers,
count(distinct case when token_in = 'wNEAR' and block_timestamp >= CURRENT_DATE - interval '{{Period}} days' then trader end) as near_no_of_swappers,
count(distinct tx_hash) as no_of_swaps,
count(distinct case when token_in = 'wNEAR'and block_timestamp >= CURRENT_DATE - interval '{{Period}} days' then tx_hash end) as near_no_of_swaps,
sum(coalesce(p.price * amount_in, p.price * amount_out)) as usd_amount,
sum(case when token_in = 'wNEAR'and block_timestamp >= CURRENT_DATE - interval '{{Period}} days' then coalesce(p.price * amount_in, p.price * amount_out) end) as near_usd_amount
from near.core.ez_dex_swaps s
left join prices p
on date_trunc('{{Granularity}}', s.block_timestamp) = p.date
and s.token_in = p.symbol
-- where token_in = 'wNEAR'
and block_timestamp >= CURRENT_DATE - interval '{{Period}} days'
-- group by 1,2,4
-- order by 1,3 desc
)
Run a query to Download Data