select * from
(select *,
row_number() over (partition by date order by tx_count desc) as rank
from
(select date_trunc('hour',BLOCK_TIMESTAMP) as date, EVENT_TYPE as contract, count(distinct TX_ID) as tx_count
from flow.core.fact_events
where BLOCK_TIMESTAMP::date='2022-05-16'
group by date, contract))
where rank<=10