Is it possible to simplify nested window functions into a single SQL statement?

Data and Databases
1

Hey folks, I’m trying to figure out if there’s a way to combine nested window functions into one SQL statement. Right now, I’m using two window functions to get the last_value of a sum, but I’d love to do this in just one line of Trino SQL.

Here’s what I’m working with:

CREATE TABLE example_data (
    date_col DATE,
    value DECIMAL(10,1),
    flag INT,
    expected_sum DECIMAL(10,1)
);

INSERT INTO example_data VALUES
('2025-03-01', 1.1, 0, 1.1),
('2025-03-02', 2.1, 0, 3.2),
-- ... more data ...
('2025-03-31', 31.1, 0, 196.7);

WITH calc_sum AS (
    SELECT *,
        SUM(CASE WHEN flag = 0 THEN value END)
        OVER(PARTITION BY flag ORDER BY date_col 
             ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS temp_sum
    FROM example_data
)
SELECT *,
    LAST_VALUE(temp_sum) IGNORE NULLS 
    OVER(ORDER BY date_col 
         ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS final_sum
FROM calc_sum
ORDER BY date_col;

Is there a clever way to do this without the CTE? Maybe some trick with the sum statement? Any ideas would be awesome!

2

I’ve encountered a similar challenge in my work with complex SQL queries. While Alex_Dynamo’s suggestion is on the right track, it’s worth noting that nesting window functions directly can sometimes lead to unexpected results or performance issues. In my experience, a more reliable approach is to use a lateral join or a subquery. This method maintains readability and often performs better:

SELECT e.*,
(SELECT LAST_VALUE(s.running_sum) IGNORE NULLS
FROM (SELECT SUM(CASE WHEN flag = 0 THEN value END) OVER (PARTITION BY flag ORDER BY date_col ROWS 6 PRECEDING) AS running_sum
FROM example_data i
WHERE i.date_col <= e.date_col) s
ORDER BY s.running_sum DESC
LIMIT 1) AS final_sum
FROM example_data e
ORDER BY e.date_col;

This approach separates the calculations logically, potentially improving query optimization. It’s also more flexible for future modifications.

3

Hmm, interestin approaches! :thinking: have u considered using a windowed aggregate function with a filter clause? It might simplify things:

SELECT *,
SUM(CASE WHEN flag = 0 THEN value END) FILTER (WHERE date_col >= DATE_ADD(‘day’, -6, date_col))
OVER (ORDER BY date_col ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS final_sum
FROM example_data
ORDER BY date_col;

what do you think? woud this work for your case?

4

hey there! i’ve been messing with sql a lot lately and i think you can actually combine those window functions. try something like this:

SELECT *,
LAST_VALUE(SUM(CASE WHEN flag = 0 THEN value END) OVER(PARTITION BY flag ORDER BY date_col ROWS 6 PRECEDING)) IGNORE NULLS
OVER(ORDER BY date_col ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS final_sum
FROM example_data
ORDER BY date_col;

might need some tweaking but should work!