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 select coalesce(t.cust_id, 'unidentified') as clientidno, dtd.d, coalesce(t.size_of_increased_intensity) as size_of_increased_intensity from customer_date_dimention dtd left join retention_weekly_intensity t ON dtd.d = t.date and 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?