1

What I'm trying to implement is to generate equal-sized time series for each customer. One some days he may have some activity hence some retention value indicator, on other dates he may not hence 0.

CustRetention table is the following:

cust_id, date, intensity
...
1,2018-02-02,4
1,2018-02-06,5
...
2,2018-05-05,7
3,2018-02-06,4

What is the sql to generate equal time series (say for 240 days) for each cust_id? I tried the following, but it didn't work

 drop table if exists retention_weekly_intensity_balanced;

 create table retention_weekly_intensity_balanced as 
 select  
 coalesce(t.cust_id, 'unidentified') as cust, 
 d.date_actual, 
 coalesce(t.size_of_increased_intensity) as size_of_increased_intensity
 from  retention_weekly_intensity t
 left join d_date d
ON
 d.date_actual = t.plaindate
WHERE d.date_actual>='2018-01-01'
AND d.date_actual <= '2018-09-01'
 ;

d_date is a simple calendar table

1 Answer 1

1

generate_series() returns a SETOF dates between the first and second argument, at intervals specified by the third argument. Seems to be what you're looking for.

testdb=# create table customers(customer_id bigint);
CREATE TABLE
testdb=# insert into customers select 1;
INSERT 0 1
testdb=# insert into customers select 2;
INSERT 0 1
testdb=# select customer_id, d from customers, generate_series('2018-11-01'::timestamp, '2018-12-01', '1 week') AS d order by 1, 2;
 customer_id |          d          
-------------+---------------------
           1 | 2018-11-01 00:00:00
           1 | 2018-11-08 00:00:00
           1 | 2018-11-15 00:00:00
           1 | 2018-11-22 00:00:00
           1 | 2018-11-29 00:00:00
           2 | 2018-11-01 00:00:00
           2 | 2018-11-08 00:00:00
           2 | 2018-11-15 00:00:00
           2 | 2018-11-22 00:00:00
           2 | 2018-11-29 00:00:00
(10 rows)
0

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.