Query editor
WITH apps AS (SELECT 'aragon_ethereum' AS source_namespace, output_0 AS contract_address, contract_address AS kernel, _appId AS app_id, call_block_time AS first_seenFROM aragon_ethereum.kernel_call_newappproxy WHERE call_successUNION ALLSELECT 'aragon_ethereum', output_appProxy, contract_address, _appId, call_block_timeFROM aragon_ethereum.kernel_call_newappinstance WHERE call_successUNION ALLSELECT 'aragon_ethereum', output_appProxy, contract_address, _appId, call_block_timeFROM aragon_ethereum.kernel_call_newpinnedappinstance WHERE call_successUNION ALLSELECT 'lido_ethereum', output_0, contract_address, _appId, call_block_timeFROM lido_ethereum.kernel_call_newappproxy WHERE call_successUNION ALLSELECT 'lido_ethereum', output_appProxy, contract_address, _appId, call_block_timeFROM lido_ethereum.kernel_call_newappinstance WHERE call_successUNION ALLSELECT 'lido_ethereum', output_appProxy, contract_address, _appId, call_block_timeFROM lido_ethereum.kernel_call_newpinnedappinstance WHERE call_success),app_set AS (SELECT source_namespace, contract_address, kernel, app_id, min(first_seen) AS first_seenFROM appsGROUP BY 1,2,3,4),acl_changes AS (SELECT 'aragon_ethereum' AS source_namespace, _app AS contract_address, _entity AS controller, _role AS role, true AS allowed, call_block_number AS bn, call_tx_index AS txi, cardinality(call_trace_address) AS depthFROM aragon_ethereum.acl_call_createpermission WHERE call_successUNION ALL SELECT 'aragon_ethereum', _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 'aragon_ethereum', _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 'aragon_ethereum', _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 'lido_ethereum', _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 'lido_ethereum', _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 'lido_ethereum', _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 'lido_ethereum', _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 source_namespace, contract_address, controller, roleFROM (SELECT *, row_number() OVER (PARTITION BY source_namespace, contract_address, controller, role ORDER BY bn DESC, txi DESC, depth DESC) AS rnFROM acl_changes) xWHERE rn = 1 AND allowed),controller_rollup AS (SELECT a.source_namespace,a.contract_address,s.kernel,CASE lower(to_hex(s.app_id))WHEN '9ac98dc5f995bf0211ed589ef022719d1487e5cb2bab505676f0d084c07cf89a' THEN 'agent.aragonpm.eth'WHEN 'bf8491150dafc5dcaee5b861414dca922de09ccffa344964ae167212e8c673ae' THEN 'finance.aragonpm.eth'WHEN '6b20a3010614eeebf2138ccec99f028a61c811b3b1a3343b6ff635985c75c91f' THEN 'token-manager.aragonpm.eth'WHEN '9fa3927f639745e587912d4b0fea7ef9013bf93fb907d29faeab57417ba6e1d4' THEN 'voting.aragonpm.eth'WHEN '7a0495dbeb66081f64dddfec2669c82ac3113a65bd84e40344e31185f9bd3bbe' THEN 'voting-proxy.aragonpm.eth'WHEN '7e852e0fcfce6551c13800f1e7476f982525c2b5277ba14b24339c68416336d1' THEN 'vault.aragonpm.eth'ELSE concat('unknown:', lower(to_hex(s.app_id)))END AS app_name,s.first_seen,a.controller,COUNT(*) AS active_roles,array_join(array_sort(array_agg(lower(to_hex(a.role)))), ',') AS role_hashes