( -- Total Accounts

SELECT * FROM (with daily_total as(

SELECT metric_date,

sum(address) OVER (ORDER BY metric_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS value

(SELECT date_trunc('day', start_date) AS metric_date,

count(distinct address) AS address

(SELECT address,

min(start_date) AS start_date

FROM

(SELECT event_to AS address,

min(block_timestamp) AS start_date

FROM redshift.udm_events_near

GROUP BY 1

UNION

SELECT event_from AS address,

min(block_timestamp) AS start_date

FROM redshift.udm_events_near tr

GROUP BY 1)sq

GROUP BY 1)si

GROUP BY 1)se),

daily_deleted as (

SELECT

date_trunc('day', block_timestamp) as date,

count(distinct event_from) as address_count

FROM gold.near_events

WHERE event_type = 'DeleteAccount'

GROUP BY 1

ORDER BY 1 DESC

SELECT metric_date,

'total' as metric_slug,

value - COALESCE(address_count,0) as value