SniperBi13
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 Usd_Price as (
select
date_trunc('Day', recorded_at) as hour_Date,
symbol,
avg(price) as price_usd
from osmosis.core.dim_prices
where date_trunc('Day', recorded_at) BETWEEN '2022-10-26' and '2022-10-31'
group by 1 ,2
)
, Final as (
SELECT
date_trunc('day', BLOCK_TIMESTAMP) as date,
TX_ID,
TRADER,
T2.PROJECT_NAME as symbol_In,
T3.PROJECT_NAME as symbol_Out,
FROM_AMOUNT/pow(10, FROM_DECIMAL) * T4.price_usd as from_Volume,
TO_AMOUNT/pow(10, TO_DECIMAL) * T5.price_usd as to_Volume
FROM osmosis.core.fact_swaps T1
join osmosis.core.dim_labels T2 on T1.FROM_CURRENCY = T2.Address
join osmosis.core.dim_labels T3 on T1.TO_CURRENCY = T3.Address
join Usd_Price T4 on T2.PROJECT_NAME = T4.symbol and date_trunc('day', T1.BLOCK_TIMESTAMP) = T4.hour_Date
join Usd_Price T5 on T3.PROJECT_NAME = T5.symbol and date_trunc('day', T1.BLOCK_TIMESTAMP) = T5.hour_Date
WHERE date BETWEEN '2022-10-26' and '2022-10-31'
)
SELECT
date,
symbol_In,
symbol_Out,
concat(symbol_In,'->',symbol_Out) pair,
COUNT(DISTINCT TX_ID) as tx_cnt,
COUNT(DISTINCT TRADER) as User,
sum(from_Volume) as swap_from_Volume,
Run a query to Download Data