with first_incoming_transaction as

(

select to_address, min(block_timestamp) as first_transaction_rec

from ethereum.core.fact_transactions

group by to_address

having first_transaction_rec > '{{Start_date}}'

),

first_outgoing_transaction as

(

select from_address, min(block_timestamp) as first_transaction_sent

from ethereum.core.fact_transactions

group by from_address

having first_transaction_sent > '{{Start_date}}'

),

growth as

(

select

inn.first_transaction_rec::date as date,

count(distinct inn.to_address) as number_of_accounts,

sum(number_of_accounts) over (order by date) as cummulative_new_users,

(number_of_accounts- lag(number_of_accounts) over(order by date asc))/lag(number_of_accounts) over(order by date asc) as growth_percentage

-- AVG(growth_percentage) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) "7_DAY_AVG_growth"

from first_outgoing_transaction out

inner join first_incoming_transaction inn

on out.from_address=inn.to_address

group by date

)

select 'ethereum' as chain, *, AVG(growth_percentage) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as ma from growth

),

arbitrum as

with first_incoming_transaction as