SQLite 3.27

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

CREATE TABLE tablename (`ColumnA` REAL, `ColumnB` TEXT, `ColumnC` TEXT);
✓
INSERT INTO tablename (`ColumnA`, `ColumnB`, `ColumnC`) VALUES
  ('2.0', 'Rock', '2020-08-08'),
  ('3.0', 'Paper', '2020-09-08'),
  ('25.0', 'Rock', '2021-09-09'),
  ('12.0', 'Rock', '2021-10-10'),
  ('5.0', 'Paper', '2021-11-11');
✓
SELECT DISTINCT t1.*, 
       t1.ColumnA + COALESCE(FIRST_VALUE(t2.ColumnA) OVER (PARTITION BY t1.ColumnC ORDER BY t2.ColumnC DESC), 0) ColumnD
FROM tablename t1 LEFT JOIN tablename t2
ON t2.ColumnC < t1.ColumnC AND t2.ColumnB <> t1.ColumnB
ORDER BY t1.ColumnC;


ColumnA
ColumnB
ColumnC
ColumnD


2
Rock
2020-08-08
2

3
Paper
2020-09-08
5

25
Rock
2021-09-09
28

12
Rock
2021-10-10
15

5
Paper
2021-11-11
17

ColumnA	ColumnB	ColumnC	ColumnD
2	Rock	2020-08-08	2
3	Paper	2020-09-08	5
25	Rock	2021-09-09	28
12	Rock	2021-10-10	15
5	Paper	2021-11-11	17