from terra.core.fact_transactions

group by 2)

,

tb2 as ( select DISTINCT tx_sender

from tb1

where days >= CURRENT_DATE - 30)

,

tb3 as ( select trunc(block_timestamp,'day') as day,

'New_users' as user_type,

count(DISTINCT tx_id) as count_tx,

count(DISTINCT tx_sender) as count_user,

sum(fee) as fee_amount_in_luna

from terra.core.fact_transactions

where block_timestamp >= CURRENT_DATE - 30

and tx_sender in (select tx_sender from tb2 )

group by 1,2

select trunc(block_timestamp,'day') as day,

'Old_users' as user_type,

count(DISTINCT tx_id) as count_tx,

count(DISTINCT tx_sender) as count_user,

sum(fee) as fee_amount_in_luna

from terra.core.fact_transactions

where block_timestamp >= CURRENT_DATE - 30

and tx_sender not in (select tx_sender from tb2 )

group by 1,2)

select case when day < '2023-01-14' then 'Pre-Station'

when day > '2023-01-14' then 'Post-Station'

when day = '2023-01-14' then 'Terra Station Launch' end as status,

user_type,

avg(count_tx) as average_tx,

avg(count_user) as average_user,

avg(fee_amount_in_luna) as average_fee