Query editor
-- SELECT * from solana.raw_0003 limit 10withblocks_cu as (selectr.blockslot as slot, b.time, sum(case when not contains(t.transaction.message.accountkeys,'Vote111111111111111111111111111111111111111') then t.meta.computeunitsconsumedelse 0end) as nonvote_computeunitsconsumed_total, sum(case when contains(t.transaction.message.accountkeys,'Vote111111111111111111111111111111111111111') and t.meta.computeunitsconsumed = 0then 3428 --hardcode vote compute cost. Source: https://github.com/solana-labs/solana/blob/master/cost-model/src/transaction_cost.rswhen 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.blockslot-- LEFT JOIN unnest(transaction.signatures) as tx(id) ON trueWHERE time > now() - interval '{{hours}}' hour-- AND blockslot = 229394527-- AND tx.id = 'u2V4YynJsktLbcj8rSo7Aih66HU59h4eEAY9LJmMjti3jaZBcMCVSJkYdkSxhekN8Dn5qKmeX979FXzvdMmpkgE'GROUP BY 1,2), minutes as (with time_seq as (SELECTsequence(date_trunc('minute', cast((now() - interval '{{hours}}' hour) as timestamp)), date_trunc('minute', cast(now() as timestamp)), interval '1' minute) as time)SELECTtimes.timeFROM time_seqLEFT JOIN unnest(time) as times(time) on true --this is just doing explode like in spark sql), minutes_cu as (SELECTdate_trunc('minute', time) as minute, avg(vote_computeunitsconsumed_total) as avg_vote_cu_block, max(vote_computeunitsconsumed_total) as max_vote_cu_block, avg(nonvote_computeunitsconsumed_total) as avg_nonvote_cu_block, max(nonvote_computeunitsconsumed_total) as max_nonvote_cu_blockFROM blocks_cugroup by 1)SELECTm.time, 48*1e6 as compute_limit, COALESCE(cu.avg_vote_cu_block,0) as avg_vote_cu_block, COALESCE(cu.max_vote_cu_block,0) as max_vote_cu_block, COALESCE(cu.avg_nonvote_cu_block,0) as avg_nonvote_cu_block, COALESCE(cu.max_nonvote_cu_block,0) as max_nonvote_cu_blockFROM minutes m