vendettaswap volume and count
Updated 2024-08-22
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 table1 as (with tab1 as (select block_timestamp::date as "Date", sum(amount_in) as SELLING_VOLUME, count(distinct tx_hash) as selling_count
from near.defi.ez_dex_swaps
where symbol_in='BLACKDRAGON'
group by 1),
tab2 as (select date_trunc('day',block_timestamp) as "Date", (sum(amount_in)/sum(amount_out)) as avg_price
from near.defi.ez_dex_swaps
where symbol_in in ('USDC','USDC.e','USDT.e','USDt')
and SYMBOL_out='BLACKDRAGON' and block_timestamp is not null
group by 1)
select tab1."Date" as "Date", SELLING_VOLUME*avg_price as "Selling Volume (USD)", selling_count
FROM TAB1 LEFT JOIN TAB2 ON tab1."Date"=tab2."Date"
order by 1),
table2 as (with tab1 as (select block_timestamp::date as "Date", sum(amount_out) as buying_volume, count(distinct tx_hash) as buying_count
from near.defi.ez_dex_swaps
where symbol_out='BLACKDRAGON'
group by 1),
tab2 as (select date_trunc('day',block_timestamp) as "Date", (sum(amount_in)/sum(amount_out)) as avg_price
from near.defi.ez_dex_swaps
where symbol_in in ('USDC','USDC.e','USDT.e','USDt')
and SYMBOL_out='BLACKDRAGON' and block_timestamp is not null
group by 1)
select tab1."Date" as "Date", buying_volume*avg_price as "Buying Volume (USD)", buying_count
FROM TAB1 LEFT JOIN TAB2 ON tab1."Date"=tab2."Date"
order by 1)
select SUM("Selling Volume (USD)"+"Buying Volume (USD)") as "Swap Volume (USD)", 'Black Dragon' as "Memecoin",
sum(selling_count+buying_count) as "Swap Count"
FROM table1 left join table2 on table1."Date"=table2."Date"
GROUP BY 2
QueryRunArchived: QueryRun has been archived