date_trunc('minute', block_timestamp) as min,

datediff(seconds, min, block_timestamp) as seconds,

CASE when seconds = 0 THEN min

WHEN seconds >0 AND seconds < 15 then min -- round down to 00:00

WHEN seconds >= 15 AND seconds < 45 then DATEADD('seconds', 30, min)

WHEN seconds >= 45 AND seconds < 60 then DATEADD('seconds', 60, min)

ELSE NULL END AS timestamp_30s,

F.value[0]::string as string,

F.value[1]::string as Price

from terra.msgs,

Table(Flatten(input => msg_value:execute_msg:feed_price:prices)) F

where F.value[0]::string = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun' and block_timestamp between '2021-09-15' and '2021-09-23'),

eth as (select block_timestamp,

date_trunc('minute', block_timestamp) as min,

datediff(seconds, min, block_timestamp) as seconds,

CASE when seconds = 0 THEN min

WHEN seconds >0 AND seconds < 15 then min -- round down to 00:00

WHEN seconds >= 15 AND seconds < 45 then DATEADD('seconds', 30, min)

WHEN seconds >= 45 AND seconds < 60 then DATEADD('seconds', 60, min)

ELSE NULL END AS timestamp_30s,

amount, amount_usd, (amount_usd/amount) as ETH_price

from ethereum.udm_events

where symbol = 'ETH' and amount is not null and amount_usd is not null and block_timestamp between '2021-09-15' and '2021-09-23')

select a.timestamp_30s, avg(a.Price) as bETH_price, avg(b.ETH_price) as ETH_price, ( avg(a.Price)/avg(b.ETH_price) ) as bETH_ETH_peg

from beth A

join eth B on a.timestamp_30s = b.timestamp_30s

group by 1