x.date as date,

SUM(total_wallets) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sum_wallet,

CASE WHEN (x.date>= '2021-09-20' and x.date<= '2021-10-14') then 'GCP1' WHEN (x.date>= '2021-12-24' and x.date<= '2022-01-07') then 'GCP2' else 'No_GCPs' end as GCPs

FROM (

SELECT a.date as date,

a.unique_wallets as added_wallets,

b.unique_wallets as removed_wallets,

a.unique_wallets - b.unique_wallets as total_wallets

FROM

(SELECT date_trunc('day', bl.block_timestamp) AS date,

COUNT(acc.address) AS unique_wallets

FROM algorand.account acc

LEFT JOIN algorand.block bl

ON acc.created_at = bl.block_id

GROUP BY date) a

FULL JOIN

(SELECT date_trunc('day', bl.block_timestamp) AS date,

COUNT(acc.address) AS unique_wallets

FROM algorand.account acc

LEFT JOIN algorand.block bl

ON acc.closed_at = bl.block_id

GROUP BY date) b

ON a.date=b.date