Query editor
WITH rem AS (SELECT time_uk AS t, app_label AS app, chain, remote_eid_name AS dst, oapp, tx_hash, layerzero_scan_linkFROM dune.canaryone.result_cpops_change_logWHERE test_flag = '' AND change = 'REMOVED' AND time_uk >= now() - INTERVAL '180' DAY),adds AS (SELECT time_uk AS t, chain, remote_eid_name AS dst, oappFROM dune.canaryone.result_cpops_change_log WHERE change = 'ADDED' AND time_uk >= now() - INTERVAL '180' DAY),r AS (SELECT rem.*, min(a.t) AS readded_tFROM rem LEFT JOIN adds a ON a.oapp = rem.oapp AND a.chain = rem.chain AND a.dst = rem.dst AND a.t > rem.tGROUP BY 1,2,3,4,5,6,7),ro AS (SELECT DISTINCT oapp, chain, dst FROM rem),lz AS (SELECT ro.oapp, ro.chain, ro.dst, m.ts_source, m.primary_keyFROM layerzero.messages m JOIN ro ON ro.oapp = lower(m.source_contract) AND ro.chain = m.source_chain_key AND ro.dst = m.destination_chain_keyWHERE m.ts_source >= now() - INTERVAL '271' DAY),cn AS (SELECT ro.oapp, ro.chain, ro.dst, c.ts_source, c.usd_valueFROM dune.canaryone.result_canary_canonical_messages c JOIN ro ON ro.oapp = lower(c.source_oapp) AND ro.chain = c.source_chain AND ro.dst = c.destination_chainWHERE c.block_date >= current_date - INTERVAL '271' DAY),lz_agg AS (SELECT r.t, r.oapp, r.chain, r.dst,count(DISTINCT IF(lz.ts_source >= r.t - INTERVAL '90' DAY AND lz.ts_source < r.t, lz.primary_key)) AS lz_msgs_90d_before,count(DISTINCT IF(lz.ts_source >= r.t AND lz.ts_source < coalesce(r.readded_t, now()), lz.primary_key)) AS lz_msgs_after,max(IF(lz.ts_source >= r.t AND lz.ts_source < coalesce(r.readded_t, now()), lz.ts_source)) AS last_msg_afterFROM r LEFT JOIN lz ON lz.oapp = r.oapp AND lz.chain = r.chain AND lz.dst = r.dstGROUP BY 1,2,3,4),cn_agg AS (SELECT r.t, r.oapp, r.chain, r.dst,sum(IF(cn.ts_source >= r.t - INTERVAL '30' DAY AND cn.ts_source < r.t, cn.usd_value)) AS lost_usd_30d_before,count(IF(cn.ts_source >= r.t - INTERVAL '30' DAY AND cn.ts_source < r.t, 1)) AS canary_msgs_30d_before,count(IF(cn.ts_source >= r.t AND cn.ts_source < coalesce(r.readded_t, now()), 1)) AS canary_msgs_afterFROM r LEFT JOIN cn ON cn.oapp = r.oapp AND cn.chain = r.chain AND cn.dst = r.dstGROUP BY 1,2,3,4)SELECT format_datetime(at_timezone(r.t, 'Europe/London'), 'yyyy-MM-dd HH:mm') AS removed_uk, r.t AS removed_at,r.app, r.chain, r.dst AS to_chain, r.oapp,CASE WHEN r.readded_t IS NOT NULL THEN '1 Re-added Canary later'WHEN cn_agg.canary_msgs_after > 0 THEN '2 Still Canary-verified traffic'WHEN lz_agg.lz_msgs_after > 0 THEN '3 Switched away (kept sending, other DVNs)'WHEN r.t >= now() - INTERVAL '7' DAY THEN '4 Too early to tell (<7d)'WHEN lz_agg.lz_msgs_90d_before > 0 THEN '5 Route went quiet (no messages since)'ELSE '6 Route unused (no messages 90d before or since)' END AS outcome,lz_agg.lz_msgs_90d_before, lz_agg.lz_msgs_after,format_datetime(at_timezone(lz_agg.last_msg_after, 'Europe/London'), 'yyyy-MM-dd') AS last_msg_after_uk,cn_agg.canary_msgs_30d_before, coalesce(cn_agg.lost_usd_30d_before, 0) AS lost_usd_30d_before, cn_agg.canary_msgs_after,format_datetime(at_timezone(r.readded_t, 'Europe/London'), 'yyyy-MM-dd') AS readded_uk,r.layerzero_scan_linkFROM rJOIN lz_agg ON lz_agg.t = r.t AND lz_agg.oapp = r.oapp AND lz_agg.chain = r.chain AND lz_agg.dst = r.dstJOIN cn_agg ON cn_agg.t = r.t AND cn_agg.oapp = r.oapp AND cn_agg.chain = r.chain AND cn_agg.dst = r.dstORDER BY r.t DESC