A as(

select date_trunc('hour',block_timestamp) as time,

count(distinct tx_from) as active_user,

count(distinct tx_id) as tx,

sum(fee/1e6) as fee,

sum(case when tx_succeeded = 'TRUE' then 1 else 0 end) as tx_succeeded,

sum(case when tx_succeeded = 'FALSE' then 1 else 0 end) as tx_false

from lava.core.fact_transactions

where block_timestamp >= '2024-07-30 11:00:00.000'

group by 1 ),

B as(

select date_trunc('hour',first) as times, count(new_user) as new_user

from (select tx_from as new_user, min(block_timestamp) as first from lava.core.fact_transactions where block_timestamp >= '2024-07-30 11:00:00.000' group by 1)

group by 1 )

select time, '-' "I",

active_user as "Active users",

sum(new_user) over(order by time) as "Total users",

sum(new_user) over(order by time) - new_user as "Retained users", new_user as "New users",

'-' "III",

fee, sum(fee) over(order by time) as fees, fee / tx_succeeded as fee_avg

from A as a

join B as b on a.time = b.times

order by 1 desc