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