This is how it currently works, first select query returns a list of parameters.

SELECT character_id, foe_id, location_id, date_time, damage, points FROM events ORDER BY date_time DESC LIMIT 100

Now, the following is iterated 100 times to complete the data, for example the first one returns character_id = 12695, location_id = 818, foe_id = 33997, date_time = ‘2024-05-02 23:00:00’.

SELECT *, A1+B1-C1-D1-E1-F1+G1+H1 AS A2 FROM 
    (SELECT * FROM 
        (SELECT id_, cnt_7, date_diff_7 , NTH_VALUE (A0,1) OVER() AS A1, NTH_VALUE (A0,2) OVER() AS B1, NTH_VALUE (B0,1) OVER() AS C1, NTH_VALUE (B0,2) OVER() AS D1 FROM 
            (SELECT damage AS A0, points AS B0, RootQuery_7.id as id_, count(*) over() as cnt_7,  max(date_diff) over() as date_diff_7 FROM 
                (SELECT *, extract(day from date_time - lag(date_time) over (ORDER BY date_time)) as date_diff FROM events WHERE character_id = 12695 AND status = 1 AND location_id = 818 AND date_time < '2024-05-02 23:00:00' ORDER BY date_time DESC LIMIT 10 OFFSET 0)
                    AS RootQuery_7) 
            AS BaseQuery7 LIMIT 1) wnd_1 
        JOIN (SELECT id_, cnt_8, date_diff_8 , NTH_VALUE (D0,2) OVER() AS H1, NTH_VALUE (C0,1) OVER() AS E1, NTH_VALUE (C0,2) OVER() AS F1, NTH_VALUE (D0,1) OVER() AS G1 FROM 
            (SELECT damage AS C0, points AS D0, RootQuery_8.id as id_, count(*) over() as cnt_8,  max(date_diff) over() as date_diff_8 FROM 
                (SELECT *, extract(day from date_time - lag(date_time) over (ORDER BY date_time)) as date_diff FROM events WHERE foe_id = 33997 AND status = 1 AND location_id = 818 AND date_time < '2024-05-02 23:00:00' ORDER BY date_time DESC LIMIT 15 OFFSET 0)
                    AS RootQuery_8)
            AS BaseQuery8 LIMIT 1) wnd_2
    ON wnd_1.id_ <> 0 WHERE cnt_7 >= 10 AND date_diff_7 <= 150 AND cnt_8 >= 10 AND date_diff_8 <= 150) AS layer_1

It works but is too ineficent and slow, please dont mind the verisimilitude of the data retreaved, since it is an example.

What im tring to do is to do is to get this list of result from a single query like this

(SELECT character_id, foe_id, location_id, date_time, damage, points FROM events ORDER BY date_time DESC LIMIT 100) ref_
JOIN
SELECT *, A1+B1-C1-D1-E1-F1+G1+H1 AS A2 FROM 
    (SELECT * FROM 
        (SELECT id_, cnt_7, date_diff_7 , NTH_VALUE (A0,1) OVER() AS A1, NTH_VALUE (A0,2) OVER() AS B1, NTH_VALUE (B0,1) OVER() AS C1, NTH_VALUE (B0,2) OVER() AS D1 FROM 
            (SELECT damage AS A0, points AS B0, RootQuery_7.id as id_, count(*) over() as cnt_7,  max(date_diff) over() as date_diff_7 FROM 
                (SELECT *, extract(day from date_time - lag(date_time) over (ORDER BY date_time)) as date_diff FROM events WHERE character_id = ref_.character_id  AND status = 1 AND location_id = ref_.location_id AND date_time < location_id = ref_.date_time ORDER BY date_time DESC LIMIT 10 OFFSET 0)te_time
                    AS RootQuery_7) 
            AS BaseQuery7 LIMIT 1) wnd_1 
        JOIN (SELECT id_, cnt_8, date_diff_8 , NTH_VALUE (D0,2) OVER() AS H1, NTH_VALUE (C0,1) OVER() AS E1, NTH_VALUE (C0,2) OVER() AS F1, NTH_VALUE (D0,1) OVER() AS G1 FROM 
            (SELECT damage AS C0, points AS D0, RootQuery_8.id as id_, count(*) over() as cnt_8,  max(date_diff) over() as date_diff_8 FROM 
                (SELECT *, extract(day from date_time - lag(date_time) over (ORDER BY date_time)) as date_diff FROM events WHERE foe_id = ref_.foe_id  AND status = 1 AND location_id = ref_.location_id AND date_time < location_id = ref_.date_time ORDER BY date_time DESC LIMIT 15 OFFSET 0)
                    AS RootQuery_8)
            AS BaseQuery8 LIMIT 1) wnd_2
    ON wnd_1.id_ <> 0 WHERE cnt_7 >= 10 AND date_diff_7 <= 150 AND cnt_8 >= 10 AND date_diff_8 <= 150) AS layer_1
ON ...

but the result of the first query results cannot be used within the deepest layer of selects in the seccond query