Query editor
withminutes 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), failures as (SELECTdate_trunc('minute', time) as minute, avg(if(is_nan(cast(failed_non_vote_transactions as double)/cast(total_non_vote_transactions as double)), 0,cast(failed_non_vote_transactions as double)/cast(total_non_vote_transactions as double))) as failure_rate, avg(total_non_vote_transactions) as avg_non_vote_txs, avg(total_vote_transactions) as avg_vote_txs, count(*) as blocks_per_minuteFROM solana.blocksWHERE time > now() - interval '{{hours}}' hourgroup by 1), fees as (SELECTdate_trunc('minute', block_time) as minute, sum(case when success = false then fee/1e9 else 0 end) as failed_fees, sum(case when success = true then fee/1e9 else 0 end) as success_feesFROM solana.transactionsWHERE block_time > now() - interval '{{hours}}' hourgroup by 1)SELECTm.time, COALESCE(1-f.failure_rate,0) as success_rate, COALESCE(f.failure_rate,0) as failure_rate, COALESCE(fees.success_fees,0) as success_fees, COALESCE(fees.failed_fees,0) as failed_fees, COALESCE(f.avg_vote_txs,0) as avg_vote_txs, COALESCE(f.avg_non_vote_txs,0) as avg_non_vote_txs, COALESCE(f.blocks_per_minute,0) as blocks_per_minute, p.price as sol_priceFROM minutes mLEFT JOIN fees ON m.time = fees.minuteLEFT JOIN failures f ON m.time = f.minuteLEFT JOIN prices.usd p ON p.blockchain = 'solana' and p.symbol = 'SOL' and p.minute = m.timeorder by m.time desc