Concatenate all previous row values from a TEXT column in reverse order

Question

My Postgres version is 11.15. I have a table like:

id	employee
1	Anna
2	Bill
3	Carol
4	Dave
5	Tom

The output I need is

id	cumulative_employees
1	Anna
2	Bill, Anna
3	Carol, Bill, Anna
4	Dave, Carol, Bill, Anna
5	Tom, Dave, Carol, Bill, Anna

I tried using LAG function of PostgreSQL but it only gives me last row value, could not find a way to get all previous rows, number of rows is dynamic and can be anywhere between 12 to 52 rows.

Can you please help me out?

Answer 1

To get the inverted order of elements in the aggregated string, something like this would do:

SELECT id
     , string_agg(employee, ', ') OVER (ORDER BY id DESC
                                        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM   tbl
ORDER  BY id;

fiddle

Aggregate functions allow a local per-group ORDER BY:

SELECT string_agg(employee, ', ' ORDER BY id DESC) AS cumulative_employees
FROM   tbl;

But the same is not implemented for window functions. Hence the "trick" with shifted window frames to achieve the same.