Companies with a single ultimate owner

WITH RECURSIVE 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, p.company_status, p.company_status_valid_from,
 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 LEFT JOIN f ON f.cvr_number=p.cvr_number
 WHERE p.is_active=true
), ownership_rows AS (SELECT DISTINCT cvr_number,owner_enhedsnummer,owner_cvr_number,owner_name,owner_enhedstype,ownership_lower_percent,ownership_upper_percent FROM gold.mart_company_ownership WHERE ownership_kind='legal_owner' AND (ownership_percent_valid_from IS NULL OR try_cast(ownership_percent_valid_from AS TIMESTAMPTZ) <= TIMESTAMPTZ '2026-10-10') AND (ownership_percent_valid_to IS NULL OR try_cast(ownership_percent_valid_to AS TIMESTAMPTZ) > TIMESTAMPTZ '2026-10-10')), sole_edges AS (
 SELECT * FROM ownership_rows QUALIFY count(*) OVER(PARTITION BY cvr_number)=1
), full_edges AS (SELECT * FROM sole_edges WHERE ownership_lower_percent=100 AND ownership_upper_percent=100),
 chain AS (
 SELECT cvr_number AS root_cvr,owner_cvr_number,owner_name,owner_enhedstype,1 AS depth,[cvr_number] AS path FROM full_edges
 UNION ALL
 SELECT c.root_cvr,e.owner_cvr_number,e.owner_name,e.owner_enhedstype,c.depth+1,list_append(c.path,e.cvr_number)
 FROM chain c JOIN full_edges e ON e.cvr_number=c.owner_cvr_number
 WHERE c.depth<20 AND NOT list_contains(c.path,e.cvr_number)
), ultimate AS (SELECT root_cvr,owner_name,depth FROM chain WHERE owner_enhedstype='PERSON')

SELECT base.*, u.owner_name, u.depth AS ownership_depth, count(*) OVER() AS total_matches
FROM base JOIN ultimate u ON u.root_cvr=base.cvr_number
WHERE true
ORDER BY employees DESC NULLS LAST, base.company_name ASC, base.cvr_number ASC