nsa2000dot3
Updated 2022-12-14
999
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
›
⌄
--credit to alitaslimi
WITH
prices AS (
SELECT
block_timestamp::date AS day,
AVG(price_usd) AS price
FROM (
SELECT
block_timestamp,
(to_amount / POW(10, to_decimal)) / (from_amount / POW(10, from_decimal)) AS price_usd
FROM
osmosis.core.fact_swaps
WHERE
tx_status = 'SUCCEEDED'
AND from_currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7' -- DOT
AND to_currency = 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858' -- USDC
AND from_amount > 0
AND to_amount > 0
UNION
SELECT
block_timestamp,
(from_amount / POW(10, from_decimal)) / (to_amount / POW(10, to_decimal)) AS price_usd
FROM
osmosis.core.fact_swaps
WHERE
tx_status = 'SUCCEEDED'
AND from_currency = 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858' -- USDC
AND to_currency = 'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7' -- DOT
AND from_amount > 0
AND to_amount > 0
)
GROUP BY
day
),
arbitrageurs AS (
SELECT
Run a query to Download Data