WITH t0 AS (

SELECT tx_id

, tx_from AS address

FROM solana.core.fact_transfers

WHERE block_timestamp >= '2022-09-01'

AND block_timestamp <= '2022-10-01'

UNION

SELECT DISTINCT tx_id

, tx_to AS address

FROM solana.core.fact_transfers

WHERE block_timestamp >= '2022-09-01'

AND block_timestamp <= '2022-10-01'

), exclude_programs AS (

SELECT address

FROM solana.core.dim_labels

WHERE label = 'solana'

), t1 AS (

SELECT t0.address

, program_id

, AVG(CASE WHEN t0.address = e.signers[0]::string THEN 1 ELSE 0 END) AS pct_is_signer

, COUNT(1) AS n_tx

FROM solana.core.fact_events e

JOIN t0

ON t0.tx_id = e.tx_id

WHERE block_timestamp >= '2022-09-01'

AND block_timestamp <= '2022-10-01'

AND NOT program_id IN (

SELECT address FROM exclude_programs

)

GROUP BY 1, 2

), t2 AS (

SELECT *

, SUM(n_tx) OVER (PARTITION BY address) AS tot_tx

FROM t1