MLDZMNOAS12-2
Updated 2023-01-28
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
›
⌄
with tb8 as (select
RECORDED_AT::date as day,
SYMBOL,
avg(PRICE) as price_token
from osmosis.core.dim_prices
where symbol not in ('IOV','JUNO')
group by 1,2)
SELECT
count(distinct TX_ID) as no_swaps,
count(distinct TRADER) as no_trader,
'1' as no_platform,
--count(distinct POOL_ADDRESS) as no_swap_pairs,
sum((TO_AMOUNT/pow(10,TO_DECIMAL))*price_token) as volume_usd,
avg((TO_AMOUNT/pow(10,TO_DECIMAL))*price_token) as average_volume,
median((TO_AMOUNT/pow(10,TO_DECIMAL))*price_token) as median_volume,
no_swaps/no_trader as average_swapper,
no_swaps/no_platform as average_swap_per_platform,
--volume_USD/no_pools as average_volume_per_pool,
volume_USD/no_platform as average_volume_per_platform,
no_trader/count(distinct date_trunc(day, block_timestamp)) as average_buyer_day,
volume_USD/count(distinct date_trunc(day, block_timestamp)) as average_volume_day,
volume_usd/count(distinct BLOCK_ID) as average_volume_block
FROM osmosis.core.fact_swaps s join osmosis.core.dim_labels b on s.TO_CURRENCY=b.ADDRESS
left join tb8 a on b.PROJECT_NAME=a.SYMBOL and s.block_timestamp::date=a.day
WHERE (TO_AMOUNT/pow(10,TO_DECIMAL))*price_token<1e6
Run a query to Download Data