Virksomheder med underskud tre år i træk i Region Nordjylland

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
), losses AS (
 SELECT a.cvr_number, b.profit_after_tax AS previous_profit, c.profit_after_tax AS third_profit, c.reporting_period_start AS first_period
 FROM ranked a JOIN ranked b ON a.cvr_number=b.cvr_number AND b.rn=2 JOIN ranked c ON a.cvr_number=c.cvr_number AND c.rn=3
 WHERE a.rn=1 AND a.profit_after_tax<0 AND b.profit_after_tax<0 AND c.profit_after_tax<0
 AND a.reporting_currency='DKK' AND b.reporting_currency='DKK' AND c.reporting_currency='DKK'
 AND try_cast(a.reporting_period_start AS DATE)=try_cast(b.reporting_period_end AS DATE)+1
 AND try_cast(b.reporting_period_start AS DATE)=try_cast(c.reporting_period_end AS DATE)+1
 AND try_cast(a.reporting_period_end AS DATE)-try_cast(a.reporting_period_start AS DATE) BETWEEN 329 AND 399
 AND try_cast(b.reporting_period_end AS DATE)-try_cast(b.reporting_period_start AS DATE) BETWEEN 329 AND 399
 AND try_cast(c.reporting_period_end AS DATE)-try_cast(c.reporting_period_start AS DATE) BETWEEN 329 AND 399
)

SELECT base.*, l.previous_profit, l.third_profit, l.first_period, count(*) OVER() AS total_matches
FROM base JOIN losses l USING(cvr_number)
WHERE (true AND EXISTS (SELECT 1 FROM gold.mart_company_geo_area g WHERE g.cvr_number=base.cvr_number AND g.area_id='region:1081' AND g.source_field='company_registered_address')) AND true
ORDER BY profit ASC NULLS LAST, base.company_name ASC, base.cvr_number ASC