WITH t0 AS (

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

, CASE WHEN from_address = '0x0000000000000000000000000000000000000000' THEN to_address ELSE from_address END AS address

, SUM(CASE WHEN from_address = '0x0000000000000000000000000000000000000000' THEN raw_amount ELSE -raw_amount END) * POWER(10, -9) AS net_amount_decimals

FROM ethereum.core.fact_token_transfers

WHERE block_timestamp >= '2021-01-01'

AND contract_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c'

AND (

from_address = '0x0000000000000000000000000000000000000000'

OR to_address = '0x3ee18b2214aff97000d974cf647e7c347e8fa585'

)

GROUP BY 1, 2

), t1 AS (

SELECT *

, SUM(net_amount_decimals) OVER (PARTITION BY address ORDER BY date) AS cumu_net_amount_decimals

FROM t0

), t2 AS (

SELECT *

, COALESCE( LAG(cumu_net_amount_decimals, 1) OVER (PARTITION BY address ORDER BY date), 0 ) AS prv_cumu_net_amount_decimals

, CASE

WHEN cumu_net_amount_decimals >= 1 AND prv_cumu_net_amount_decimals < 1 THEN 1

WHEN cumu_net_amount_decimals < 1 AND prv_cumu_net_amount_decimals >= 1 THEN -1

ELSE 0 END AS net_chg

FROM t1

), t3 AS (

SELECT date

, SUM(net_chg) AS net_new_wsol_holders

FROM t2

GROUP BY 1

), t4 AS (

SELECT *

, SUM(net_new_wsol_holders) OVER (ORDER BY DATE) AS cumu_wsol_holders

FROM t3