SELECT

SPLIT_PART(date_trunc('day', block_timestamp), ' ', 1) as day,

count(DISTINCT tx_hash) as transactions,

--count(DISTINCT FROM_ADDRESS) as users,

count(DISTINCT TO_ADDRESS) as active_contracts,

sum(ETH_VALUE) as eth_transfered

FROM optimism.core.fact_transactions

WHERE block_timestamp > CURRENT_DATE - 180

AND STATUS LIKE 'SUCCESS'

GROUP BY 1

ORDER BY 1

), tab2 as (

SELECT

min_day as day1,

count(*) as users

FROM (

SELECT

from_address,

min(SPLIT_PART(date_trunc('day', block_timestamp), ' ', 1)) as min_day

FROM optimism.core.fact_transactions

GROUP BY 1

)

GROUP BY 1

SELECT

day,

transactions,

users,

active_contracts,

eth_transfered

FROM tab1

LEFT outer join tab2

ON day = day1