with ust_price as (

date_trunc('hour', block_timestamp) AS ust_date,

avg(price_usd) over (order by ust_date asc rows 12 PRECEDING ) as ma12hrust ,

avg(price_usd) over (order by ust_date asc rows 24 PRECEDING ) as madayust ,

avg(price_usd) over (order by ust_date asc rows 72 PRECEDING ) as ma3dayust ,

avg(price_usd) over (order by ust_date asc rows 240 PRECEDING ) as maweekust

FROM terra.oracle_prices

where SYMBOL = 'UST' and block_timestamp > '2021-04-01' order by ust_date desc

),

avg_prc_luna as (

date_trunc('hour', block_timestamp) AS date,

avg(price_usd) over (order by date asc rows 12 PRECEDING ) as ma12hr ,

avg(price_usd) over (order by date asc rows 24 PRECEDING ) as maday ,

avg(price_usd) over (order by date asc rows 72 PRECEDING ) as ma3day ,

avg(price_usd) over (order by date asc rows 240 PRECEDING ) as maweek

FROM terra.oracle_prices

where SYMBOL = 'LUNA' and block_timestamp > '2021-04-01' order by date desc

),

final_test as (

select date, avg(ma12hr) as hr12, avg(maday) as day1, avg(maweek) as week, avg(ma3day) as day3

from avg_prc_luna where date > '2021-04-01'

group by date order by date desc limit 10000

),

ust as (

SELECT ust_date, avg(MA12HRUST) as hr12_ust, avg(madayust) as day1_ust, avg(maweekust) as week_ust, avg(ma3dayust) as day3_ust

from ust_price where ust_date > '2021-04-01'

group by ust_date

order by ust_date desc

limit 10000

),