select

count(DISTINCT TX_HASH) as num_tx,

count(DISTINCT BLOCK_NUMBER) as num_bolck,

count(DISTINCT FROM_ADDRESS) as num_user,

date_trunc('hour',BLOCK_TIMESTAMP) as hour_date,

sum(GAS_PRICE) as sum_gas,

avg(GAS_PRICE) as avg_gas,

median(GAS_PRICE) as median_gas,

case when BLOCK_TIMESTAMP >='2023-01-17 10:45:02.000' then 'After' else 'Before' end as period

from polygon.core.fact_transactions

where BLOCK_TIMESTAMP >= dateadd(day,datediff(day,CURRENT_DATE-1,'2023-01-17'),'2023-01-17' )

and BLOCK_TIMESTAMP < CURRENT_DATE

group by period , hour_date

, after as (

select 'After', * from total

where period = 'After'

order by num_tx DESC

limit 10

, before as (

select 'Before', * from total

where period = 'Before'

order by num_tx DESC

limit 10

, max_after as

(select max(num_tx) as max_num_tx from after)

select * from after

union

select * from before