流失客户表
来源:互联网 发布:网络视频下载 编辑:程序博客网 时间:2024/04/29 11:42
#当月首次登陆
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
;
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
;
- 流失客户表
- 潜在客户流失排查
- 客户流失模型
- 抽丝剥茧谈CRM:客户获取、客户转化、客户流失、客户流失挽回
- 深度反思客户为何流失的真相
- 虚拟主机不稳定,导致客户大量流失?
- 如何用CRM减少客户流失
- 如何用CRM减少客户流失
- 数据挖掘案例:建立客户流失模型
- Cox模型分析客户流失时间
- 数据挖掘案例:建立客户流失模型
- 决策树算法建立电信客户流失模型
- 客户管理中新的竞争会导致客户流失
- CRM下午茶(六)-客户为什么流失?
- CRM下午茶(七)-潜在客户流失排查
- CRM下午茶(六)-客户为什么流失?
- CRM下午茶(七)-潜在客户流失排查
- 哈希表在电信公用电话客户流失分析中的应用
- Dynamics CRM 2013 初体验(3):新增加的功能
- Dynamics CRM 2013 初体验(4):不再被支持的功能
- 将dom对象转成jquery对象 和不能编辑日期控件的文本框
- Dynamics CRM 2013 初体验(5):Business Rule
- 创建表空间及添加数据文件
- 流失客户表
- Sunday algorithm
- Jom 读取XMl文件
- sql获取数据结构
- Android onActivityResult
- HDU 1050 Moving Tables
- 新浪微博Oauth2.0授权 获取Access Token以及API的使用
- Servlet 过滤器实现一段时间内请求次数过滤
- mysql免安装版本使用