zcash-shielded-transaction-censorship

01_num_shielded_txs.sql

-- counts the number of shielded zcash transactions sent between 2019-01-01 to 2019-05-29
SELECT COUNT(*) AS num_shielded_txs FROM `bigquery-public-data.crypto_zcash.transactions` AS zec_txs
WHERE
  zec_txs.is_coinbase = FALSE
  AND zec_txs.block_timestamp >= '2019-01-01'
  AND zec_txs.block_timestamp <= '2019-05-29'
  AND (zec_txs.input_count = 0
    OR zec_txs.output_count = 0
    OR EXISTS (SELECT 1 FROM UNNEST(zec_txs.inputs) WHERE type = 'shielded')
    OR EXISTS (SELECT 1 FROM UNNEST(zec_txs.outputs) WHERE type = 'shielded'))
               
-- result: 86,849 shielded transactions

02_f2pool_blocks.sql

-- selects all blocks mined by f2pool between 2019-01-01 to 2019-05-29
SELECT block_hash FROM `bigquery-public-data.crypto_zcash.transactions` as zec_txs
WHERE zec_txs.is_coinbase = TRUE
  AND zec_txs.outputs[OFFSET(0)].addresses[OFFSET(0)] LIKE 't1aZvxRLCGVeMPFXvqfnBgHVEbi4c6g8MVa'
  AND zec_txs.block_timestamp >= '2019-01-01'
  AND zec_txs.block_timestamp <= '2019-05-29'

-- result: 14,084 blocks mined by f2pool

03_txs_in_f2pool_blocks.sql

-- counts the number of transactions sent between 2019-01-01 to 2019-05-29 included in blocks mined by f2pool
WITH f2pool_blocks AS (
  SELECT block_hash as f2_block_hash FROM `bigquery-public-data.crypto_zcash.transactions` as zec_txs
  WHERE zec_txs.is_coinbase = TRUE
  AND zec_txs.outputs[OFFSET(0)].addresses[OFFSET(0)] LIKE 't1aZvxRLCGVeMPFXvqfnBgHVEbi4c6g8MVa'
  AND zec_txs.block_timestamp >= '2019-01-01'
  AND zec_txs.block_timestamp <= '2019-05-29'
) 
SELECT 
  COUNT(*)
FROM
  `bigquery-public-data.crypto_zcash.transactions` AS zec_txs
INNER JOIN f2pool_blocks ON zec_txs.block_hash = f2pool_blocks.f2_block_hash

-- result: 35,712 transactions

04_f2pool_shielded_txs.sql

-- selects all shielded transactions sent between 2019-01-01 to 2019-05-29 included in blocks mined by f2pool
WITH f2pool_blocks AS (
  SELECT block_hash AS f2_block_hash
  FROM `bigquery-public-data.crypto_zcash.transactions`
  WHERE is_coinbase = TRUE
  AND outputs[OFFSET(0)].addresses[OFFSET(0)] LIKE 't1aZvxRLCGVeMPFXvqfnBgHVEbi4c6g8MVa'
  AND block_timestamp >= '2019-01-01'
  AND block_timestamp <= '2019-05-29'
)
SELECT 
  zec_txs.block_timestamp,
  zec_txs.hash,
  zec_txs.block_hash,
  zec_txs.input_count,
  zec_txs.output_count,
  (SELECT COUNT(*) FROM UNNEST(zec_txs.inputs) WHERE type = 'shielded') as shielded_inputs,
  (SELECT COUNT(*) FROM UNNEST(zec_txs.outputs) WHERE type = 'shielded') as shielded_outputs
FROM
  `bigquery-public-data.crypto_zcash.transactions` AS zec_txs
INNER JOIN f2pool_blocks ON zec_txs.block_hash = f2pool_blocks.f2_block_hash
WHERE
  zec_txs.is_coinbase = FALSE
  AND (zec_txs.input_count = 0
    OR zec_txs.output_count = 0
    OR EXISTS (SELECT 1 FROM UNNEST(zec_txs.inputs) WHERE type = 'shielded')
    OR EXISTS (SELECT 1 FROM UNNEST(zec_txs.outputs) WHERE type = 'shielded'))

-- result: 120 transactions

05_data.txt

See https://gist.github.com/ldub/4ca2c91c35a98c5a02443cc39fb18c02