with active as ( select tx_signer, count(DISTINCT(tx_hash)) as total_tx

from near.core.fact_transactions

where block_timestamp::date >= '2022-07-01'

group by 1

having total_tx > 25)

,

average as ( select date(block_timestamp) as date, tx_signer, count(DISTINCT(tx_hash)) as total_tx

from near.core.fact_transactions

where tx_signer in (select tx_signer from active)

and date >= '2022-07-01'

group by 1,2)

select date, count(DISTINCT(tx_signer)) as user, sum(total_tx) as total_txs, avg(total_tx) as avg_tx

from average

group by 1