keshanUntitled Query
Updated 2022-06-06
99
1
2
3
4
5
6
7
8
9
10
11
12
13
›
⌄
select date_trunc('hour', block_timestamp) as hour,
(case when to_currency='Osmosis' then from_currency when from_currency='Osmosis' then to_currency else null end) as token,
avg(price) as price
from (select block_timestamp,
(case when from_currency='uosmo' then (from_amount/pow(10, from_decimal)) / (to_amount/pow(10, to_decimal)) when to_currency='uosmo' then (to_amount/pow(10, to_decimal)) / (from_amount/pow(10, from_decimal)) else null end) as price,
(case when t.label is null then to_currency else t.label end) as to_currency,
(case when f.label is null then from_currency else f.label end) as from_currency
from osmosis.core.fact_swaps
left join osmosis.core.dim_labels t on t.address=to_currency
left join osmosis.core.dim_labels f on f.address=from_currency)
where hour >= '2022-1-1' and price is not null and token like '%Luna%'
group by hour, token
Run a query to Download Data