-- Exact daily mint and burn, read from block headers.
-- total_supply only rises at an epoch's first block (validator rewards) and only falls when fees are burnt,
-- so summing the positive and negative block-to-block differences separates gross issuance from burn.
-- LAG is partitioned by month to stay inside sandbox memory limits; one boundary diff per month is dropped.
WITH b AS (
  SELECT block_date, block_height, total_supply, gas_price,
         total_supply - LAG(total_supply) OVER (PARTITION BY DATE_TRUNC(block_date, MONTH) ORDER BY block_height) AS d
  FROM `bigquery-public-data.crypto_near_mainnet_us.blocks`
  WHERE block_date >= '2020-07-01'
)
SELECT block_date,
       COUNT(*) AS blocks,
       MAX(block_height) AS last_height,
       MAX_BY(total_supply, block_height) / 1e24 AS supply_near,
       SUM(IF(d > 0, d, 0)) / 1e24 AS minted_near,
       SUM(IF(d < 0, -d, 0)) / 1e24 AS burned_near,
       COUNTIF(d > 0) AS mint_events,
       AVG(gas_price) AS gas_price_yocto
FROM b GROUP BY block_date ORDER BY block_date