bitcoin_block_time_queries

-- 1 - general
select
  to_timestamp(max(b1.block_nTime)) as last_block_date,
  count(*) as blocks_in_db,
  avg(round((b1.block_ntime - b2.block_ntime))) as avg_blocktime_seconds,
  avg(round((b1.block_ntime - b2.block_ntime) / 60)) as avg_blocktime_minutes
from block b1
  inner join chain_candidate cc1 on b1.block_id = cc1.block_id
  inner join block b2 on b2.block_id = b1.prev_block_id
  inner join chain_candidate cc2 on b2.block_id = cc2.block_id
where
  cc1.chain_id = 1 and cc1.in_longest=1
  --cc2.chain_id = 1 and cc1.in_longest=1
;


-- 2 - single block times by minute histogram
Copy (
select
  case when round((b1.block_ntime - b2.block_ntime) / 60) <= -20 then -20 else case when round((b1.block_ntime - b2.block_ntime) / 60) >= 60 then 60 else round((b1.block_ntime - b2.block_ntime) / 60) end end as bin,
  count(*)
from block b1
  inner join chain_candidate cc1 on b1.block_id = cc1.block_id
  inner join block b2 on b2.block_id = b1.prev_block_id
  inner join chain_candidate cc2 on b2.block_id = cc2.block_id
where
  cc1.chain_id = 1 and cc1.in_longest=1 and
  cc2.chain_id = 1 and cc2.in_longest=1
group by bin
) to '/tmp/block_times.csv' with CSV;
--having round((b1.block_ntime - b4.block_ntime) / 60)


-- 3 - sequence of 3 blocks in a row
Copy (
select
  case when round((b1.block_ntime - b4.block_ntime) / 60) <= -20 then -20 else case when round((b1.block_ntime - b4.block_ntime) / 60) >= 127 then 127 else round((b1.block_ntime - b4.block_ntime) / 60) end end as bin,
  count(*)
from block b1
  inner join chain_candidate cc1 on b1.block_id = cc1.block_id
  inner join block b2 on b2.block_id = b1.prev_block_id
  inner join block b3 on b3.block_id = b2.prev_block_id
  inner join block b4 on b4.block_id = b3.prev_block_id
where
  cc1.chain_id = 1 and cc1.in_longest=1
  group by bin
--having round((b1.block_ntime - b4.block_ntime) / 60)
) to '/tmp/sequence_of_3_times.csv' with CSV;


-- 3 - sequence of 4 blocks in a row
Copy (
select
  case when round((b1.block_ntime - b5.block_ntime) / 60) <= -20 then -20 else case when round((b1.block_ntime - b5.block_ntime) / 60) >= 127 then 127 else round((b1.block_ntime - b5.block_ntime) / 60) end end as bin,
  count(*)
from block b1
  inner join chain_candidate cc1 on b1.block_id = cc1.block_id
  inner join block b2 on b2.block_id = b1.prev_block_id
  inner join block b3 on b3.block_id = b2.prev_block_id
  inner join block b4 on b4.block_id = b3.prev_block_id
  inner join block b5 on b5.block_id = b4.prev_block_id
where
  cc1.chain_id = 1 and cc1.in_longest=1
  group by bin
--having round((b1.block_ntime - b4.block_ntime) / 60)
) to '/tmp/sequence_of_4_times.csv' with CSV;