'Osmosis' as chain,

case

when extract(dow from date_trunc(hour, block_timestamp)) = 0 then '7.Sunday'

when extract(dow from date_trunc(hour, block_timestamp)) = 1 then '1.Monday'

when extract(dow from date_trunc(hour, block_timestamp)) = 2 then '2.Tuesday'

when extract(dow from date_trunc(hour, block_timestamp)) = 3 then '3.Wednesday'

when extract(dow from date_trunc(hour, block_timestamp)) = 4 then '4.Thursday'

when extract(dow from date_trunc(hour, block_timestamp)) = 5 then '5.Friday'

when extract(dow from date_trunc(hour, block_timestamp)) = 6 then '6.Saturday'

end as days,

date_part(hour, date_trunc(hour, block_timestamp)) as hours,

count(tx_id) as "Daily TX Count",

"Daily TX Count"/(60 * count(distinct date_trunc(hour,block_timestamp))) as "Transactions Per Minute (TPM)",

"Daily TX Count"/(3600 * count(distinct date_trunc(hour,block_timestamp))) as "Transactions Per Second (TPS)"

from Osmosis.core.fact_transactions

where block_timestamp >= '2022-01-01' and block_timestamp::date != CURRENT_DATE and tx_status = 'SUCCEEDED'

group by 1,2,3