Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;
ORA-00936: missing expression

CREATE TABLE deals_table(
   company_id        INTEGER  NOT NULL 
  ,deal_id           INTEGER  NOT NULL
  ,deal_date         DATE  NOT NULL
  ,deal_date_5y_back DATE  NOT NULL
);
alter session set nls_date_format = 'yyyy-mm-dd';
begin
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,1,'2006-02-27','2001-02-27');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,2,'2012-04-01','2007-04-01');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,3,'2015-03-23','2010-03-23');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,4,'2018-09-19','2013-09-19');
end;
/
1 rows affected

select d.*,
    count(*) over(
        partition by company_id
        order by deal_date
        range between interval '5' year preceding and current row
    ) - 1 num_deals_5y_back
from deals_table d


COMPANY_ID
DEAL_ID
DEAL_DATE
DEAL_DATE_5Y_BACK
NUM_DEALS_5Y_BACK


86
1
2006-02-27
2001-02-27
0

86
2
2012-04-01
2007-04-01
0

86
3
2015-03-23
2010-03-23
1

86
4
2018-09-19
2013-09-19
1

COMPANY_ID	DEAL_ID	DEAL_DATE	DEAL_DATE_5Y_BACK	NUM_DEALS_5Y_BACK
86	1	2006-02-27	2001-02-27	0
86	2	2012-04-01	2007-04-01	0
86	3	2015-03-23	2010-03-23	1
86	4	2018-09-19	2013-09-19	1