-- ALGORAND BLOCKCHAIN BLOCK PERFORMANCE /* -- What’s the average time between block and how has this changed over time? select DATE_TRUNC('day', block_timestamp) AS date, avg(dif) AS average_blocktime_daily, max(max(dif)) over() as max_blocktime, min(min(dif)) over() as min_blocktime, sum(sum(dif)) over() / sum(count(dif)) over() as average_blocktime from (select block_id, block_timestamp, datediff(second, block_timestamp, lag(block_timestamp, -1) over (order by block_id)) as dif from algorand.block) as alg group by date having average_blocktime_daily < 5 and average_blocktime_daily > 4 order by date; */ /* -- What’s the max recorded time between two blocks? select top 1 DATE_TRUNC('day', block_timestamp) AS date, dif as max_blocktime from (select block_id, block_timestamp, datediff(second, block_timestamp, lag(block_timestamp, -1) over (order by block_id)) as dif from algorand.block) as alg where dif is not null order by dif desc -- What’s the min recorded time between two blocks? select top 1 DATE_TRUNC('day', block_timestamp) AS date, dif as max_blocktime from (select block_id, block_timestamp, datediff(second, block_timestamp, lag(block_timestamp, -1) over (order by block_id)) as dif from algorand.block) as alg order by dif asc */ /* -- Es wurden Sekunden benutt, weil es keine Millisekunden gibt, wie die query beweist select right(date_part(epoch_millisecond, block_timestamp), 3) as ms, count(block_id) from algorand.block where block_timestamp >= '2019-06-11' group by ms */ /* -- Block 4 (wurde für den Grapfen verwendet) select tx_type_name as transaction_type, block_id , count(*) as transactions, sum(transactions) over() as total_transactions from algorand.transactions where block_id = ( select top 1 block_id from algorand.transactions GROUP BY block_id order by count(*) desc) Group by block_id, tx_type_name */ /* -- wann wurde dieser block erstellt select block_timestamp from algorand.block where block_id = '17758402'; */ /* -- Beweis, dass in der Block Tabelle Zeilen pro Block und aus diesem Grund Block_id sich nciht wiederholt SELECT block_id FROM algorand.block GROUP BY block_id HAVING COUNT(*) > 1; -- Beweis, dass in der Transactions Tabelle Zeilen pro Transaktion sind und aus diesem Grund sich die Block_id wiederholt SELECT block_id FROM algorand.transactions GROUP BY block_id HAVING COUNT(*) > 1; SELECT tx_id FROM algorand.transactions GROUP BY tx_id HAVING COUNT(*) > 1; */ /* ERSTER ANSATZ; SOLL JEDOCH DIREKT AUF TAGEN AGGREGIERT WERDEN -- Has the number of transactions per block increased as network adoption increased (Chart the number of transactions -- per block over time)? select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2019-06-15 15:53:37.000' and date < '2019-06-20' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2019-07-01' and date < '2019-07-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2019-08-01' and date < '2019-08-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2019-09-01' and date < '2019-09-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2019-10-01' and date < '2019-10-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2019-11-01' and date < '2019-11-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2019-12-01' and date < '2019-12-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-01-01' and date < '2020-01-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-02-01' and date < '2020-02-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-03-01' and date < '2020-03-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-04-01' and date < '2020-04-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-05-01' and date < '2020-05-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-06-01' and date < '2020-06-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-07-01' and date < '2020-07-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-08-01' and date < '2020-08-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-09-01' and date < '2020-09-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-10-01' and date < '2020-10-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-11-01' and date < '2020-11-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2020-12-01' and date < '2020-12-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-01-01' and date < '2021-01-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-02-01' and date < '2021-02-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-03-01' and date < '2021-03-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-04-01' and date < '2021-04-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-05-01' and date < '2021-05-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-06-01' and date < '2021-06-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-07-01' and date < '2021-07-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-08-01' and date < '2021-08-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-09-01' and date < '2021-09-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-10-01' and date < '2021-10-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-11-01' and date < '2021-11-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2021-12-01' and date < '2021-12-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2022-01-01' and date < '2022-01-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2022-02-01' and date < '2022-02-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2022-03-01' and date < '2022-03-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2022-04-01' and date < '2022-04-05' order by date; select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2022-05-01' and date < '2022-05-05' order by date; */ -- Has the number of transactions per block increased as network adoption increased (Chart the number of transactions -- per block over time)? select DATE_TRUNC('second', block_timestamp) AS date, block_id, count(*) total_transaction from algorand.transactions GROUP BY date, block_id having date > '2019-06-15 15:53:37.000' and date < '2019-06-20' order by date;

Query	Creator	Created
Volume of ALGO transferred from CEXs daily	Fatih	Apr 16, 2022
Untitled Query	Fatih	May 4, 2022
ALGORAND BLOCKCHAIN BLOCK PERFORMANCE	Fatih	Apr 30, 2022
Volume of ALGO transferred to CEXs daily	Fatih	Apr 16, 2022
Dex swaps Tinyman	Fatih	Jun 21, 2022
Block - Frage 5	Fatih	Jul 26, 2022
Algorand Fundaction new CEO Stacri	Fatih	May 8, 2022

Fatih