Query editor
WITH raw AS (SELECTblock_time,block_number,tx_hash,topic0,varbinary_substring(topic1, 13, 20) AS user_address,varbinary_to_uint256(topic2) AS pid,varbinary_to_uint256(varbinary_substring(data, 1, 32)) AS amountFROM ethereum.logsWHERE block_date BETWEEN DATE '2021-03-20' AND DATE '2021-06-30'AND contract_address = 0x0feccb11c5b61b3922c511d0f002c0b72d770dceAND topic0 IN (0x36af321ec8d3c75236829c5317affd40ddb308863a1236d2d277a4025cccee1e,0xf279e6a1f5e320cca91135676d9cb6e44ca8a08c0b88342bcdb1144f6511b568)), deposits AS (SELECT user_address, pid, SUM(CAST(amount AS int256)) AS deposited_raw, COUNT(*) AS deposit_events, MIN(block_number) AS first_blockFROM rawWHERE topic0 = 0x36af321ec8d3c75236829c5317affd40ddb308863a1236d2d277a4025cccee1eGROUP BY 1, 2), withdrawals AS (SELECT user_address, pid, SUM(CAST(amount AS int256)) AS withdrawn_raw, COUNT(*) AS withdraw_events, MAX(block_number) AS last_withdraw_blockFROM rawWHERE topic0 = 0xf279e6a1f5e320cca91135676d9cb6e44ca8a08c0b88342bcdb1144f6511b568GROUP BY 1, 2), net AS (SELECTCOALESCE(d.user_address, w.user_address) AS user_address,COALESCE(d.pid, w.pid) AS pid,COALESCE(d.deposited_raw, 0) AS deposited_raw,COALESCE(w.withdrawn_raw, 0) AS withdrawn_raw,COALESCE(d.deposited_raw, 0) - COALESCE(w.withdrawn_raw, 0) AS net_raw,COALESCE(d.deposit_events, 0) AS deposit_events,COALESCE(w.withdraw_events, 0) AS withdraw_events,d.first_block,w.last_withdraw_blockFROM deposits dFULL OUTER JOIN withdrawals wON d.user_address = w.user_address AND d.pid = w.pid)SELECT'0x' || lower(to_hex(user_address)) AS user_address,pid,net_raw,net_raw / 1e18 AS net_units_assuming_18_decimals,deposited_raw / 1e18 AS deposited_units,withdrawn_raw / 1e18 AS withdrawn_units,deposit_events,withdraw_events,first_block,last_withdraw_blockFROM netWHERE net_raw > 0ORDER BY net_raw DESCLIMIT 100