Companies with the same owner

WITH ranked AS (
 SELECT *, row_number() OVER (PARTITION BY cvr_number ORDER BY reporting_period_end DESC, publication_timestamp DESC, filing_id DESC, reporting_currency) AS rn
 FROM gold.mart_company_financials_annual_scoped
 WHERE statement_scope = 'entity'
), f AS (SELECT * FROM ranked WHERE rn = 1 AND reporting_currency = 'DKK'),
base AS (
 SELECT p.cvr_number, p.company_name, p.city, p.registered_address_slug, p.registered_address_identity, p.registered_address_formatted, p.founded_at,
 f.reporting_period_start, f.reporting_period_end, f.average_employees AS employees,
 f.operating_profit, f.profit_after_tax AS profit, f.equity, f.revenue
 FROM gold.mart_company_profile p JOIN f ON p.cvr_number = f.cvr_number
 WHERE p.is_active = true
), owners AS (SELECT DISTINCT cvr_number, person_id, display_name, slug, ownership_lower_percent, ownership_upper_percent, ownership_label FROM gold.mart_person_ownership WHERE ownership_kind = 'legal_owner' AND effective_from <= TIMESTAMPTZ '2026-10-10' AND (effective_to IS NULL OR effective_to > TIMESTAMPTZ '2026-10-10')), portfolios AS (SELECT person_id, count(DISTINCT cvr_number) AS owned_companies FROM owners GROUP BY person_id HAVING count(DISTINCT cvr_number) >= 5), matches AS (SELECT owners.*, owned_companies FROM owners JOIN portfolios USING(person_id) QUALIFY row_number() OVER(PARTITION BY cvr_number ORDER BY owned_companies DESC, person_id) = 1)
SELECT base.*, display_name AS owner_name, person_id, slug AS owner_slug, ownership_label, owned_companies, count(*) OVER() AS total_matches
FROM base JOIN matches USING(cvr_number)
WHERE base.employees >= 50
ORDER BY owned_companies DESC NULLS LAST, base.company_name ASC, base.cvr_number ASC