apostleoffinanceOsmosis DEX Activities
Updated 2025-01-17
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 price AS (
SELECT
recorded_hour::DATE AS date,
currency,
AVG(price) AS token_price
FROM osmosis.price.ez_prices
GROUP BY 1, 2
),
--To query out total voulme in USD and fees in USD , we have to use CTE to first get the price of token
dex_act AS (
SELECT
date_trunc('month', block_timestamp) AS date,
COUNT(DISTINCT tx_id) AS "Number of Swaps",
COUNT(DISTINCT trader) AS "Number of Swappers",
SUM(token_price*to_amount/pow(10, a.DECIMAL)) AS "Trading Volume in USD",
AVG(token_price*to_amount/pow(10, a.DECIMAL)) AS "Avg Trading Volume in USD"
FROM osmosis.defi.fact_swaps b
LEFT JOIN price c ON b.block_timestamp::DATE= date AND b.to_currency = c.currency
LEFT JOIN osmosis.core.dim_tokens a ON b.to_currency = a.address
WHERE block_timestamp >= '2023-01-01'
AND block_timestamp < CURRENT_DATE--'2024-04-03'
AND tx_succeeded = 'TRUE'
AND a.DECIMAL IS NOT NULL
AND a.DECIMAL>0
GROUP BY 1),
--The query above is to get information on swaps,swappers and volume in usd
fee as (
SELECT
BLOCK_DATE as date,
sum(FEES*token_price) as "Trading fee (USD)",
sum("Trading fee (USD)") over (order by BLOCK_DATE) as "Cumulative trading fee (USD)"
Auto-refreshes every 12 hours
QueryRunArchived: QueryRun has been archived