Как суммировать сумму для каждого пользователя?

Кажется, вам нужны суммы для первых записей н, сумма которых равна 1000. Вам нужна совокупная сумма:

select t.*,
       (case when running_sum <= 1000 then num
             when running_sum - num < 1000 then 1000 - (running_sum - num)
             else 0
         end) as new_num
from (select t.*, sum(num) over (partition by user_id order by num_id) as running_sum
      from t
     ) t;

BEGIN TRANSACTION;

/* Create a table called TEST123*/
/* A working example in SQLLITE3*/
 CREATE TABLE TEST123
(USER_ID integer,
NUM integer,
num_id integer);

/* Create few records in this table*/
INSERT INTO TEST123 VALUES(47641,800,1);
INSERT INTO TEST123 VALUES(47641,100,2);
INSERT INTO TEST123 VALUES(47641,250,3);
INSERT INTO TEST123 VALUES(70919,200,1);
INSERT INTO TEST123 VALUES(70919,200,2);
INSERT INTO TEST123 VALUES(70919,200,3);
INSERT INTO TEST123 VALUES(70919,200,4);

/* Display all the records from the table */
SELECT * FROM TEST123;


/*
SELECT a.USER_ID,  a.NUM ,a.num_id , (SELECT SUM(b.NUM) FROM TEST123 AS b where b.num_id<=a.num_id and b.user_id = a.user_id)
FROM TEST123 AS a 
GROUP BY a.USER_ID,a.NUM,a.num_id ORDER BY  a.USER_ID,a.num_id;
*/

CREATE TEMPORARY TABLE TEMP_TABLE1 AS
SELECT a.USER_ID AS USER_ID,  a.NUM AS NUM ,a.num_id AS num_id, (SELECT SUM(b.NUM) FROM TEST123 AS b where b.num_id<=a.num_id and b.user_id = a.user_id) AS CUM_NUM
FROM TEST123 AS a 
GROUP BY a.USER_ID,a.NUM,a.num_id ORDER BY  a.USER_ID,a.num_id;

SELECT * FROM TEMP_TABLE1;


SELECT USER_ID, NUM, num_id, CASE WHEN CUM_NUM > 1000 THEN (1000 - (CUM_NUM-NUM))  ELSE CUM_NUM END FINAL_CUM_NUM
FROM
TEMP_TABLE1;

create table t1(
tuser number,
tnum number,
numid number
);

Вставленные записи:

TUSER   TNUM    NUMID
47641   800     1
47641   100     2
47641   300     3
70919   200     1
70919   200     2
70919   200     3
70919   400     4
23451   800     1
23451   100     2
23451   600     3

Запрос решения:

select Tuser,
case 
when sum(Tnum) over(partition by Tuser order by numid)<=1000 then Tnum
when sum(Tnum) over(partition by Tuser order by numid)>1000 then 1000-((sum(Tnum) over(partition by Tuser order by numid))-tnum)
end Tnum,numid
from t1;

Выход:

TUSER   TNUM    NUMID
23451   800     1
23451   100     2
23451   100     3
47641   800     1
47641   100     2
47641   100     3
70919   200     1
70919   200     2
70919   200     3
70919   400     4