Growing companies with new owners

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
), comparison AS (
 SELECT c.cvr_number, old.average_employees AS previous_employees, old.revenue AS previous_revenue, old.profit_after_tax AS previous_profit, old.reporting_period_end AS previous_period,
 100.0*(c.average_employees-old.average_employees)/nullif(old.average_employees,0) AS employee_growth,
 100.0*(c.revenue-old.revenue)/nullif(old.revenue,0) AS revenue_growth_percent
 FROM ranked c JOIN ranked old ON c.cvr_number=old.cvr_number AND c.rn=1 AND old.rn=2
 WHERE c.reporting_currency='DKK' AND old.reporting_currency='DKK'
 AND try_cast(c.reporting_period_end AS DATE)-try_cast(c.reporting_period_start AS DATE) BETWEEN 329 AND 399
 AND try_cast(old.reporting_period_end AS DATE)-try_cast(old.reporting_period_start AS DATE) BETWEEN 329 AND 399
 AND try_cast(c.reporting_period_start AS DATE)=try_cast(old.reporting_period_end AS DATE)+1
)
, changes AS (
 SELECT cvr_number, effective_from AS event_date, after_value, before_value,
 json_extract_string(metadata_json, '$.relationship_subject.name') AS event_owner,
 json_extract_string(metadata_json, '$.relationship_subject.cvr_number') AS event_owner_cvr
 FROM gold.mart_company_timeline
 WHERE category = 'ownership' AND field_key = 'legal_owner'
 AND json_extract_string(metadata_json, '$.event_type') = 'legal_owner_changed'
 AND json_extract_string(metadata_json, '$.change_kind') = 'created'
 AND effective_from >= TIMESTAMPTZ '2026-07-13' AND effective_from < TIMESTAMPTZ '2026-10-11'
 QUALIFY row_number() OVER(PARTITION BY cvr_number ORDER BY effective_from DESC, event_id) = 1
)

SELECT base.*, e.event_date, e.event_owner, c.previous_employees, c.previous_revenue, c.previous_profit, c.previous_period, c.employee_growth, c.revenue_growth_percent, count(*) OVER() AS total_matches
FROM base JOIN comparison c USING(cvr_number) JOIN changes e USING(cvr_number)
WHERE c.previous_employees>=10 AND c.employee_growth>20 AND try_cast(base.founded_at AS DATE)<DATE '2026-07-13'
ORDER BY employee_growth DESC NULLS LAST, base.company_name ASC, base.cvr_number ASC