This is a continuation of an question found here:
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.
I am trying to sort out ways in which I can optimize the giant cartesian join that seems necessary in the query below:
SELECT c.customer_id, d.the_day , sum(t.tsla_amount) OVER w AS tsla_running_amount , sum(t.goog_amount) OVER w AS goog_running_amount FROM ( SELECT the_day::date FROM generate_series(timestamp '2019-01-01' , date_trunc('day', localtimestamp) , interval '1 day') the_day ) d CROSS JOIN (SELECT DISTINCT customer_id FROM transactions) c -- ! LEFT JOIN ( SELECT customer_id , created_at::date AS the_day , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount FROM transactions t WHERE created_at >= timestamp '2019-01-01' GROUP BY customer_id, created_at::date ) t USING (customer_id, the_day) WINDOW w AS (PARTITION BY customer_id ORDER BY the_day) ORDER BY customer_id, the_day;
While this query works, the end goal that I am trying to achieve is that, instead of every single customer having an entry on each day, I want only the customers that have transacted up through that day to be included in the report, and then as soon as a "new" customer has a transaction, they are then included moving forward.
(Currently this query creates rows for each customer even on days in the past in which they never had a transaction yet, and defaults values to 0)
CUSTOMER DDL: CREATE TABLE customers ( customer_id varchar(255) NOT NULL, city varchar(255) NULL, state varchar(255) NULL, postal_code varchar(255) NULL, inserted_at timestamp NOT NULL, updated_at timestamp NOT NULL, CONSTRAINT customers_pkey PRIMARY KEY (customer_id) ); CREATE TRANSACTION DDL: CREATE TABLE public.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) );
Old customers are making stock transactions from 01-01-20 forward
New Customer makes their 1st stock transaction on 01-03-20 (purchase 2 goog)
The report generated in this case for these customers would have an entry for each day, for each customer showing a running balance of their stock transactions:
01-01-2020 - old_123_cust_id - 5 - tsla
01-01-2020 - old_234_cust_id - 10 - goog
01-02-2020 - old_123_cust_id - 5 - tsla
01-02-2020 - old_234_cust_id - 10 - goog
01-03-2020 - old_123_cust_id - 5 - tsla
01-03-2020 - old_234_cust_id - 10 - goog
01-03-2020 - NEW_567_cust_id - 2 - goog
01-04-2020 - old_123_cust_id - 5 - tsla
01-04-2020 - old_234_cust_id - 10 - goog
01-04-2020 - NEW_567_cust_id - 2 - goog
In this example, the NEW_567_cust_id, does not appear in the report, until their 1st transaction, and then moving forward, that customer continues to appear in the report, even if they do not make any additional transactions.
Estimated transactions row count: 300,000
Estimated customers row count: 45,000
Earliest transaction: 01-01-2019
Range: 01-01-2019 -> Today() (When query runs)
The day of a customer's 1st transaction will never change
Happy to provide any additional information!