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 = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' and block_timestamp between '2021-05-19' and '2021-05-23'),

luna as (select luna_exchange_rate as LUNA_price,

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

from terra.oracle_prices

where currency = 'uusd' and source = 'oracle' and block_timestamp between '2021-05-19' and '2021-05-25')

select a.timestamp_30s, min(a.price) as bluna, min(b.luna_price) as luna, bluna/luna as bluna_to_luna_peg

from raw a

join luna b on a.timestamp_30s = b.timestamp_30s

group by 1