Virksomheder med størst vækst i ansatte

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,
 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 p.cvr_number = f.cvr_number
 WHERE p.is_active = true
), previous AS (SELECT cvr_number, average_employees AS previous_employees, reporting_period_end AS previous_period, reporting_currency AS previous_currency, reporting_period_days AS previous_days FROM ranked WHERE rn=2)
SELECT base.*, previous_employees, previous_period, round((employees / previous_employees - 1) * 100,1) AS employee_growth, count(*) OVER() AS total_matches
FROM base JOIN previous USING(cvr_number)
WHERE base.employees >= 100 AND previous_employees > 0 AND base.employees > previous_employees * 1.2 AND previous_currency = 'DKK' AND previous_days BETWEEN 330 AND 400 AND date_diff('day',try_cast(base.reporting_period_start AS DATE),try_cast(base.reporting_period_end AS DATE)) BETWEEN 329 AND 400
ORDER BY employee_growth DESC NULLS LAST, base.company_name ASC, base.cvr_number ASC