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;
CREATE TABLE transactions ( transaction_id varchar(255) NOT NULL, amount float8 NOT NULL, stock_ticker varchar(255) NOT NULL, transaction_type varchar(255) NOT NULL, customer_id varchar NOT NULL, inserted_at timestamp NOT NULL, created_at timestamp NOT NULL, CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id) ); INSERT INTO transactions(transaction_id, amount, stock_ticker, transaction_type, customer_id, inserted_at, created_at) VALUES ('123123abmk12', 10, 'tsla', 'purchase', 'a1b2c3', '2020-04-01 01:00:00', '2020-04-01 01:00:00') , ('123123abmk13', 20, 'tsla', 'purchase', 'a1b2c3', '2020-04-03 01:00:00', '2020-04-03 01:00:00') , ('123123abmk14', 5, 'goog', 'purchase', 'a1b2c3', '2020-04-01 01:00:00', '2020-04-01 01:00:00') , ('123123abmk15', 8, 'goog', 'purchase', 'a1b2c3', '2020-04-03 01:00:00', '2020-04-03 01:00:00'); CREATE INDEX ix_transactions_customer_id ON transactions USING btree (customer_id);
The result here always comes back with multiple rows per day, when I want them to be grouped all into one day.
After doing some research I attempted to cast
date in the
GROUP BY clause as well, but I get this error:
Column t.created_at must appear in the GROUP BY clause or be used in an aggregate function
In addition, the results are only going to show days 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 that day. (Using the most recent running balance on the row instead.)
I think that
generate_series() is the way to go, but I am having trouble understanding how to fit it in.