Query editor
WITH daily_apps AS (SELECT project, SUM(messages) AS msgs_365dFROM dune.gigagobin.result_canary_sentinel_app_dailyWHERE day >= CURRENT_DATE - INTERVAL '365' DAYAND day < CURRENT_DATEGROUP BY 1),top25 AS (SELECT project, msgs_365d,ROW_NUMBER() OVER (ORDER BY msgs_365d DESC) AS rank_by_msgs_365dFROM daily_appsORDER BY msgs_365d DESCLIMIT 25),msgs AS (SELECTCOALESCE(NULLIF(TRIM(m.project), ''), 'unknown') AS project,COALESCE(DATE(m.ts_source), c.block_date) AS day,m.source_chain_key AS source_chain,m.destination_chain_key AS dest_chainFROM layerzero.messages mINNER JOIN dune.canaryprotocol.result_transaction_list_validated_by_lz_canary_dvn cON m.tx_hash_source = CAST(c.tx_hash AS varchar)AND ((c.chain = 'bnb' AND m.source_chain_key = 'bsc')OR (c.chain = 'berachain' AND m.source_chain_key = 'bera')OR (c.chain = 'avalanche_c' AND m.source_chain_key = 'avalanche')OR (c.chain = 'hyperevm' AND m.source_chain_key = 'hyperliquid')OR c.chain = m.source_chain_key)WHERE c.block_date < DATE '2026-07-09'AND c.block_date >= CURRENT_DATE - INTERVAL '365' DAYAND m.state = 'Success'UNION ALLSELECTCOALESCE(NULLIF(TRIM(m.project), ''), 'unknown') AS project,DATE(m._updated_at) AS day,m.source_chain_key AS source_chain,m.destination_chain_key AS dest_chainFROM layerzero.messages mWHERE m._updated_at >= GREATEST(DATE '2026-07-09', CURRENT_DATE - INTERVAL '365' DAY)AND m._updated_at < CURRENT_DATEAND m.state = 'Success'UNION ALLSELECTCOALESCE(NULLIF(TRIM(m.project), ''), 'unknown') AS project,COALESCE(DATE(m.ts_source), c.block_date) AS day,m.source_chain_key AS source_chain,m.destination_chain_key AS dest_chainFROM layerzero.messages mINNER JOIN dune.canaryprotocol.result_transaction_list_include_fee_paide_to_wbtc_canary_dvn cON m.tx_hash_source = CAST(c.tx_hash AS varchar)AND ((c.chain = 'bnb' AND m.source_chain_key = 'bsc')OR (c.chain = 'berachain' AND m.source_chain_key = 'bera')OR (c.chain = 'avalanche_c' AND m.source_chain_key = 'avalanche')