Daily Mined Blocks Ethereum Classic

daily_mined_blocks.sql

WITH greater_than_million AS (
  SELECT TIMESTAMP_TRUNC(timestamp, DAY) AS block_day,
  COUNT(*) AS gtm_blocks_mined
  FROM `bigquery-public-data.crypto_ethereum_classic.blocks` 
  WHERE gas_used > 1000000 
  GROUP BY block_day
),
total_blocks AS (
  SELECT TIMESTAMP_TRUNC(timestamp, DAY) AS day,
  COUNT(*) AS total_blocks_mined
  FROM `bigquery-public-data.crypto_ethereum_classic.blocks`
  GROUP BY day
),
combined_book AS (
  SELECT *
  FROM total_blocks tot
  LEFT JOIN greater_than_million gtm ON tot.day = gtm.block_day 
)
SELECT gtm_blocks_mined,
  total_blocks_mined,
  day 
FROM combined_book
ORDER BY day ASC