MariaDB 10.3

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

create table dividends( id bigint unsigned auto_increment primary key,
                        company varchar(5) not null,
                        record_date date null,
                        summ integer null );
INSERT INTO dividends (company, record_date, summ) VALUES 
('CompA','19-12-20','192'),
('CompA','19-07-09','155'),
('CompA','18-12-21','95'),
('CompA','18-07-11','130'),
('CompA','17-12-22','85'),
('CompA','17-07-10','120'),
('CompA','16-12-23','75'),
('CompA','16-07-12','112'),
('CompA','15-12-24','65'),
('CompA','15-07-14','94'),
('CompA','14-12-26','60'),
('CompA','14-07-15','60'),
('CompA','13-08-15','50'),
('CompA','13-01-13','50'),
('CompA','12-11-12','40'),
('CompA','12-01-11','75'),
('CompB','19-01-11','75'),
('CompB','18-01-11','25'),
('CompB','17-01-11','75'),
('CompB','16-01-11','60'),
('CompB','15-01-11','55'),
('CompB','14-01-11','45'),
('CompB','13-01-11','35'),
('CompB','12-01-11','25'),
('CompC','19-02-21','75'),
('CompC','18-03-12','70'),
('CompC','14-06-01','40'),
('CompC','13-07-22','20');
SELECT * FROM dividends;


id
company
record_date
summ


1
CompA
2019-12-20
192

2
CompA
2019-07-09
155

3
CompA
2018-12-21
95

4
CompA
2018-07-11
130

5
CompA
2017-12-22
85

6
CompA
2017-07-10
120

7
CompA
2016-12-23
75

8
CompA
2016-07-12
112

9
CompA
2015-12-24
65

10
CompA
2015-07-14
94

11
CompA
2014-12-26
60

12
CompA
2014-07-15
60

13
CompA
2013-08-15
50

14
CompA
2013-01-13
50

15
CompA
2012-11-12
40

16
CompA
2012-01-11
75

17
CompB
2019-01-11
75

18
CompB
2018-01-11
25

19
CompB
2017-01-11
75

20
CompB
2016-01-11
60

21
CompB
2015-01-11
55

22
CompB
2014-01-11
45

23
CompB
2013-01-11
35

24
CompB
2012-01-11
25

25
CompC
2019-02-21
75

26
CompC
2018-03-12
70

27
CompC
2014-06-01
40

28
CompC
2013-07-22
20


WITH
cte1 AS ( SELECT company, 
                 YEAR(record_date) record_year, 
                 SUM(summ) summ
          FROM dividends
          GROUP BY 1, 2 ),
cte2 AS ( SELECT company, 
                 record_year, 
                 summ, 
                 LAG(summ) OVER (PARTITION BY company ORDER BY record_year) lag_summ, 
                 LAG(record_year) OVER (PARTITION BY company ORDER BY record_year) lag_year,
                 (summ < COALESCE(LAG(summ) OVER (PARTITION BY company ORDER BY record_year), 0)) summ_not_over,
                 (COALESCE(LEAD(record_year) OVER (PARTITION BY company ORDER BY record_year), YEAR(CURRENT_DATE)) - record_year) != 1 is_not_consecutive
          FROM cte1 ),
cte3 AS ( SELECT company, 
                 record_year, 
                 summ,
                 SUM(summ_not_over) OVER (PARTITION BY company ORDER BY record_year DESC) summ_over,
                 SUM(is_not_consecutive) OVER (PARTITION BY company ORDER BY record_year DESC) is_consecutive
          FROM cte2
          WHERE record_year >= YEAR(CURRENT_DATE) - 7 )
SELECT company, (SUM(!is_consecutive) + SUM(!(summ_over + is_consecutive))) / 14 DSI
FROM cte3
GROUP BY 1;


company
DSI


CompA
0.9286

CompB
0.5714

CompC
0.2857


WITH
cte1 AS ( SELECT company, 
                 YEAR(record_date) record_year, 
                 SUM(summ) summ
          FROM dividends
          GROUP BY 1, 2 )
SELECT * FROM cte1;


company
record_year
summ


CompA
2012
115

CompA
2013
100

CompA
2014
120

CompA
2015
159

CompA
2016
187

CompA
2017
205

CompA
2018
225

CompA
2019
347

CompB
2012
25

CompB
2013
35

CompB
2014
45

CompB
2015
55

CompB
2016
60

CompB
2017
75

CompB
2018
25

CompB
2019
75

CompC
2013
20

CompC
2014
40

CompC
2018
70

CompC
2019
75


WITH
cte1 AS ( SELECT company, 
                 YEAR(record_date) record_year, 
                 SUM(summ) summ
          FROM dividends
          GROUP BY 1, 2 ),
cte2 AS ( SELECT company, 
                 record_year, 
                 summ, 
                 LAG(summ) OVER (PARTITION BY company ORDER BY record_year) lag_summ, 
                 LAG(record_year) OVER (PARTITION BY company ORDER BY record_year) lag_year,
                 (summ < COALESCE(LAG(summ) OVER (PARTITION BY company ORDER BY record_year), 0)) summ_not_over,
                 (COALESCE(LEAD(record_year) OVER (PARTITION BY company ORDER BY record_year), YEAR(CURRENT_DATE)) - record_year) != 1 is_not_consecutive
          FROM cte1 )
SELECT * FROM cte2;


company
record_year
summ
lag_summ
lag_year
summ_not_over
is_not_consecutive


CompA
2012
115
null
null
0
0

CompA
2013
100
115
2012
1
0

CompA
2014
120
100
2013
0
0

CompA
2015
159
120
2014
0
0

CompA
2016
187
159
2015
0
0

CompA
2017
205
187
2016
0
0

CompA
2018
225
205
2017
0
0

CompA
2019
347
225
2018
0
0

CompB
2012
25
null
null
0
0

CompB
2013
35
25
2012
0
0

CompB
2014
45
35
2013
0
0

CompB
2015
55
45
2014
0
0

CompB
2016
60
55
2015
0
0

CompB
2017
75
60
2016
0
0

CompB
2018
25
75
2017
1
0

CompB
2019
75
25
2018
0
0

CompC
2013
20
null
null
0
0

CompC
2014
40
20
2013
0
1

CompC
2018
70
40
2014
0
0

CompC
2019
75
70
2018
0
0


WITH
cte1 AS ( SELECT company, 
                 YEAR(record_date) record_year, 
                 SUM(summ) summ
          FROM dividends
          GROUP BY 1, 2 ),
cte2 AS ( SELECT company, 
                 record_year, 
                 summ, 
                 LAG(summ) OVER (PARTITION BY company ORDER BY record_year) lag_summ, 
                 LAG(record_year) OVER (PARTITION BY company ORDER BY record_year) lag_year,
                 (summ < COALESCE(LAG(summ) OVER (PARTITION BY company ORDER BY record_year), 0)) summ_not_over,
                 (COALESCE(LEAD(record_year) OVER (PARTITION BY company ORDER BY record_year), YEAR(CURRENT_DATE)) - record_year) != 1 is_not_consecutive
          FROM cte1 ),
cte3 AS ( SELECT company, 
                 record_year, 
                 summ,
                 SUM(summ_not_over) OVER (PARTITION BY company ORDER BY record_year DESC) summ_over,
                 SUM(is_not_consecutive) OVER (PARTITION BY company ORDER BY record_year DESC) is_consecutive
          FROM cte2
          WHERE record_year >= YEAR(CURRENT_DATE) - 7 )
SELECT * FROM cte3;


company
record_year
summ
summ_over
is_consecutive


CompA
2019
347
0
0

CompA
2018
225
0
0

CompA
2017
205
0
0

CompA
2016
187
0
0

CompA
2015
159
0
0

CompA
2014
120
0
0

CompA
2013
100
1
0

CompB
2019
75
0
0

CompB
2018
25
1
0

CompB
2017
75
1
0

CompB
2016
60
1
0

CompB
2015
55
1
0

CompB
2014
45
1
0

CompB
2013
35
1
0

CompC
2019
75
0
0

CompC
2018
70
0
0

CompC
2014
40
0
1

CompC
2013
20
0
1

id	company	record_date	summ
1	CompA	2019-12-20	192
2	CompA	2019-07-09	155
3	CompA	2018-12-21	95
4	CompA	2018-07-11	130
5	CompA	2017-12-22	85
6	CompA	2017-07-10	120
7	CompA	2016-12-23	75
8	CompA	2016-07-12	112
9	CompA	2015-12-24	65
10	CompA	2015-07-14	94
11	CompA	2014-12-26	60
12	CompA	2014-07-15	60
13	CompA	2013-08-15	50
14	CompA	2013-01-13	50
15	CompA	2012-11-12	40
16	CompA	2012-01-11	75
17	CompB	2019-01-11	75
18	CompB	2018-01-11	25
19	CompB	2017-01-11	75
20	CompB	2016-01-11	60
21	CompB	2015-01-11	55
22	CompB	2014-01-11	45
23	CompB	2013-01-11	35
24	CompB	2012-01-11	25
25	CompC	2019-02-21	75
26	CompC	2018-03-12	70
27	CompC	2014-06-01	40
28	CompC	2013-07-22	20

company	record_year	summ
CompA	2012	115
CompA	2013	100
CompA	2014	120
CompA	2015	159
CompA	2016	187
CompA	2017	205
CompA	2018	225
CompA	2019	347
CompB	2012	25
CompB	2013	35
CompB	2014	45
CompB	2015	55
CompB	2016	60
CompB	2017	75
CompB	2018	25
CompB	2019	75
CompC	2013	20
CompC	2014	40
CompC	2018	70
CompC	2019	75

company	record_year	summ	lag_summ	lag_year	summ_not_over	is_not_consecutive
CompA	2012	115	null	null	0	0
CompA	2013	100	115	2012	1	0
CompA	2014	120	100	2013	0	0
CompA	2015	159	120	2014	0	0
CompA	2016	187	159	2015	0	0
CompA	2017	205	187	2016	0	0
CompA	2018	225	205	2017	0	0
CompA	2019	347	225	2018	0	0
CompB	2012	25	null	null	0	0
CompB	2013	35	25	2012	0	0
CompB	2014	45	35	2013	0	0
CompB	2015	55	45	2014	0	0
CompB	2016	60	55	2015	0	0
CompB	2017	75	60	2016	0	0
CompB	2018	25	75	2017	1	0
CompB	2019	75	25	2018	0	0
CompC	2013	20	null	null	0	0
CompC	2014	40	20	2013	0	1
CompC	2018	70	40	2014	0	0
CompC	2019	75	70	2018	0	0