case

when block_timestamp::date < '2022-10-11' then 'befor-hack'

when block_timestamp::date >= '2022-10-11' then 'after-hack'

end as date_case,

project_name,

count(DISTINCT tx_to) as total_users,

count(DISTINCT tx_id) as total_transaction

from solana.core.fact_transfers x join flipside_prod_db.crosschain.address_labels y on

x.tx_from = address

where tx_to not in ( select address

from flipside_prod_db.crosschain.address_labels

where label_subtype = 'hot_wallet'

and blockchain = 'solana')

and label_subtype = 'hot_wallet'

and date >= CURRENT_DATE - 20

group by date,date_case,project_name

order by date)

select date_case,

project_name,

avg(total_users) as user_average,

avg(total_transaction) as transaction_average

from t1

group by date_case,project_name