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);

SELECT * -- ⑥ FROM ( -- ① SELECT the_day::date FROM generate_series(timestamp '2020-04-01' , timestamp '2020-04-07' -- date_trunc('day', localtimestamp) , interval '1 day') the_day ) d LEFT JOIN ( -- ② SELECT customer_id , created_at::date AS the_day -- ⑥ , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'tsla')) OVER w AS tsla_running_amount -- ③ , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'goog')) OVER w AS goog_running_amount FROM transactions t WHERE created_at >= timestamp '2020-01-01' -- ④ GROUP BY customer_id, created_at::date -- ⑤ WINDOW w AS (PARTITION BY customer_id ORDER BY created_at::date) -- ③ ) t USING (the_day) -- ⑥ ORDER BY customer_id, the_day; -- ⑦