Query editor
with raw_eth_deposits as (-- Need to unnest, since you can deposit multiple tokens-- TX: 0xd410fa68460501dc98a55eed7568fdfa8cad2b76f427d7aebc7f4102a3db2b98SELECT time, token_address, token_amount, call_tx_hashFROM (SELECT date_trunc('day', call_block_time) as time, json_value(json_query(unique_deposit, 'lax $.token' OMIT quotes), 'lax $.tokenAddress' RETURNING VARCHAR(42)) as token_address, CAST(JSON_QUERY(unique_deposit, 'lax $.value') as decimal(38, 0)) as token_amount, deposits.call_tx_hashFROM railgun_ethereum.RailgunLogic_call_generateDeposit as deposits, unnest(_notes) as all_deposits(unique_deposit)WHERE deposits.call_success) as deposits-- JOIN erc20_ethereum.evt_Transfer as transfers on-- deposits.call_tx_hash = transfers.evt_tx_hash and-- deposits.token_address = transfers.contract_address and-- "to" = 0xfa7093cdd9ee6932b4eb2c9e1cde7ce00b1fa4b9), raw_eth_withdraws as (SELECT time, token_address, token_amount, call_tx_hash-- , txs."from" as userFROM (SELECT date_trunc('day', call_block_time) as time, JSON_QUERY(JSON_QUERY(json_query(ELEMENT_AT("_transactions", -1), 'lax $.withdrawPreimage' OMIT QUOTES), 'lax $.token' OMIT QUOTES), 'lax $.tokenAddress' OMIT QUOTES) AS token_address, CAST(JSON_QUERY(json_query(ELEMENT_AT("_transactions", -1), 'lax $.withdrawPreimage' OMIT QUOTES), 'lax $.value') as DECIMAL(38, 0)) as token_amount, call_tx_hash-- , _transactions-- , as tokensFROM railgun_ethereum.RailgunLogic_call_transactWHERE call_success) as withdraws-- JOIN-- ethereum.transactions as txs on withdraws.call_tx_hash = txs.hash-- WHERE token_amount <> 0), new_raw_eth_deposits as (-- ShieldsSELECT date_trunc('day', logs.block_time) as time, CONCAT(0x, SUBSTRING(logs."data" FROM 27 + 64 * 7 FOR 40)) AS token_address, (bytearray_to_decimal(CONCAT(0x, SUBSTRING(logs."data" FROM 27 + 64 * 9 + 8 FOR 32)))/0.9975) AS token_amount-- , txs."from" as user, logs.tx_hashFROM ethereum.logs as logs-- JOIN-- ethereum.transactions AS txs on logs.tx_hash = txs.hashWHERE-- New Contractlogs.contract_address = 0xfa7093cdd9ee6932b4eb2c9e1cde7ce00b1fa4b9 ANDlogs.topic1 = 0xc3821e11e71307afd1d94a490660178ff37aefdd3c0514e5dd08937bd7024f34UNION ALL-- New Shield TopicSELECTdate_trunc('day', block_time) as time, bytearray_substring(data, 1 + 12 + 32 * 8, 20) AS token_address, bytearray_to_decimal(bytearray_substring(data, 1 + 12 + 32 * 10 + 4, 16))/0.9975 AS token_amount, tx_hashFROM ethereum.logs as logsWHERE-- New Contractlogs.contract_address = 0xfa7093cdd9ee6932b4eb2c9e1cde7ce00b1fa4b9 ANDlogs.topic0 = 0x3a5b9dc26075a3801a6ddccf95fec485bb7500a91b44cec1add984c21ee6db3b