rakhisanjayaAVERAGE SWAPS & SWAPPERS AND VOLUME
Updated 2022-12-08
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 solpricet as (
select block_timestamp::date as day,
swap_from_mint as token,
median (swap_to_amount/swap_from_amount) as USDPrice
from solana.fact_swaps
where swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') --USDC,USDT
and swap_to_amount > 0
and swap_from_amount > 0
and block_timestamp >= '2022-10-25'
and succeeded = 'TRUE'
group by 1,2),
osmopricet as (
select recorded_at::date as day,
address,
symbol,
avg (price) as USDPrice
from osmosis.core.dim_prices t1 join osmosis.core.dim_labels t2 on t1.symbol = t2.project_name
where symbol != 'IOV'
and recorded_at >= '2022-10-25'
group by 1,2,3),
maintable as (
select 'Ethereum Uniswap' as dex,
block_timestamp::date as date,
case when date < '2022-11-08' then 'Before Collapse'
when date >= '2022-11-08' and date <= '2022-11-10' then 'Main Collapse Days'
when date > '2022-11-10' then 'After Collapse' end as timespan,
count (distinct tx_hash) as Swaps_Count,
count (distinct recipient) as Swappers_Count,
abs (sum (amount0_usd)) as Total_Volume,
abs (avg (amount0_usd)) as Average_Volume
from ethereum.uniswapv3.ez_swaps
where block_timestamp >= '2022-10-25' and block_timestamp::date != CURRENT_DATE
group by 1,2,3