shahdadi-9ptm8Yosmo 1
Updated 2022-11-16
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
WITH A AS(SELECT
DATE_TRUNC('HOUR',BLOCK_TIMESTAMP) DATE,
(SUM(TO_AMOUNT)/POW(10,18))/(SUM(FROM_AMOUNT)/POW(10,6)) OSMO_PRICE
FROM osmosis.core.fact_swaps
WHERE DATE >= CURRENT_DATE - 28
AND FROM_CURRENCY = 'uosmo' AND TO_CURRENCY ='ibc/0CD3A0285E1341859B5E86B6AB7682F023D03E97607CCC1DC95706411D866DF7'
GROUP BY 1
),B AS(
SELECT
DATE_TRUNC('HOUR',BLOCK_TIMESTAMP) DATE,
COUNT(TX_ID) TXN
FROM osmosis.core.fact_transactions
WHERE DATE >= CURRENT_DATE - 28
GROUP BY 1
), C AS(
SELECT A.DATE,OSMO_PRICE,TXN
FROM A JOIN B ON A.DATE=B.DATE
)
SELECT
DATE_TRUNC('DAY',DATE) DATE,
SUM(TXN) transactions,
AVG(OSMO_PRICE) price
FROM C GROUP BY 1
Run a query to Download Data