Postgresql – Left join for unique pairs, 0 otherwise


Just to continue the question , I'm struggling to do left join and to keep equal sized time series generated in the previous step.

So I have customer_date_dimention table with equal time series for each cust_id.

cust_id, date

1, 2018-01-01

1, 2018-01-02

2, 2018-01-01

2, 2018-01-02

And then I have retention_weekly_intensity

cust_id, date, intensity    
1, 2018-01-02,6

2, 2018-01-02, 10

and then I want to create the following result:

cust_id, date, intensity

1, 2018-01-01, 0

1, 2018-01-02, 6

2, 2018-01-01, 0

2, 2018-01-02, 7

The query is the following, but the verification gives different sized series

create table retention_weekly_intensity_balanced as 
 coalesce(t.cust_id, 'unidentified') as clientidno, 
 coalesce(t.size_of_increased_intensity) as size_of_increased_intensity
 from customer_date_dimention dtd
 left join retention_weekly_intensity t

 dtd.d = 
 dtd.cust_id = t.cust_id
WHERE dtd.cust_id is not NULL

select cust_dt, count(*) from retention_weekly_intensity_balanced group by 1

How do I properly join the labeled series with other data?

Kind regards

Best Answer

If i understood correctly, then you want to join your two tables and if there is no entry in retention for a given cust_id and date the intensity should return 0.

So you might be looking for something like this:

DROP TABLE IF EXISTS customer_date_dimension;
CREATE TABLE customer_date_dimension(
 cust_id INT,
 cust_date DATE

INSERT INTO customer_date_dimension(cust_id, cust_date) VALUES
(1, '2018-01-01'),
(1, '2018-01-02'),
(2, '2018-01-01'),
(2, '2018-01-02');

CREATE TABLE retention(
  cust_id INT,
  cust_date DATE,
  intensity INT

INSERT INTO retention(cust_id, cust_date, intensity) VALUES
(1, '2018-01-02', 6),
(2, '2018-01-02', 10);

SELECT c.cust_id AS customer_id, c.cust_date AS "date", 
      (CASE WHEN r.intensity IS NULL THEN 0 ELSE r.intensity END) AS intensity
FROM customer_date_dimension AS c
LEFT JOIN retention AS r ON
      c.cust_id = r.cust_id
  AND c.cust_date = r.cust_date;