Query editor
--https://web3datadegens.substack.com/p/web3-sql-weekly-8-analyzing-account?sd=pfwithop as (/*if you're trying to use this data yourself, you can just copy and paste this "op" CTE segment. The rest is just to aggregate and make it pretty.*/with base as (--bundler calls an entrypoint with handleOps, then each op is validated/pay vaidated and then each is called in an innerHandleOp where the account is called then the target action is delegatecalled--creates a safe, or simple account sometimes https://etherscan.io/address/0x15ba39375ee2ab563e8873c8390be6f2e2f50232#readContractSELECTfrom_hex(json_extract_scalar(json_extract_scalar(opInfo, '$.mUserOp'), '$.sender')) as sender, call_tx_hash, call_trace_address, call_block_number, tx."from" as bundlerFROM erc4337_polygon.EntryPoint_v0_6_call_innerHandleOp opLEFT JOIN polygon.transactions tx ON op.call_tx_hash = tx.hash), joined as (SELECTb.*, t.to, t.input, t.trace_address, row_number() over (partition by b.sender, call_trace_address, call_tx_hash order by t.trace_address asc) as first_callFROM base bLEFT JOIN polygon.traces tON b.call_tx_hash = t.tx_hash--we basically want the traces where the userop smart account "sender" is calling a transaction within same trace. Always happens at least after 3rd trace depthand cardinality(t.trace_address) > 3and b.call_trace_address[1] = t.trace_address[1]and b.sender = t."from"and t.call_type != 'delegatecall'and t.block_time >= timestamp '2023-04-01' --v0.6 deployed at)SELECT * FROM joinedWHERE first_call = 1 --we only want the first function call from the sender for a given op), sig as (SELECT distinct id, split_part(signature,'(',1) as functionFROM abi.signaturesWHERE type = 'function_call'), contracts as (SELECT address, name, blockchain, row_number() over (partition by address order by created_at desc) as latestFROM labels.contractsWHERE blockchain = 'polygon')SELECTcase when input != 0x then concat('<a href="', get_chain_explorer.explorer, '/address/', cast(to as varchar), '"target ="_blank">', '📄' || COALESCE('✔️ ' || c.name, ' ' || cast(to as varchar)), '</a>')else '💵 direct_transfer'end as called_contract, count(*) as total_ops