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 timestamp_30s, avg(ETH_price) as ETH_price

from raw

group by 1