mehdimarjanOsmosis Average Swap From Fee
Updated 2022-06-27Copy Reference Fork
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
›
⌄
WITH swaps AS (
SELECT s.BLOCK_TIMESTAMP AS TIME, s.TX_ID, TO_CURRENCY, PROJECT_NAME, TRADER, FEE
FROM osmosis.core.fact_swaps s
INNER JOIN osmosis.core.fact_transactions t ON s.TX_ID = t.TX_ID
INNER JOIN osmosis.core.dim_labels ON ADDRESS = TO_CURRENCY
WHERE s.TX_STATUS = 'SUCCEEDED'
),
fees AS (
SELECT TIME, split_part(FEE, 'uosmo', 1)/1e6 AS FEE, 'OSMO' AS fee_currency, (split_part(FEE, 'uosmo', 1)/1e6) * PRICE AS usd_fee
FROM swaps
INNER JOIN osmosis.core.dim_prices ON 'OSMO' = SYMBOL
WHERE FEE ilike '%uosmo%'
-- AND FEE != '0uosmo'
UNION
SELECT TIME, (split_part(FEE, 'ibc', 1)/1e6) AS FEE, (split_part(FEE, 'ibc', 1)/1e6) * PRICE AS usd_fee,
(CASE
WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/1480B8FD20AD5FCAE81EA87584D269547DD4D436843C1D20F15E00EB64743EF4' THEN 'AKT'
WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' THEN 'ATOM'
WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/0EF15DF2F02480ADE0BB6E85D9EBB5DAEA2836D3860E9F97F9AADE4F57A31AA0' THEN 'LUNC'
END
) AS fee_currency
FROM swaps s
INNER JOIN osmosis.core.dim_prices ON SYMBOL = 'ATOM'
WHERE CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) IN ('ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2')
)
SELECT TIME, split_part(FEE, 'uosmo', 1)/1e6 AS FEE, 'OSMO' AS fee_currency, (split_part(FEE, 'uosmo', 1)/1e6) * PRICE AS usd_fee
FROM swaps
INNER JOIN osmosis.core.dim_prices ON 'OSMO' = SYMBOL
WHERE FEE ilike '%uosmo%'
-- AND FEE != '0uosmo'
UNION
SELECT TIME, (split_part(FEE, 'ibc', 1)/1e6) AS FEE, (split_part(FEE, 'ibc', 1)/1e6) * PRICE AS usd_fee,
(CASE
WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/1480B8FD20AD5FCAE81EA87584D269547DD4D436843C1D20F15E00EB64743EF4' THEN 'AKT'
WHEN CONCAT( 'ibc', split_part(FEE, 'ibc', 2)) = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' THEN 'ATOM'
Run a query to Download Data