Query editor
WITH app_names(app_id, app_name) AS (VALUES(0x3b4bf6bf3ad5000ecf0f989d5befde585c6860fea3e574a4fab4c49d1c177d9c, 'kernel.aragonpm.eth'),(0xe3262375f45a6e2026b7e7b18c2b807434f2508fe1a2a3dfb493c7df8f4aad6a, 'acl.aragonpm.eth'),(0xddbcfd564f642ab5627cf68b9b7d374fb4f8a36e941a75d89c87998cef03bd61, 'evmreg.aragonpm.eth'),(0x9ac98dc5f995bf0211ed589ef022719d1487e5cb2bab505676f0d084c07cf89a, 'agent.aragonpm.eth'),(0xbf8491150dafc5dcaee5b861414dca922de09ccffa344964ae167212e8c673ae, 'finance.aragonpm.eth'),(0x6b20a3010614eeebf2138ccec99f028a61c811b3b1a3343b6ff635985c75c91f, 'token-manager.aragonpm.eth'),(0x9fa3927f639745e587912d4b0fea7ef9013bf93fb907d29faeab57417ba6e1d4, 'voting.aragonpm.eth'),(0x7a0495dbeb66081f64dddfec2669c82ac3113a65bd84e40344e31185f9bd3bbe, 'voting-proxy.aragonpm.eth'),(0x7e852e0fcfce6551c13800f1e7476f982525c2b5277ba14b24339c68416336d1, 'vault.aragonpm.eth')),universe AS (SELECT COUNT(*) creation_records, COUNT(DISTINCT address) unique_contracts FROM ethereum.creation_traces),app_instances AS (SELECT 'aragon_ethereum' AS source_namespace, output_0 AS contract_address, contract_address AS kernel, _appId AS app_id FROM aragon_ethereum.kernel_call_newappproxy WHERE call_successUNION ALL SELECT 'aragon_ethereum', output_appProxy, contract_address, _appId FROM aragon_ethereum.kernel_call_newappinstance WHERE call_successUNION ALL SELECT 'aragon_ethereum', output_appProxy, contract_address, _appId FROM aragon_ethereum.kernel_call_newpinnedappinstance WHERE call_successUNION ALL SELECT 'lido_ethereum', output_0, contract_address, _appId FROM lido_ethereum.kernel_call_newappproxy WHERE call_successUNION ALL SELECT 'lido_ethereum', output_appProxy, contract_address, _appId FROM lido_ethereum.kernel_call_newappinstance WHERE call_successUNION ALL SELECT 'lido_ethereum', output_appProxy, contract_address, _appId FROM lido_ethereum.kernel_call_newpinnedappinstance WHERE call_success),apps AS (SELECT DISTINCT source_namespace, contract_address, kernel, app_id FROM app_instances),acl_changes AS (SELECT _app AS contract_address, _entity AS entity, _role AS role, true AS allowed, call_block_number AS bn, call_tx_index AS txi, cardinality(call_trace_address) AS depth FROM aragon_ethereum.acl_call_createpermission WHERE call_successUNION ALL SELECT _app,_entity,_role,true,call_block_number,call_tx_index,cardinality(call_trace_address) FROM aragon_ethereum.acl_call_grantpermission WHERE call_successUNION ALL SELECT _app,_entity,_role,true,call_block_number,call_tx_index,cardinality(call_trace_address) FROM aragon_ethereum.acl_call_grantpermissionp WHERE call_successUNION ALL SELECT _app,_entity,_role,false,call_block_number,call_tx_index,cardinality(call_trace_address) FROM aragon_ethereum.acl_call_revokepermission WHERE call_successUNION ALL SELECT _app,_entity,_role,true,call_block_number,call_tx_index,cardinality(call_trace_address) FROM lido_ethereum.acl_call_createpermission WHERE call_successUNION ALL SELECT _app,_entity,_role,true,call_block_number,call_tx_index,cardinality(call_trace_address) FROM lido_ethereum.acl_call_grantpermission WHERE call_successUNION ALL SELECT _app,_entity,_role,true,call_block_number,call_tx_index,cardinality(call_trace_address) FROM lido_ethereum.acl_call_grantpermissionp WHERE call_successUNION ALL SELECT _app,_entity,_role,false,call_block_number,call_tx_index,cardinality(call_trace_address) FROM lido_ethereum.acl_call_revokepermission WHERE call_success),active_acl AS (SELECT contract_address, COUNT(*) FILTER (WHERE allowed) active_acl_rolesFROM (SELECT *, row_number() OVER (PARTITION BY contract_address, entity, role ORDER BY bn DESC, txi DESC, depth DESC) rn FROM acl_changes) xWHERE rn=1 GROUP BY 1),classified AS (SELECT a.source_namespace, a.contract_address, a.kernel, coalesce(n.app_name, concat('unknown_app_id:', lower(to_hex(a.app_id)))) AS app_name, coalesce(acl.active_acl_roles,0) active_acl_rolesFROM apps a LEFT JOIN app_names n ON a.app_id=n.app_id LEFT JOIN active_acl acl ON a.contract_address=acl.contract_address)SELECT 'APP' AS row_type, source_namespace, app_name, CAST(NULL AS varchar) AS kernel, COUNT(*) AS contracts, COUNT_IF(active_acl_roles > 0) AS with_active_acl, SUM(active_acl_roles) AS active_acl_role_rows, MIN(u.creation_records) creation_records, MIN(u.unique_contracts) unique_contractsFROM classified CROSS JOIN universe uGROUP BY 1,2,3,4UNION ALLSELECT 'KERNEL' AS row_type, source_namespace, CAST(NULL AS varchar) AS app_name, CAST(kernel AS varchar) AS kernel, COUNT(*) AS contracts, COUNT_IF(active_acl_roles > 0), SUM(active_acl_roles), MIN(u.creation_records), MIN(u.unique_contracts)FROM classified CROSS JOIN universe uGROUP BY 1,2,3,4ORDER BY row_type, source_namespace DESC, contracts DESC