clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1305706 fiddles created (20095 in the last week).

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') , ('123123abmk16', 10, 'tsla', 'purchase', 'bbbbbb', '2020-04-01 01:00:00', '2020-04-05 01:00:00') , ('123123abmk17', 20, 'tsla', 'purchase', 'bbbbbb', '2020-04-03 01:00:00', '2020-04-08 01:00:00') , ('123123abmk18', 5, 'goog', 'purchase', 'bbbbbb', '2020-04-01 01:00:00', '2020-04-06 01:00:00') , ('123123abmk19', 8, 'goog', 'purchase', 'bbbbbb', '2020-04-03 01:00:00', '2020-04-07 01:00:00'); CREATE INDEX ix_transactions_customer_id ON transactions USING btree (customer_id); CREATE TABLE customers ( customer_id varchar(255) PRIMARY KEY, first_transaction_at timestamp -- can be NULL! ); INSERT INTO customers(customer_id, first_transaction_at) VALUES ('a1b2c3', '2020-04-01 01:00:00') , ('bbbbbb', '2020-04-05 01:00:00') , ('XXX' , NULL);
8 rows affected
3 rows affected
 hidden batch(es)


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 customers c CROSS JOIN LATERAL ( SELECT the_day::date FROM generate_series(first_transaction_at , timestamp '2020-04-10' -- date_trunc('day', localtimestamp) , interval '1 day') the_day ) d 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 '2020-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;
customer_id the_day tsla_running_amount goog_running_amount
a1b2c3 2020-04-01 10 5
a1b2c3 2020-04-02 10 5
a1b2c3 2020-04-03 30 13
a1b2c3 2020-04-04 30 13
a1b2c3 2020-04-05 30 13
a1b2c3 2020-04-06 30 13
a1b2c3 2020-04-07 30 13
a1b2c3 2020-04-08 30 13
a1b2c3 2020-04-09 30 13
bbbbbb 2020-04-05 10
bbbbbb 2020-04-06 10 5
bbbbbb 2020-04-07 10 13
bbbbbb 2020-04-08 30 13
bbbbbb 2020-04-09 30 13
 hidden batch(es)