Query editor
WITH blocks_cu AS (SELECTr.blockslot AS slot,b.time,SUM(CASEWHEN NOT contains(t.transaction.message.accountkeys, 'Vote111111111111111111111111111111111111111')THEN t.meta.computeunitsconsumedELSE 0END) AS nonvote_computeunitsconsumed_total,SUM(CASEWHEN contains(t.transaction.message.accountkeys, 'Vote111111111111111111111111111111111111111')AND t.meta.computeunitsconsumed = 0THEN 3428 -- hardcode vote compute costWHEN contains(t.transaction.message.accountkeys, 'Vote111111111111111111111111111111111111111')THEN t.meta.computeunitsconsumedELSE 0END) AS vote_computeunitsconsumed_totalFROM solana.raw_0003 rJOIN unnest(transactions) t ON trueJOIN solana.blocks b ON b.slot = r.blockslotWHERE time > now() - interval '2' dayGROUP BY 1, 2),days AS (WITH time_seq AS (SELECT sequence(date_trunc('day', cast((now() - interval '2' day) AS timestamp)),date_trunc('day', cast(now() AS timestamp)),interval '1' day) AS time)SELECTtimes.timeFROM time_seqLEFT JOIN unnest(time) AS times(time) ON true),daily_cu AS (SELECTdate_trunc('day', time) AS day,avg(vote_computeunitsconsumed_total) AS avg_vote_cu_block,avg(nonvote_computeunitsconsumed_total) AS avg_nonvote_cu_block,count(*) AS blocks_in_dayFROM blocks_cuGROUP BY 1)SELECTd.time,48 * 1e6 AS compute_limit,COALESCE(cu.avg_vote_cu_block, 0) AS avg_vote_cu_block,COALESCE(cu.avg_nonvote_cu_block, 0) AS avg_nonvote_cu_block,COALESCE(cu.blocks_in_day, 0) AS blocks_in_dayFROM days dLEFT JOIN daily_cu cu ON d.time = cu.day