Cardano chain load

cardano_load.sql

-- Cardano load calculation

-- Note: Protocol parameters are hardcoded to simplify understanding.
-- In practice, parameters should be retrieved for each block to get
-- exact values after parameter changes.

-- 24 hours load
SELECT SUM(load)/COUNT(id) FROM (SELECT block.id,GREATEST(block.size::decimal/90112,SUM(unit_steps)::decimal/20000000000, SUM(unit_mem)::decimal/62000000) AS load FROM block join tx ON tx.block_id=block.id JOIN redeemer ON redeemer.tx_id=tx.id WHERE time > (NOW() AT TIME ZONE 'UTC' - INTERVAL '1 DAY') GROUP BY block.id) s;

-- 1 hour load
SELECT SUM(load)/COUNT(id) FROM (SELECT block.id,GREATEST(block.size::decimal/90112,SUM(unit_steps)::decimal/20000000000, SUM(unit_mem)::decimal/62000000) AS load FROM block join tx ON tx.block_id=block.id JOIN redeemer ON redeemer.tx_id=tx.id WHERE time > (NOW() AT TIME ZONE 'UTC' - INTERVAL '1 HOUR') GROUP BY block.id) s;

-- 5 minutes load
SELECT SUM(load)/COUNT(id) FROM (SELECT block.id,GREATEST(block.size::decimal/90112,SUM(unit_steps)::decimal/20000000000, SUM(unit_mem)::decimal/62000000) AS load FROM block join tx ON tx.block_id=block.id JOIN redeemer ON redeemer.tx_id=tx.id WHERE time > (NOW() AT TIME ZONE 'UTC' - INTERVAL '5 MINUTE') GROUP BY block.id) s;