DATE_TRUNC('day', block_timestamp) AS date,

WHEN to_address IS NULL THEN 'Contract Creation'

WHEN to_address <> from_address THEN 'External Transaction'

ELSE 'Internal Transaction'

END AS transaction_type,

COUNT(distinct tx_hash) AS num_transactions,

sum(num_transactions) over (partition by transaction_type order by date) as total_transactions

FROM base.goerli.fact_transactions

where status='SUCCESS'

GROUP BY 1,2

order by 1 asc,2