SQLite 3.27

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

CREATE TABLE T (`user` VARCHAR(7), `hour` VARCHAR(1), `item` VARCHAR(1), `delta` INTEGER);
INSERT INTO T (`user`, `hour`, `item`, `delta`) VALUES
  ('Alice', '1', 'A', '1'),
  ('Alice', '1', 'A', '2'),
  ('Bob',   '2', 'A', '2'),
  ('Alice', '3', 'A', '1'),
  ('Bob', '3', 'B', '1'),
  ('Alice', '5', 'A', '-1'),
  ('Bob', '5', 'B', '3');
CREATE TABLE P (`hour` INTEGER, `item` VARCHAR(1), `price` FLOAT);
INSERT INTO P (`hour`, `item`, `price`) VALUES
  ('1', 'A', '1.1'),
  ('1', 'B', '1.2'),
  ('2', 'A', '2.1'),
  ('2', 'B', '2.2'),
  ('3', 'A', '3.1'),
  ('3', 'B', '3.2'),
  ('4', 'A', '4.1'),
  ('4', 'B', '4.2'),
  ('5', 'A', '5.1'),
  ('5', 'B', '5.2');
WITH ranked_b AS (
  SELECT F.*, ROW_NUMBER() OVER (PARTITION BY p_hour, user, item ORDER BY hour DESC) rn
  FROM (
    SELECT P.hour p_hour, P.price, B.*  
    FROM P 
    INNER JOIN (
      SELECT DISTINCT hour, user, item, 
             SUM(delta) OVER (PARTITION BY user, item ORDER BY hour) running_total 
      FROM T
    ) B ON P.item = B.item AND B.hour <= P.hour  
  ) F
)  
SELECT p_hour hour, item, price, user, running_total 
FROM ranked_b 
WHERE rn = 1;


hour
item
price
user
running_total


1
A
1.1
Alice
3

2
A
2.1
Alice
3

2
A
2.1
Bob
2

3
A
3.1
Alice
4

3
A
3.1
Bob
2

3
B
3.2
Bob
1

4
A
4.1
Alice
4

4
A
4.1
Bob
2

4
B
4.2
Bob
1

5
A
5.1
Alice
3

5
A
5.1
Bob
2

5
B
5.2
Bob
4


SELECT p_hour hour, item, price, user, running_total
FROM (
  SELECT P.hour p_hour, P.price, B.*  
  FROM P 
  INNER JOIN (
    SELECT DISTINCT hour, user, item, 
           SUM(delta) OVER (PARTITION BY user, item ORDER BY hour) running_total 
    FROM T
  ) B ON P.item = B.item AND B.hour <= P.hour  
) F
GROUP BY p_hour, user, item
HAVING MAX(hour);


hour
item
price
user
running_total


1
A
1.1
Alice
3

2
A
2.1
Alice
3

2
A
2.1
Bob
2

3
A
3.1
Alice
4

3
A
3.1
Bob
2

3
B
3.2
Bob
1

4
A
4.1
Alice
4

4
A
4.1
Bob
2

4
B
4.2
Bob
1

5
A
5.1
Alice
3

5
A
5.1
Bob
2

5
B
5.2
Bob
4

hour	item	price	user	running_total
1	A	1.1	Alice	3
2	A	2.1	Alice	3
2	A	2.1	Bob	2
3	A	3.1	Alice	4
3	A	3.1	Bob	2
3	B	3.2	Bob	1
4	A	4.1	Alice	4
4	A	4.1	Bob	2
4	B	4.2	Bob	1
5	A	5.1	Alice	3
5	A	5.1	Bob	2
5	B	5.2	Bob	4