SELECT
date_trunc('day', block_timestamp) as date,
PROJECT_NAME,
COUNT(DISTINCT tx_hash) as total_tx,
COUNT(DISTINCT origin_from_address) as total_users
FROM optimism.core.fact_event_logs
LEFT outer JOIN optimism.core.dim_labels ON address = contract_address
where project_name in (
SELECT project_name
FROM (
SELECT
PROJECT_NAME,
COUNT(DISTINCT origin_from_address)
FROM optimism.core.fact_event_logs
LEFT outer JOIN optimism.core.dim_labels
ON address = contract_address
where not project_name is NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10
)
)
AND not project_name IS NULL
and block_timestamp::date >= '2022-01-01'
GROUP BY 1,2