Virksomheder med overskud tre år i træk

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
), history AS (SELECT cvr_number, reporting_period_start, reporting_period_end, reporting_period_days, reporting_currency, profit_after_tax, average_employees, rn FROM ranked WHERE rn <= 3), sequenced AS (SELECT *, lag(try_cast(reporting_period_end AS DATE)) OVER(PARTITION BY cvr_number ORDER BY reporting_period_end) AS previous_end FROM history), profitable AS (SELECT cvr_number, min(profit_after_tax) AS minimum_profit, min(reporting_period_start) AS first_period, count(*) AS periods FROM sequenced GROUP BY cvr_number HAVING count(*) = 3 AND count(*) FILTER(WHERE previous_end IS NOT NULL AND try_cast(reporting_period_start AS DATE) <> previous_end + 1) = 0 AND min(profit_after_tax) > 0 AND count(profit_after_tax) = 3 AND count(DISTINCT reporting_currency) = 1 AND min(reporting_currency) = 'DKK' AND min(reporting_period_days) >= 330 AND max(reporting_period_days) <= 400 AND sum(reporting_period_days) = date_diff('day',min(try_cast(reporting_period_start AS DATE)),max(try_cast(reporting_period_end AS DATE))) + 1 AND date_diff('day',min(try_cast(reporting_period_start AS DATE)),max(try_cast(reporting_period_end AS DATE))) BETWEEN 1000 AND 1100)
SELECT base.*, minimum_profit, first_period, periods, count(*) OVER() AS total_matches
FROM base JOIN profitable USING(cvr_number)
WHERE base.employees >= 250
ORDER BY minimum_profit DESC NULLS LAST, base.company_name ASC, base.cvr_number ASC