Addresses

with
start_times as (
	select
		"from" as address,
		min(block_time) as start_time
	from
		ethereum.transactions
	group by
		1
	union
	select
		"to" as address,
		min(block_time) as start_time
	from
		ethereum.transactions
	group by
		1
),
new_addresses as (
	select
		date_trunc('day', start_time) as date,
		count(distinct address) as addresses
	from
		start_times
	group by
		1
),
active_addresses as (
	select
		date_trunc('day', block_time) as date,
		count(distinct "from") as addresses
	from
		ethereum.transactions
	group by
		1
),
dates as (
	select
		timestamp as date
	from
		utils.days
)
select
	d.date,
	a.addresses as addresses_active,
	n.addresses as addresses_new,
	a.addresses - n.addresses as addresses_returning
from
	dates as d
	left join active_addresses as a on d.date = a.date
	left join new_addresses as n on d.date = n.date
order by
	d.date desc