Query editor
WITH program_compute AS (SELECTdate_trunc('month', block_time) as month,split(log, ' ')[2] as program_id,sum(cast(split(log,' ')[4] as bigint)) as computed_units_spent,count(*) as invocationsFROM solana.transactions t,unnest(log_messages) as l(log)WHERE block_time >= date '2024-05-16'AND block_time < date '2024-06-01'AND success = trueAND log LIKE 'Program % consumed % of % compute units'GROUP BY 1, 2),program_names AS (SELECTprogram_id,min(namespace) as namespaceFROM solana.programsGROUP BY program_id),ranked_programs AS (SELECTmonth,program_id,computed_units_spent,invocations,row_number() OVER (PARTITION BY month ORDER BY computed_units_spent DESC) as rankFROM program_compute)SELECTrp.month,rp.program_id,pn.namespace as program_name,rp.computed_units_spent as total_compute,rp.invocations as total_invocations,rp.computed_units_spent / rp.invocations as avg_compute_per_invoke,rp.rankFROM ranked_programs rpLEFT JOIN program_names pn ON pn.program_id = rp.program_idWHERE rank <= 100ORDER BY month DESC, rank ASC;