Or this:
SELECT
active_flag = CASE WHEN c.active_flag = 1 THEN 'Active' ELSE 'Inactive' END,
c.name,
c.company_code,
d.active_users,
d.new_users,
d.inactive_users,
test_company_flag,
parent_company_flag
FROM dbo.company c
LEFT JOIN (
SELECT
company_code,
active_users= SUM(CASE WHEN active_flag = 1 AND unregister_flag = 0 THEN 1 END),
new_users= SUM(CASE WHEN active_flag = 1 AND unregister_flag = 1 THEN 1 END),
inactive_users= SUM(CASE WHEN active_flag = 0 THEN 1 END)
FROM dbo.person
GROUP BY company_code
) d ON d.company_code = c.company_code
What do you mean by "Unfortunately test data is not available"? Why can't you make some? You could obfuscate the company name and Bob's your uncle.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden