Query editor
WITH combined_data AS (SELECTchain AS network,DATE_TRUNC('day', call_block_time) AS date,SUM(CASE WHEN call_success THEN 1 ELSE 0 END) AS successes,SUM(CASE WHEN call_success THEN 0 ELSE 1 END) AS fails,COUNT(*) AS totalFROM sushiswap_multichain.routeprocessor6_1_call_processrouteWHERE call_block_time >= DATE_ADD('day', - {{days}}, DATE_TRUNC('day', CURRENT_TIMESTAMP))AND call_block_time < DATE_TRUNC('day', CURRENT_TIMESTAMP)GROUP BY chain, DATE_TRUNC('day', call_block_time)UNION ALLSELECTchain AS network,DATE_TRUNC('day', call_block_time) AS date,SUM(CASE WHEN call_success THEN 1 ELSE 0 END) AS successes,SUM(CASE WHEN call_success THEN 0 ELSE 1 END) AS fails,COUNT(*) AS totalFROM sushiswap_multichain.routeprocessor6_1_call_processrouteWHERE call_block_time >= DATE_ADD('day', - {{days}}, DATE_TRUNC('day', CURRENT_TIMESTAMP))AND call_block_time < DATE_TRUNC('day', CURRENT_TIMESTAMP)GROUP BY chain, DATE_TRUNC('day', call_block_time)UNION ALLSELECTchain AS network,DATE_TRUNC('day', call_block_time) AS date,SUM(CASE WHEN call_success THEN 1 ELSE 0 END) AS successes,SUM(CASE WHEN call_success THEN 0 ELSE 1 END) AS fails,COUNT(*) AS totalFROM sushiswap_multichain.routeprocessor6_1_call_processrouteWHERE call_block_time >= DATE_ADD('day', - {{days}}, DATE_TRUNC('day', CURRENT_TIMESTAMP))AND call_block_time < DATE_TRUNC('day', CURRENT_TIMESTAMP)GROUP BY chain, DATE_TRUNC('day', call_block_time))SELECTnetwork,date,SUM(successes) AS successes,SUM(fails) AS fails,SUM(total) AS total,(SUM(successes) * 100.0 / SUM(total)) AS success_percentageFROM combined_dataGROUP BY network, dateORDER BY date DESC, network;