This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).
All of this is being done on a PostgreSQL 11.5 DB.
SELECT * FROM ( SELECT the_day::date FROM generate_series(timestamp '2020-01-01', 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;
When a customer has NO transactions on a day, I need to be able to display the "most recent" running total for that customer for that day.
2020-01-01: Customer purchases 5 "tsla" for a total balance of 5. 2020-01-02: Customer takes no action. Current total balance display as 5. 2020-01-03: Customer sells 2 "tsla", total balance now 3. 2020-01-04: Customer takes no action. Current balance still display as 3.
Currently, the way that this is set up, the record for a customer on a day where they have no transactions will return as a NULL row for balance totals.
How can I display their "most recent" running balance on days when they have no transactions?
This query will be running for all customers that exist within the
transactions table (have had a transaction at least once in the past).