PostgreSQL – Generate multiple running totals w/ group by day (Generate Series)


I’m having trouble figuring out a way to make this query work.

I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running balance but for some reason I cannot get the GROUP BY portion of this query to work.

It continues to have duplicate days in the result set even when I attempt to group by the date (created_at). Sample below:

select   t.customer_id,   t.created_at::date,   sum(case when t.stock_ticker = 'tsla' then t.amount end) over (order by t.created_at::date rows unbounded preceding) as tsla_running_amount,   sum(case when t.stock_ticker = 'goog' then t.amount end) over (order by t.created_at::date rows unbounded preceding) as goog_running_amount, from transactions t group by t.created_at, t.customer_id, t.stock_ticker, t.amount order by t.created_at desc; 

The results here always comes back with multiple records per day, when I want them to be grouped all into one day.

After doing some research I attempted cast the “created_at” to ::date inside of the group by fuction as well, but I get a:

“Column t.created_at must appear in the GROUP BY clause or be used in an aggregate function” error every single time.”

In addition, the results are only going to show the day’s in which a transaction has happened for a user. I need to be able to show a ROW for each day in a time series (1 year) even if the user did not make a transaction on that day. (Using the most recent running balance on the row instead)

I think that using “generate_series” is the proper way to do this, but I am having trouble understanding how to fit it in.

Thank you in advance!