davidwallUntitled Query
Updated 2023-01-18
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/addcca4b-ea4c-4c05-a439-3f0e368310fe
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-30
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-30
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-30
group by 1
),
matic_price as (
select
trunc(hour,'day') as date,
avg(price) as matic_price,
LAG(matic_price,1) IGNORE NULLS OVER (ORDER BY date) as last_price,
((matic_price-last_price)/matic_price)*100 as matic_price_change
from ethereum.core.fact_hourly_token_prices where token_address=lower('0x7c9f4C87d911613Fe9ca58b579f737911AAD2D43') and hour>=CURRENT_DATE-30
Run a query to Download Data