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?
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 givencust_id
anddate
theintensity
should return0
.So you might be looking for something like this: