-- How the largest signer left: its daily transactions around April 2026, and the mass account deletions in October.
SELECT 'signer_daily' AS series, block_date, signer_account_id AS who, COUNT(*) AS txs
FROM `bigquery-public-data.crypto_near_mainnet_us.transactions`
WHERE block_date BETWEEN '2026-03-28' AND '2026-04-12' AND signer_account_id = 'hotwallet.kaiching'
GROUP BY 1, 2, 3
UNION ALL
SELECT 'delete_account', block_date, 'kaiching accounts' AS who, COUNT(*) AS txs
FROM `bigquery-public-data.crypto_near_mainnet_us.transaction_actions`
WHERE block_date BETWEEN '2026-09-25' AND '2026-10-15' AND action_kind = 'DELETE_ACCOUNT' AND signer_account_id LIKE '%kaiching'
GROUP BY 1, 2, 3
ORDER BY 1, 2