WITH top_10_prejects as (SELECT b.PROJECT_NAME AS project_name,
count(DISTINCT tx_hash) AS num_tx,
count(DISTINCT origin_from_address) AS num_users
FROM optimism.core.fact_event_logs AS a
JOIN optimism.core.dim_labels AS b ON a.origin_to_address = b.address
WHERE tx_status = 'SUCCESS'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10)
SELECT date_trunc('week', block_timestamp) AS dt,
b.PROJECT_NAME,
count(DISTINCT tx_hash) AS total_txs,
count(DISTINCT origin_from_address) AS total_users
FROM optimism.core.fact_event_logs AS a
JOIN optimism.core.dim_labels AS b ON a.origin_to_address = b.address
WHERE project_name in
(SELECT project_name
FROM top_10_prejects)
AND block_timestamp::date != CURRENT_DATE
GROUP BY 1, 2