Ejerledede virksomheder med overskud

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 JOIN f ON f.cvr_number=p.cvr_number
 WHERE p.is_active=true
)
SELECT base.*, (SELECT string_agg(DISTINCT o.display_name, ', ' ORDER BY o.display_name) FROM gold.mart_person_ownership o JOIN gold.mart_person_roles_current r ON r.participant_enhedsnummer=o.owner_enhedsnummer AND r.cvr_number=o.cvr_number WHERE o.cvr_number=base.cvr_number AND o.ownership_kind='legal_owner' AND o.ownership_lower_percent>0 AND (o.effective_from IS NULL OR try_cast(o.effective_from AS TIMESTAMPTZ) <= TIMESTAMPTZ '2026-10-10') AND (o.effective_to IS NULL OR try_cast(o.effective_to AS TIMESTAMPTZ) > TIMESTAMPTZ '2026-10-10') AND r.role_key='executive_board' AND r.is_current=true AND (r.valid_from IS NULL OR try_cast(r.valid_from AS TIMESTAMPTZ) <= TIMESTAMPTZ '2026-10-10') AND (r.valid_to IS NULL OR try_cast(r.valid_to AS TIMESTAMPTZ) > TIMESTAMPTZ '2026-10-10')) AS owner_name, count(*) OVER() AS total_matches
FROM base
WHERE profit>0 AND EXISTS (SELECT 1 FROM gold.mart_person_ownership o JOIN gold.mart_person_roles_current r ON r.participant_enhedsnummer=o.owner_enhedsnummer AND r.cvr_number=o.cvr_number WHERE o.cvr_number=base.cvr_number AND o.ownership_kind='legal_owner' AND o.ownership_lower_percent>0 AND (o.effective_from IS NULL OR try_cast(o.effective_from AS TIMESTAMPTZ) <= TIMESTAMPTZ '2026-10-10') AND (o.effective_to IS NULL OR try_cast(o.effective_to AS TIMESTAMPTZ) > TIMESTAMPTZ '2026-10-10') AND r.role_key='executive_board' AND r.is_current=true AND (r.valid_from IS NULL OR try_cast(r.valid_from AS TIMESTAMPTZ) <= TIMESTAMPTZ '2026-10-10') AND (r.valid_to IS NULL OR try_cast(r.valid_to AS TIMESTAMPTZ) > TIMESTAMPTZ '2026-10-10'))
ORDER BY profit DESC NULLS LAST, base.company_name ASC, base.cvr_number ASC