/*select target from (select block_timestamp as t, tx_hash, from_address, to_address, project_name as target from optimism.core.fact_transactions a inner join optimism.core.dim_labels b on b.address=a.to_address) group by 1 */ select "Contract Address", "Past 30 Days", "30-60 Days Ago", CASE WHEN "30-60 Days Ago" > 0 THEN (("Past 30 Days"/"30-60 Days Ago"-1)*100)::DECIMAL(18,2) ELSE 0 END AS "M/M", "New MAAs", CASE WHEN "M2 New MAAs" > 0 THEN ("Retained MAAs"/ "M2 New MAAs" * 100)::DECIMAL(18,2) ELSE 0 END AS "M2 Retention", "Daily Average", CASE WHEN "Past 30 Days" > 0 THEN ("Daily Average"/"Past 30 Days" * 100)::DECIMAL(18,2) ELSE 0 END AS "DAU / MAU" from (SELECT project_name as "Contract Address", COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '30 Days' AND tr.block_timestamp < Current_date THEN from_address END) as "Past 30 Days", COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '60 Days' AND tr.block_timestamp < Current_date - INTERVAL '30 Days' THEN from_address END) as "30-60 Days Ago", COUNT(DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '30 DAYS' AND first_tx < current_date THEN from_address END) AS "New MAAs", COUNT(DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '60 DAYS' AND first_tx < current_date - INTERVAL '30 DAYS' AND tr.block_timestamp >= CURRENT_DATE - INTERVAL '30 Days' AND tr.block_timestamp < Current_date THEN from_address END) AS "Retained MAAs", COUNT(DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '60 DAYS' AND first_tx < current_date - INTERVAL '30 DAYS' THEN from_address END) AS "M2 New MAAs", ((COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '24 Hours' AND tr.block_timestamp < Current_date THEN from_address END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '48 Hours' AND tr.block_timestamp < Current_date - INTERVAL '24 Hours' THEN from_address END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '72 Hours' AND tr.block_timestamp < Current_date - INTERVAL '48 Hours' THEN from_address END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '96 Hours' AND tr.block_timestamp < Current_date - INTERVAL '72 Hours' THEN from_address END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '120 Hours' AND tr.block_timestamp < Current_date - INTERVAL '96 Hours' THEN from_address END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '144 Hours' AND tr.block_timestamp < Current_date - INTERVAL '120 Hours' THEN from_address END) + COUNT(DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '168 Hours' AND tr.block_timestamp < Current_date - INTERVAL '144 Hours' THEN from_address END)) / 7) AS "Daily Average" from (SELECT block_timestamp, to_address as target, from_address FROM optimism.core.fact_transactions) tr JOIN (SELECT from_address AS wallet, MIN(block_timestamp) AS first_tx FROM optimism.core.fact_transactions tr GROUP BY 1) b ON tr.from_address = b.wallet inner join optimism.core.dim_labels c on c.address=tr.target WHERE tr.block_timestamp >= CURRENT_DATE - INTERVAL '90 Days' AND tr.block_timestamp < Current_date GROUP BY 1) a order by 2 desc limit 100

Red and Blue Health