Query editor
WITH top_programs AS (SELECT DISTINCT program_idFROM dune.enki_labs.dataset_top_1000_programs_by_invocations_solana_monthly_2024WHERE from_iso8601_timestamp(month) = timestamp '2024-03-01'),filtered_txs AS (SELECTdate_trunc('day', block_time) as day,split(log, ' ')[2] as program_id,cast(split(log,' ')[4] as bigint) as computed_units_spent,cast(split(log,' ')[6] as bigint) as computed_units_allocated,block_slotFROM solana.transactions t,unnest(log_messages) as l(log)WHERE block_time >= date '2024-03-08'AND block_time < date '2024-03-16'AND success = trueAND log LIKE 'Program % consumed % of % compute units'AND split(log, ' ')[2] IN (SELECT program_id FROM top_programs)),daily_metrics AS (SELECTday,program_id,avg(computed_units_spent) as avg_cu_per_invoke,avg(computed_units_allocated) as avg_cu_allocated,avg(cast(computed_units_spent as double)/nullif(cast(computed_units_allocated as double), 0)) as cu_efficiency_ratio,sum(computed_units_spent) as total_compute,count(*) as times_invoked,count(distinct block_slot) as unique_blocks_usedFROM filtered_txsGROUP BY 1, 2),daily_blocks AS (SELECTdate_trunc('day', time) as day,COUNT(DISTINCT slot) as blocks_per_dayFROM solana.blocksWHERE time >= date '2024-03-08'AND time < date '2024-03-16'GROUP BY 1),daily_totals AS (SELECTday,sum(total_compute) as total_cu_all_programs,sum(times_invoked) as total_invocationsFROM daily_metricsGROUP BY 1)SELECTdm.day,COALESCE(db.blocks_per_day, 0) as blocks_in_day,COALESCE(p.namespace, dm.program_id) as program_name,dm.program_id,