WITH t0 AS (

SELECT DATE_TRUNC('week', block_timestamp)::date AS week

, signers[0]::string AS address

, COUNT(1) AS n_tx

, COUNT(DISTINCT(block_timestamp::date)) AS n_days_active

FROM solana.core.fact_transactions

WHERE week >= CURRENT_DATE - 365

GROUP BY 1, 2

), t1 AS (

SELECT t0.week

, DATEADD('week', 1, t0.week) AS next_week

, COUNT(1) AS n_wallets

, SUM(CASE WHEN t0.n_tx >= 5 THEN 1 ELSE 0 END) AS n_wallets_5_tx

, SUM(CASE WHEN t0.n_days_active >= 2 THEN 1 ELSE 0 END) AS n_wallets_2_days

, SUM(CASE WHEN t0b.address IS NULL THEN 0 ELSE 1 END) AS n_wallets_next_week

FROM t0

LEFT JOIN t0 t0b

ON t0b.week = DATEADD('week', 1, t0.week)

AND t0b.address = t0.address

GROUP BY 1

), t2 AS (

SELECT DATE_TRUNC('week', block_timestamp)::date AS week

, from_address AS address

, COUNT(1) AS n_tx

, COUNT(DISTINCT(block_timestamp::date)) AS n_days_active

FROM polygon.core.fact_transactions

WHERE week >= CURRENT_DATE - 180

GROUP BY 1, 2

), t3 AS (

SELECT t2.week

, DATEADD('week', 1, t2.week) AS next_week

, COUNT(1) AS n_wallets

, SUM(CASE WHEN t2.n_tx >= 5 THEN 1 ELSE 0 END) AS n_wallets_5_tx