genesis_non_movers.sql

WITH
genesis AS (
  SELECT to_address AS address, SUM(value / 1e18) AS genesis_amount
  FROM `bigquery-public-data.crypto_ethereum.traces` AS tr
  WHERE tr.trace_type = 'genesis'
  GROUP BY 1
),
genesis_out AS (
  SELECT
      genesis.address,
      genesis.genesis_amount,
      COUNT(DISTINCT tr.transaction_hash) AS num_tx_out
  FROM genesis
  LEFT JOIN `bigquery-public-data.crypto_ethereum.traces` AS tr
  ON genesis.address = tr.from_address
  AND tr.value > 0
  GROUP BY 1, 2
)
SELECT
  COUNT(*) AS wallets_with_no_outgoing_tx,
  SUM(genesis_amount) AS eth_by_non_movers
FROM genesis_out
WHERE num_tx_out = 0

-- Result Apr 5, 2020 11:15:00 UTC:
-- wallets_with_no_outgoing_tx: 860
-- eth_by_non_movers: 2,682,476.903