Query editor
WITH d AS (SELECT project, day, volume_usd, messagesFROM dune.gigagobin.result_canary_sentinel_app_dailyWHERE day < CURRENT_DATE),agg AS (SELECTproject,COUNT(*) AS active_days,COUNT_IF(volume_usd > 0) AS valued_days,COUNT_IF(volume_usd > 0) * 1.0 / COUNT(*) AS valued_share,SUM(messages) AS msgs_allFROM dGROUP BY 1),focus AS (SELECT * FROM aggWHERE project IN ('Aori','Shadows','Centrifuge','Derive','Xmaquina','CARV','Stargate','USDT0','Ethena','PayPal','wBTC','Paxos','aPriori','Orderly Network')OR msgs_all IN (SELECT msgs_all FROM (SELECT msgs_all FROM agg ORDER BY msgs_all DESC LIMIT 25) t))SELECTproject,active_days,valued_days,valued_share,CASE WHEN valued_share < 0.5 THEN 'SKIP_USD' ELSE 'PASS_USD' END AS gate_at_cmin_0_5,msgs_allFROM focusORDER BY valued_share ASC, msgs_all DESC