WITH eth_bridgers as ( SELECT DISTINCT event_inputs:accountId as near_wallet FROM ethereum.core.fact_event_logs l WHERE origin_to_address = '0x23ddd3e3692d1861ed57ede224608875809e127f' AND contract_name = 'ERC20Locker' AND event_name ILIKE 'locked' AND (near_wallet ILIKE '%.near' OR LEFT(near_wallet, 2) <> '0x') AND tx_status ILIKE 'SUCCESS' ), aurora_bridgers as ( SELECT DISTINCT REGEXP_SUBSTR(logs[0], 'from aurora to (.+)' , 1, 1, 'ei') as near_wallet FROM near.core.fact_receipts r JOIN near.core.fact_transactions t ON r.tx_hash = t.tx_hash WHERE logs[0] ILIKE 'transfer % from aurora %' AND t.tx_signer = 'relay.aurora' AND tx_receiver = 'aurora' AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL ), new_wallets as ( SELECT TRY_PARSE_JSON(args):new_account_id::string as wallet, DATE(block_timestamp) as day_of_creation, CASE WHEN e.near_wallet IS NOT NULL OR a.near_wallet IS NOT NULL THEN 'Rainbow Bridge User' ELSE 'Native User' END as label FROM near.core.fact_actions_events_function_call f LEFT JOIN eth_bridgers e ON TRY_PARSE_JSON(args):new_account_id::string = e.near_wallet LEFT JOIN aurora_bridgers a ON TRY_PARSE_JSON(args):new_account_id::string = a.near_wallet WHERE method_name ILIKE 'create\\_account%' escape '\\' AND tx_hash NOT IN ( SELECT DISTINCT r.tx_hash FROM near.core.fact_receipts r JOIN near.core.fact_actions_events_function_call f ON f.tx_hash = r.tx_hash WHERE method_name ILIKE 'create\\_account%' escape '\\' AND REGEXP_SUBSTR(status_value, 'Failure') IS NOT NULL ) ), aggregate as ( SELECT tx_signer as wallet, label, day_of_creation, COUNT(DISTINCT tx_hash) as total_txs, COUNT(DISTINCT DATE(block_timestamp)) as days_active, MIN(DATE(block_timestamp)) as first_tx, MAX(DATE(block_timestamp)) as last_tx, DATEDIFF('day', first_tx, CURRENT_DATE()) as wallet_age FROM near.core.fact_transactions t JOIN new_wallets ON tx_signer = wallet GROUP BY 1, 2, 3 ) SELECT /*CASE WHEN total_txs <= 5 THEN '1-5 TXs' WHEN total_txs <= 10 THEN '6-10 TXs' WHEN total_txs <= 15 THEN '11-15 TXs' WHEN total_txs <= 20 THEN '16-20 TXs' WHEN total_txs <= 30 THEN '21-30 TXs' WHEN total_txs <= 40 THEN '31-40 TXs' WHEN total_txs <= 50 THEN '31-50 TXs' WHEN total_txs <= 60 THEN '51-60 TXs' WHEN total_txs <= 70 THEN '61-70 TXs' WHEN total_txs <= 80 THEN '71-80 TXs' WHEN total_txs <= 90 THEN '81-90 TXs' WHEN total_txs <= 100 THEN '91-100 TXs' WHEN total_txs <= 200 THEN '101-200 TXs' WHEN total_txs <= 300 THEN '201-300 TXs' WHEN total_txs <= 400 THEN '301-400 TXs' WHEN total_txs <= 500 THEN '401-500 TXs' WHEN total_txs <= 600 THEN '501-600 TXs' WHEN total_txs <= 700 THEN '601-700 TXs' WHEN total_txs <= 800 THEN '701-800 TXs' WHEN total_txs <= 900 THEN '801-900 TXs' WHEN total_txs <= 1000 THEN '901-1000 TXs' WHEN total_txs <= 5000 THEN '1001-5000 TXs' WHEN total_txs <= 10000 THEN '5001-10,000 TXs' WHEN total_txs <= 100000 THEN '10,001-100,000 TXs' ELSE '>100,000 TXs ' END as label, */total_txs, COUNT(DISTINCT wallet) FROM aggregate GROUP BY 1 ORDER BY 1

The Journeymen