date_trunc('hour', block_timestamp) as date,

'Osmosis' as chain,

count(distinct block_id) as block_count,

count(distinct tx_id) as tx_count,

sum(case when TX_STATUS = 'SUCCEEDED' then 1 else 0 end) as successful_tx,

sum(case when TX_STATUS !='SUCCEEDED' then 1 else 0 end) as failed_tx,

successful_tx + failed_tx as total_tx, -- shld equal to tx_count

count(distinct tx_from) as unique_users,

tx_count/block_count as tx_per_block,

successful_tx/(failed_tx+successful_tx)*100 as success_tx_rate

FROM osmosis.core.fact_transactions

WHERe block_timestamp > '2022-07-10' AND blocK_timestamp <'2022-07-11'

GROUP BY 1