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 = t.date 
 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

1 Answer 1


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;
  • doesn't work, (CASE WHEN r.intensity IS NULL THEN 0 ELSE r.intensity END) is the same coalesce(t.size_of_increased_intensity) as size_of_increased_intensity Commented Nov 9, 2018 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.