Query editor
WITH ns_create AS (SELECTblock_time,account_argumentsFROM solana.instruction_callsWHERE executing_account = 'namesLPneVptA9Z5rqUDD9tMTWEJwofgaYwp8cawRkX' -- Name ServiceAND tx_success = TRUEAND BYTEARRAY_TO_BIGINT(BYTEARRAY_SUBSTRING(data, 1, 1)) = 0 -- NameRegistryCreate),subs AS (SELECTblock_time,element_at(account_arguments, 3) AS name_account, -- childelement_at(account_arguments, 5) AS name_class, -- classelement_at(account_arguments, 6) AS parent_account -- parentFROM ns_create),parents AS (SELECTelement_at(account_arguments, 3) AS parent_name_account,element_at(account_arguments, 6) AS parent_parent_accountFROM ns_create),first_level_dot_sol AS (SELECTDATE_TRUNC('month', s.block_time) AS month,s.name_accountFROM subs sJOIN parents pON s.parent_account = p.parent_name_accountWHERE s.name_class = '11111111111111111111111111111111'AND p.parent_parent_account = '58PwtjSDuFHuUkYjH9BYnnQKHfwo9reZhC2zMJv9JPkx' -- .sol TLDAND s.block_time >= DATE_TRUNC('month', DATE_ADD('month', -11, CURRENT_DATE)) -- last 12 months),months AS (SELECT CAST(x AS date) AS monthFROM UNNEST(SEQUENCE(DATE_TRUNC('month', DATE_ADD('month', -11, CURRENT_DATE)),DATE_TRUNC('month', CURRENT_DATE),INTERVAL '1' month)) AS t(x))SELECTm.month,COALESCE(COUNT(DISTINCT f.name_account), 0) AS registrationsFROM months mLEFT JOIN first_level_dot_sol f ON f.month = m.monthGROUP BY 1ORDER BY 1;