SQLite 3.8

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

CREATE TABLE sales_performance
(
 product_ID varchar(3) not null,
 sales_period varchar(4) not null,
 sales_qty char(2) not null,
 sales_index char(2) not null,
 product_sub char(3) not null,
 goal_met char(1)  not null,
 PRIMARY KEY (product_ID, sales_period)
)
INSERT INTO sales_performance
(product_ID,sales_period,sales_qty,sales_index,product_sub,goal_met) 
VALUES
 ('C12','0001','15','20','D71','Y')
,('D71','0001','07','09','C12','N')
,('F20','0001','25','30','C05','Y')
,('C05','0001','10','15','F20','N')
,('C12','0002','15','30','C05','Y')
,('C05','0002','12','06','C12','N')
,('D71','0002','30','20','F20','Y')
,('F20','0002','20','15','D71','N')
,('C12','0003','05','04','F20','N')
,('F20','0003','40','35','C12','Y')
,('D71','0003','20','20','C05','Y')
,('C05','0003','12','10','D71','N')
;
SELECT c12.*, sub.sales_index
FROM sales_performance c12
LEFT JOIN sales_performance sub 
  ON sub.sales_period < c12.sales_period 
 AND 
 (
    (sub.product_ID = c12.product_sub AND sub.goal_met = 'N') OR
    (sub.product_ID = c12.product_ID AND sub.goal_met = 'Y')
 )
WHERE c12.product_id = 'C12'
ORDER BY c12.product_ID, c12.sales_period, sub.product_ID


product_ID
sales_period
sales_qty
sales_index
product_sub
goal_met
sales_index


C12
0001
15
20
D71
Y
null

C12
0002
15
30
C05
Y
15

C12
0002
15
30
C05
Y
20

C12
0003
05
04
F20
N
20

C12
0003
05
04
F20
N
30

C12
0003
05
04
F20
N
15


SELECT 
 c12.*
 , ROUND(AVG(sub.sales_index),1) AS sales_index_goal
FROM sales_performance c12
LEFT JOIN sales_performance sub 
  ON sub.sales_period < c12.sales_period 
 AND 
 (
    (sub.product_ID = c12.product_sub AND sub.goal_met = 'N') OR
    (sub.product_ID = c12.product_ID AND sub.goal_met = 'Y')
 )
WHERE c12.product_id = 'C12' 
GROUP BY c12.product_ID, c12.sales_period
ORDER BY c12.product_ID, c12.sales_period;


product_ID
sales_period
sales_qty
sales_index
product_sub
goal_met
sales_index_goal


C12
0001
15
20
D71
Y
null

C12
0002
15
30
C05
Y
17.5

C12
0003
05
04
F20
N
21.7

product_ID	sales_period	sales_qty	sales_index	product_sub	goal_met	sales_index
C12	0001	15	20	D71	Y	null
C12	0002	15	30	C05	Y	15
C12	0002	15	30	C05	Y	20
C12	0003	05	04	F20	N	20
C12	0003	05	04	F20	N	30
C12	0003	05	04	F20	N	15