date_trunc('{{date_aggregation}}', block_timestamp) as date,

count(distinct(tx_hash)) as n_transactions,

avg(n_transactions) over (order by date rows between 13 preceding and current row) as "12h_ma_n_transactions",

count(distinct(from_address)) as active_addresses,

avg(active_addresses) over (order by date rows between 13 preceding and current row) as "12h_ma_active_addresses",

sum(eth_value) as total_eth_value,

avg(total_eth_value) over (order by date rows between 13 preceding and current row) as "12h_ma_total_eth_value",

sum(tx_fee) as total_transaction_fee,

avg(total_transaction_fee) over (order by date rows between 13 preceding and current row) as "12h_ma_total_transaction_fee",

total_transaction_fee/n_transactions as avg_transaction_fee,

avg(avg_transaction_fee) over (order by date rows between 13 preceding and current row) as "12h_ma_avg_transaction_fee",

count(distinct(from_address)) as from_addressess,

avg(from_addressess) over (order by date rows between 13 preceding and current row) as "12h_ma_from_addressess",

count(distinct(to_address)) as to_addressess,

avg(to_addressess) over (order by date rows between 13 preceding and current row) as "12h_ma_to_addressess"

from ethereum.core.fact_transactions

where date >= '2023-1-1'

and date <= '2023-1-16'

group by date),

price as (select

date_trunc('{{date_aggregation}}', hour) as date,

avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where token_address is null

and hour >= '2023-1-1'

and hour <= '2023-1-16'

group by date

),

first_tx as (select

from_address,

min(block_timestamp) as first_transactions

from ethereum.core.fact_transactions

group by from_address