Query editor
WITH creates AS (SELECTblock_time,element_at(account_arguments, 3) AS name_account, -- created accountelement_at(account_arguments, 5) AS name_class, -- classelement_at(account_arguments, 6) AS parent_account -- parentFROM solana.instruction_callsWHERE executing_account = 'namesLPneVptA9Z5rqUDD9tMTWEJwofgaYwp8cawRkX' -- Name ServiceAND tx_success = TRUEAND BYTEARRAY_TO_BIGINT(BYTEARRAY_SUBSTRING(data, 1, 1)) = 0 -- NameRegistryCreate),parents AS (SELECTelement_at(account_arguments, 3) AS parent_name_account,element_at(account_arguments, 6) AS parent_parent_accountFROM solana.instruction_callsWHERE executing_account = 'namesLPneVptA9Z5rqUDD9tMTWEJwofgaYwp8cawRkX'AND tx_success = TRUEAND BYTEARRAY_TO_BIGINT(BYTEARRAY_SUBSTRING(data, 1, 1)) = 0 -- Create rows only),first_level_subs AS (SELECTc.name_account,c.block_timeFROM creates cJOIN parents pON c.parent_account = p.parent_name_accountWHERE c.name_class = '11111111111111111111111111111111'AND p.parent_parent_account = '58PwtjSDuFHuUkYjH9BYnnQKHfwo9reZhC2zMJv9JPkx' -- .sol TLD),last_create AS (SELECT name_account, max(block_time) AS last_create_timeFROM first_level_subsGROUP BY 1),last_delete AS (SELECTacc AS name_account,max(ic.block_time) AS last_delete_timeFROM solana.instruction_calls icCROSS JOIN UNNEST(ic.account_arguments) AS t(acc)WHERE ic.executing_account = 'namesLPneVptA9Z5rqUDD9tMTWEJwofgaYwp8cawRkX'AND ic.tx_success = TRUEAND BYTEARRAY_TO_BIGINT(BYTEARRAY_SUBSTRING(ic.data, 1, 1)) = 3 -- NameRegistryDeleteGROUP BY 1)SELECT COUNT(*) AS total_first_level_subdomains_dot_sol_liveFROM last_create lcLEFT JOIN last_delete ld USING (name_account)WHERE COALESCE(ld.last_delete_time, TIMESTAMP '1970-01-01') < lc.last_create_time;