Companies with capital increases

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
), events AS (SELECT cvr_number,decided_date AS event_date,NULL AS before_value,funding_kind AS after_value,nominal_change,currency FROM gold.mart_company_capital_event WHERE is_retracted=false AND direction='increase' AND nominal_change>0 AND currency='DKK' AND try_cast(decided_date AS DATE)>=DATE '2026-07-13' AND try_cast(decided_date AS DATE)<DATE '2026-10-11' QUALIFY row_number() OVER(PARTITION BY cvr_number ORDER BY decided_date DESC,capital_event_id)=1)

SELECT base.*, e.event_date, e.before_value, e.after_value, e.nominal_change, count(*) OVER() AS total_matches
FROM base JOIN events e USING(cvr_number)
WHERE true
ORDER BY event_date DESC NULLS LAST, base.company_name ASC, base.cvr_number ASC