Virksomheder med helt nye registrerede ejere

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, 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
), old_owners AS (SELECT DISTINCT cvr_number,coalesce(nullif(owner_enhedsnummer,''),nullif(owner_cvr_number,'')) AS owner_key,owner_name,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-07-13') AND (ownership_percent_valid_to IS NULL OR try_cast(ownership_percent_valid_to AS TIMESTAMPTZ)>TIMESTAMPTZ '2026-07-13')), new_owners AS (SELECT DISTINCT cvr_number,coalesce(nullif(owner_enhedsnummer,''),nullif(owner_cvr_number,'')) AS owner_key,owner_name,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')),
 old_sets AS (SELECT cvr_number,string_agg(owner_name,', ' ORDER BY owner_name) AS previous_owners FROM old_owners GROUP BY cvr_number HAVING sum(ownership_lower_percent)=100 AND sum(ownership_upper_percent)=100 AND count(*)=count(DISTINCT owner_key) AND count(ownership_lower_percent)=count(*) AND count(ownership_upper_percent)=count(*)),
 new_sets AS (SELECT cvr_number,string_agg(owner_name,', ' ORDER BY owner_name) AS owner_name FROM new_owners GROUP BY cvr_number HAVING sum(ownership_lower_percent)=100 AND sum(ownership_upper_percent)=100 AND count(*)=count(DISTINCT owner_key) AND count(ownership_lower_percent)=count(*) AND count(ownership_upper_percent)=count(*)),
 changed AS (SELECT n.cvr_number,n.owner_name,o.previous_owners FROM new_sets n JOIN old_sets o USING(cvr_number) WHERE NOT EXISTS (SELECT 1 FROM old_owners x JOIN new_owners y ON x.cvr_number=y.cvr_number AND x.owner_key=y.owner_key WHERE x.cvr_number=n.cvr_number))

SELECT base.*, c.owner_name, c.previous_owners, count(*) OVER() AS total_matches
FROM base JOIN changed c USING(cvr_number)
WHERE true
ORDER BY employees DESC NULLS LAST, base.company_name ASC, base.cvr_number ASC