with assets as (
SELECT asset_id, asset_name, sum(volume_usd_in_hour) as vol_usd, avg(volatility_measure) as avg_volatility, sum(swaps_in_hour) as vol_swaps
from algorand.prices_swap
where date(block_hour) >= CURRENT_DATE - 30
GROUP by asset_id, asset_name HAVING vol_swaps > 0
order by vol_swaps DESC
limit 10
)
SELECT extract('hour', block_hour) as h, avg(volatility_measure) as avg_volatility, avg(volume_usd_in_hour) as avg_volume, avg(swaps_in_hour) as avg_swaps
from algorand.prices_swap
where date(block_hour) >= CURRENT_DATE - 30
and asset_id in (SELECT asset_id from assets)
GROUP by h