Count the number of fully-shielded Zcash transactions per month

zcash-fully-shielded-per-month.sql

/* Count the number of fully-shielded Zcash transactions per month */

SELECT FORMAT_DATE("%E4Y-%m", Date(block_timestamp)) as month, count(distinct `hash`) as fully_shielded_tx FROM
(
  SELECT /* fully shielded transactions with 0 fee */
    `hash`,
    block_timestamp,
    fee
  FROM
    `bigquery-public-data.crypto_zcash.transactions`
  WHERE
    is_coinbase = FALSE
    AND input_count = 0
    AND output_count = 0

  UNION DISTINCT

  SELECT /* fully shielded transactions with nonzero fee */
    `hash`,
    block_timestamp,
    fee
  FROM
    `bigquery-public-data.crypto_zcash.transactions`,
    UNNEST(inputs) AS i
  WHERE
    is_coinbase = FALSE
    AND input_count = 1
    AND output_count = 0
    AND i.type = "shielded"
    AND i.value = fee
)
GROUP BY 1 ORDER BY 1