I am trying to gather a count of users by mobile platform who haven't logged in the past but may have logged in the reporting week. For this I have built two virtual tables, with reporting_date - it is Saturday of each week, cust_id, app_logins, mobile_platform with one (newmob) collecting all cust_ids who have app_logins =0 and the other (existmob) have cust_ids which have app_logins>0.
My logic is to use all cust_ids in newmob table till prior Saturday of reporting week in question and find is any of those cust_ids logged in till the Saturday of the reporting week, but it doesn't seem to be working.
table structure - newmob reportingdate | Cust_id | app_login (0) | mobile_os (null)
table structure - existmob reportingdate | Cust_id | app_login (>0) | mobile_os (e.g. Android, iOS)
SQL:
--to_char(last_day(trunc(add_months(trunc(current_date,'D')-1,-12),'D')), 'YYYY-MM-DD') is the min date in tables
Select a.reportingdate, count(distinct a.cust_id) as cust_count, a.mobile_os
from existmob a
where a.reportingdate >= to_char(last_day(trunc(add_months(trunc(current_date,'D')-1,-12),'D')), 'YYYY-MM-DD') + 6
and a.CUST_ID in (select cust_ID from newmob b where b.reportingdate <= a.reportingdate -7)
group by 1,3;
Any help will be appreciated.