流失客户表

#当月首次登陆
SELECT UUID_SHORT(),DATE_FORMAT(SYSDATE(),"%Y-%m-00") as Period ,'Free' AS UserType ,'Total users who claimed account this month' as TotalName,
( SELECT COUNT(m.MemberID)  FROM singtel_drptetl.ott_memberinfo m
 WHERE m.firstlogindate >= ${RepM_StartDate} AND m.firstlogindate < ${RepM_EndDate} AND m.MemberID in
 (
  SELECT DISTINCT al.UserID FROM singtel_drptetl.activitieslog al
    WHERE al.PlanID='2,3,9,13,30'
    AND   al.CreateDate >=${RepM_StartDate} AND al.CreateDate < ${RepM_EndDate}
    )
) AS TotalUserCount
UNION ALL
#上月首次登陆当月有下载
SELECT UUID_SHORT(),DATE_FORMAT(SYSDATE(),"%Y-%m-00") as Period , 'Free' AS UserType,'Total users who claimed last month and used this month' as TotalName,
( SELECT COUNT(m.MemberID) FROM singtel_drptetl.ott_memberinfo m
 WHERE m.firstlogindate >= date_sub(${RepM_StartDate},interval 1 month) AND m.firstlogindate < date_sub(${RepM_EndDate},interval 1 month) AND m.MemberID IN
 (
   SELECT DISTINCT al.UserID FROM singtel_drptetl.activitieslog al
    WHERE al.PlanID = '2,3,9,13,30'
    AND   al.CreateDate >= ${RepM_StartDate} AND al.CreateDate < ${RepM_EndDate}
    AND   al.logtype IN ('streaming','playing','download')
)) AS TotalUserCount
UNION ALL
#上月首次登陆当月无下载
SELECT UUID_SHORT(),DATE_FORMAT(SYSDATE(),"%Y-%m-00") as Period ,'Free' AS UserType ,'Total users who claimed last month and did not use this month' as TotalName,
(SELECT COUNT(m.MemberID) FROM singtel_drptetl.ott_memberinfo m
 WHERE
  m.firstlogindate >= date_sub(${RepM_StartDate},interval 1 month) AND m.firstlogindate < date_sub(${RepM_EndDate},interval 1 month) AND m.MemberID IN
  (
   SELECT DISTINCT al.UserID FROM singtel_drptetl.activitieslog al
    WHERE al.PlanID = '2,3,9,13,30' 
    AND   al.CreateDate >= ${RepM_StartDate} AND al.CreateDate < ${RepM_EndDate}
    AND   al.logtype NOT IN ('streaming','playing','download')
)) AS TotalUserCount
UNION ALL
#上月登陆当月无登陆
SELECT UUID_SHORT(),DATE_FORMAT(SYSDATE(),"%Y-%m-00") as Period ,'Free' AS UserType ,'Total users who claimed last month and terminated this month' as TotalName,
(SELECT COUNT(m.MemberID) FROM singtel_drptetl.ott_memberinfo m
 WHERE
  m.firstlogindate >= date_sub(${RepM_StartDate},interval 1 month) AND m.firstlogindate < date_sub(${RepM_EndDate},interval 1 month) AND m.MemberID NOT IN
  (
   SELECT DISTINCT al.UserID FROM singtel_drptetl.activitieslog al
    WHERE al.PlanID = '2,3,9,13,30' 
    AND   al.CreateDate >= ${RepM_StartDate} AND al.CreateDate < ${RepM_EndDate}
)) AS TotalUserCount
UNION ALL
#两月前首次登陆当月有下载
SELECT UUID_SHORT(),DATE_FORMAT(SYSDATE(),"%Y-%m-00") as Period , 'Free' AS UserType,'Total users who claimed 2 months ago and used this month' as TotalName,
( SELECT COUNT(m.MemberID) FROM singtel_drptetl.ott_memberinfo m
 WHERE m.firstlogindate >= date_sub(${RepM_StartDate},interval 2 month) AND m.firstlogindate < date_sub(${RepM_EndDate},interval 2 month)  AND m.MemberID IN
 (
  SELECT DISTINCT al.UserID FROM singtel_drptetl.activitieslog al
    WHERE  al.PlanID = '2,3,9,13,30'
    AND    al.CreateDate >= ${RepM_StartDate} AND al.CreateDate < ${RepM_EndDate}
    AND    al.logtype IN ('streaming','playing','download')
)) AS TotalUserCount
UNION ALL
#两月前首次登陆当月无下载
SELECT UUID_SHORT(),DATE_FORMAT(SYSDATE(),"%Y-%m-00") as Period ,'Free' AS UserType ,'Total users who claimed 2 months and did not use this month' as TotalName,
(SELECT COUNT(m.MemberID) FROM singtel_drptetl.ott_memberinfo m
 WHERE
  m.firstlogindate >= date_sub(${RepM_StartDate},interval 2 month) AND m.firstlogindate < date_sub(${RepM_EndDate},interval 2 month) AND m.MemberID IN
  (
   SELECT DISTINCT al.UserID FROM singtel_drptetl.activitieslog al
    WHERE al.PlanID = '2,3,9,13,30'
    AND   al.CreateDate >= ${RepM_StartDate} AND al.CreateDate < ${RepM_EndDate}
    AND   al.logtype NOT IN ('streaming','playing','download')
)) AS TotalUserCount
UNION ALL
#两月前登陆当月无登陆
SELECT UUID_SHORT(),DATE_FORMAT(SYSDATE(),"%Y-%m-00") as Period ,'Free' AS UserType ,'Total users who claimed 2 months ago and terminated this month' as TotalName,
(SELECT COUNT(m.MemberID) FROM singtel_drptetl.ott_memberinfo m
 WHERE
  m.firstlogindate >= date_sub(${RepM_StartDate},interval 2 month) AND m.firstlogindate < date_sub(${RepM_EndDate},interval 2 month) AND m.MemberID NOT IN
  (
   SELECT DISTINCT al.UserID FROM singtel_drptetl.activitieslog al
    WHERE al.PlanID = '2,3,9,13,30'
    AND   al.CreateDate >= ${RepM_StartDate} AND al.CreateDate < ${RepM_EndDate}
)) AS TotalUserCount
;