kidaTotal MEV Amount by Swapper
Updated 2022-12-23
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
prices AS (
SELECT
TRUNC(recorded_at, 'day') as date,
AVG(price) as price
FROM
osmosis.core.dim_prices
WHERE
symbol = 'ATOM'
GROUP BY date
),
token_prices AS (
SELECT
s.date,
NVL(l.project_name, s.denom) as symbol,
s.atom_ratio,
p.price,
atom_ratio * IFF(price is not null, price, 0) AS price_usd
FROM (
SELECT
block_timestamp::date as date,
CASE WHEN from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2'
THEN to_currency
ELSE from_currency
END as denom,
MEDIAN(
CASE WHEN from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2'
THEN (from_amount / POW(10, NVL(from_decimal, 6))) / (to_amount / POW(10, NVL(to_decimal, 6)))
ELSE (to_amount / POW(10, NVL(to_decimal, 6))) / (from_amount / POW(10, NVL(from_decimal, 6)))
END
) as atom_ratio
FROM osmosis.core.fact_swaps
WHERE
(from_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2') OR
(to_currency = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' AND block_timestamp >= '2022-06-21') -- get to only when after 2021-06-22 cause of inaccuracies in swap ratio
Run a query to Download Data