Cardano Throughput and Stake Pool Sizes
Description
Context: This data set contains information on the load and the number of active stake pools on the Cardano proof-of-stake blockchain platform between 29/07/2020 and 07/08/2021. The information was extracted from Cardano using the Cardano DB Sync tool. Content: - `date`: The date the measurements refer to - `avg_tps`: The average transactions per second observed during the 24 hour period - `cumulative_added`: The total cumulative number of stake pools added during this period and all previous periods - `cumulative_removed`: The total cumulative number of stake pools removed during this period and all previous periods - `total`: The total number of stake pools active during this period
Files
Steps to reproduce
Using the "Cardano DB Sync" tool (https://github.com/input-output-hk/cardano-db-sync), data can be extracted from the Cardano blockchain and saved in a local database. This database can subsequently be queried to produce the given output. To generate `avg_tps` data the following query can be used: ``` SELECT series::date AS day, (sum(tx_count) / 24 / 60 / 60) AS tps FROM generate_series(date '2020-07-29', date '2021-08-07', interval '1 day' day) AS g(series) LEFT JOIN block ON block.time::date = g.series::date GROUP BY 1 ORDER BY day; ``` To generate `cumulative_added` data the following query can be used: ``` SELECT series::date AS time, max(updates.ad) AS added FROM generate_series(date '2020-07-29', date '2021-08-07', interval '1 day' day) AS g(series) LEFT JOIN (SELECT pool_update.hash_id AS pid, min(b.time) AS tm, row_number() over (ORDER BY min(b.time)) AS ad FROM pool_update LEFT JOIN tx on pool_update.registered_tx_id = tx.id LEFT JOIN block b on tx.block_id = b.id GROUP BY pid ORDER BY tm) as updates ON updates.tm::date = g.series::date GROUP BY time ORDER BY time; ``` To generate `cumulative_removed` data the following query can be used: ``` SELECT series::date AS time, max(deletes.del) AS deleted FROM generate_series(date '2020-07-29', date '2021-08-07', interval '1 day' day) AS g(series) LEFT JOIN (SELECT pool_retire.hash_id AS pid, max(b.time) AS tm, row_number() over (ORDER BY min(b.time)) AS del FROM pool_retire LEFT JOIN tx on pool_retire.announced_tx_id = tx.id LEFT JOIN block b on tx.block_id = b.id GROUP BY pid ORDER BY tm) as deletes ON deletes.tm::date = g.series::date GROUP BY time ORDER BY time; ```