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 (30 Seconds)"

from terra.msg_events

where event_type = 'from_contract' and event_attributes:"1_contract_address"::string = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' and event_attributes:"2_contract_address"::string = 'terra17yap3mhph35pcwvhza38c2lkj7gzywzy05h7l0' and block_timestamp between '2021-05-19' and '2021-05-23'

and luna is not null and bluna is not null)

select "timestamp (30 Seconds)", avg(bluna_to_luna_peg) as bluna_to_luna_peg, min(bluna_to_luna_peg) as min_depeg

from raw

group by 1