davidwallUntitled Query
Updated 2023-01-19
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
›
⌄
--credit : https://app.flipsidecrypto.com/velocity/queries/8344c80f-3ec1-45da-87e9-2af34c62fded
WITH
osmo_price as (
select
trunc(recorded_at,'day') as date,
avg(price) as osmo_price,
LAG(osmo_price,1) IGNORE NULLS OVER (ORDER BY date) as last_price,
((osmo_price-last_price)/osmo_price)*100 as osmo_price_change
from osmosis.core.dim_prices where symbol='OSMO' and date>=CURRENT_DATE-90
group by 1
),
luna_price as (
select
trunc(recorded_hour,'day') as date,
avg(close) as luna_price,
LAG(luna_price,1) IGNORE NULLS OVER (ORDER BY date) as last_price,
((luna_price-last_price)/luna_price)*100 as luna_price_change
from crosschain.core.fact_hourly_prices where id='terra-luna-2' and date>=CURRENT_DATE-90
group by 1
),
atom_price as (
select
trunc(recorded_at,'day') as date,
avg(price) as atom_price,
LAG(atom_price,1) IGNORE NULLS OVER (ORDER BY date) as last_price,
((atom_price-last_price)/atom_price)*100 as atom_price_change
from osmosis.core.dim_prices where symbol='ATOM' and date>=CURRENT_DATE-90
group by 1
),
changes as (
SELECT
x.date,
luna_price_change,
(osmo_price_change+luna_price_change+atom_price_change)/3 as avg_market_change
from osmo_price x, luna_price y, atom_price a
where x.date=y.date and x.date =a.date
Run a query to Download Data